Как правильно сформировать запрос sql?

Question

[vovaaar]

Добрый день.
Есть сущности: Задачи, награды за задачи


Так же есть табличка для связи заданий с призами. К одному заданию может прилагаться множество призов.


Суть вопроса: Как при обращении по ajax к определенному php файлу, отдавать данные формата задача - ее призы, задача - ее призы

Можно ли это сделать одним запросом? Пробовал через join, но получается множество дублей задач, так как призов к одной задаче множество. Как это делается правильно ? Может я намудрил чего со структурой. Буду благодарен за любые наводки.

Answer 1

[Dmitry]

У вас получилась связь "многие-ко-многим" (many-to-many relationship). Это нормальная схема для указанного примера. Здесь вы можете почитать о ней подробнее: https://habr.com/ru/post/193380/

Данные можно получить, к примеру, следующим образом (tasks_prizes - соединительная таблица):

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;

Т.е. left join соединительной таблицы, затем left join призов.

Результат такого запроса:

+----+-----------+------------+
| 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   |
+----+-----------+------------+

По этим данным с помощью php создаете ответ, какой вам необходим. Можно сделать, к примеру, ассоциативный массив, где ключами будут идентификаторы задач, а значением - массив с дополнительной информацией по задаче и ее призами:

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

Answer 2

[Rsa97]

Можно через JOIN, просто игнорируя лишнюю информацию. Можно двумя запросами, сначала нужные задачи, затем их призы. Можно написать сложный запрос с CONCAT и GROUP_CONCAT, формируя JSON прямо в запросе.

Comments

[vovaaar]

Rsa97 а по поводу структуры бд как считаете, в данной ситуации так и нужно делать ? или есть какие то варианты поинтересней?

[Rsa97]

vovaaar, Учитывая набор полей в таблице призов, по ним явно осуществляется поиск. Значит денормализацию делать не стоит.

Answer 3

[alexalexes]

Пробовал через join, но получается множество дублей задач, так как призов к одной задаче множество.

Не проблема, после выполнения запроса, собираете вложенный объект.
Если боитесь все строки получать, то в цикле получайте по одной строке fetch-функцией, тоже не проблема.

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 4

[Adamos]

Я бы при обращении к этому ajax-файлу выдавал следующее:

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

И собирал нужные данные в кучку уже джаваскриптом на клиенте. Чтобы:
1) не гонять хренову кучу без толку дублирующейся информации между базой и пыхом и между сервером и клиентом;
2) получать первые два списка тупо из кэша БД, потому что никакие хитрые выборки в этой ситуации на хрен не нужны.