MySQL: Получение группированных подмножеств в виде JSON

2013-07-08

Иногда, нам нужно одним запросом получить данные не только с одной таблицы, но и с зависимой от нее как «один ко многим». То-есть одной записи из главной таблицы соответствует несколько с зависимой. Это можно сделать несколькими путями. Самый 1-й, который приходит на ум — просто использовать JOIN, но в этом случае мы получим в ответе дублирующиеся записи главное таблицы, и отличающиеся только данными с зависимой. В этом случае нам придется делать дополнительные манипуляции с полученным результатом, чтобы получить более удобную иерархическую структуру данных.

Но, можно сделать проще — можно подготовить вложенную структуру прямо с помощью запроса:

Идея следующая: использую GROUP BY избегаем дублирования данных основной таблицы, а с помощью функции GROUP_CONCAT формируем нужный нам формат вложенных данных, например JSON:

Допустим у нас есть такие данные:

questions:

id text
1 Question 1 ?
2 Question 2 ?
3 Question 3 ?

answers:

id question_id text
1 1 Answer 1 for 1
2 1 Answer 2 for 1
3 2 Answer 3 for 2


SELECT
q.id,
q.text question,
CONCAT('[',
GROUP_CONCAT(CONCAT('{"id":', a.id, ', "answer":"', a.text,'"}')),
']') answers_list
FROM
questions q
LEFT JOIN answers a ON q.id=a.question_id
GROUP BY
q.id;

В результате получим такой результат:

id question answers_list
1 Question 1 ? [{«id»:1, «answer»:»Answer 1 for 1″},{«id»:2, «answer»:»Answer 2 for 1″}]
2 Question 2 ? [{«id»:3, «answer»:»Answer 3 for 2″}]
3 Question 3 ? NULL


Один комментарий на «MySQL: Получение группированных подмножеств в виде JSON»

  1. lex:

    Хорошее решение!!! Автору респект!

Добавить комментарий