How to form a sql query correctly?

Question

SQL

Good afternoon.
There are entities: Tasks, rewards for tasks


. There is also a sign for linking tasks with prizes. Multiple prizes can be attached to one task.


Essence of the question: How, when accessing a certain php file via ajax, give data of the format task - its prizes, task - its prizes

Can this be done with one request? I tried using join, but it turns out a lot of duplicate tasks, since there are many prizes for one task. How is it done correctly? Maybe I messed something up with the structure. I would be grateful for any hints.

Answer 1

You have a many-to-many relationship. This is the normal circuit for the given example. Here you can read more about it: https://habr.com/ru/post/193380/
Data can be obtained, for example, as follows (tasks_prizes is a join table):

SELECT t.id, t.name_task, p.name_prize 
FROM tasks t
LEFT JOIN tasks_prizes tp ON t.id = tp.id_task  
LEFT JOIN prizes p ON p.id = tp.id_prize
ORDER BY t.id ASC;

Those. left join of the join table, then left join of the prizes.
The result of such a request:

+----+-----------+------------+
| id | name_task | name_prize |
+----+-----------+------------+
|  1 | task #1   | prize #1   |
|  1 | task #1   | prize #2   |
|  2 | task #2   | prize #3   |
|  2 | task #2   | prize #4   |
|  3 | task #3   | prize #5   |
+----+-----------+------------+

Based on these data, using php, create the answer you need. You can make, for example, an associative array, where the keys will be task identifiers, and the value will be an array with additional information on the task and its prizes:

$tasks = [
  '1' => [
    'name_task' => 'task #1',
    'prizes' => [
      '1' => [
        'name_prize' => 'prize #1',
      ],
      '2' => [
        'name_prize' => 'prize #2',
      ]
    ]
  ]
];

Answer 2

can. subqueries

Answer 3

It is possible through JOIN, simply ignoring the extra information. It is possible with two requests, first the necessary tasks, then their prizes. It is possible to write a complex query with CONCAT and GROUP_CONCAT, generating JSON directly in the query.

Answer 4

Tried through join, but it turns out a lot of duplicate tasks, since there are many prizes for one task.

Not a problem, after executing the request, collect the nested object.
If you are afraid to receive all the lines, then in the loop, get one line at a time with the fetch function, also not a problem.

select task_id, ......, prize_id, .......
from (сложный join)

$out = [];
foreach($rows as $row)
{
  $task = &$out[row['task_id']];
  $task['id'] = row['task_id'];
  $task['name'] = row['name_task'];
  .... // другие свойства задачи
  if(!array_key_exists('prizes', $task))
  {   
    $task['prizes'] = [];
  }
 if(!is_null($row['prize_id']))
  {
   $prize = &$task['prizes'][$row['prize_id']];
   $prize['id'] =  $row['prize_id'];
    ..... // другие свойства приза
    unset($prize);
  }
  unset($task);
}
var_dump($out);

Answer 5

I would issue the following when accessing this ajax file:

$result = [
  'tasks' => [
    1 => ['title' => 'Собрать монеты', 'desc' => 'Подробное описание'],
    2 => ...
  ],
  'awards' => [1 => 'Серебро', 2 => 'Золото', 3 => 'Чугун'...],
  'prizes' => [[1, 1], [2, 2], [2, 1]...]
];

And he collected the necessary data in a bunch already with a javascript on the client. In order to:
1) not drive a shitty bunch of uselessly duplicated information between the database and the puff and between the server and the client;
2) getting the first two lists is stupid from the database cache, because no tricky selections are needed in this situation.