1 заметка с тегом

joinWith

PostgreSQL: группируем данные и оборачиваем в массив

Вдруг стоит задача получить сгруппированный список, например из связанной таблицы, да при том оформить построчно данные в виде массива, как-то так:

id author book_ids
1 1 [1, 4]
2 2 3
3 3 [2, 5, 7]

Ну давайте посмотрим, что можно с тим сделать в PostgreSQL.

Вот представим есть 3 таблицы:

  • Книги, `books`
  • Авторы, `authors`
  • Связанная таблица Книга — Автор, `book_author`.

Опишем данные:

Табл `books`:

serial id
string name, Название книги
timestamp create_date, Дата выпуска

id name create_date
1 Преступление и наказание ...
2 Анна Каренина ...
3 Мёртвые души ...
4 Братья Карамазовы ...

Табл `authors`:

serial id
string name, Имя автора
string lastname, Фамилия
string middlename, Отчество (необязательное)
datetime birth_date, День рождения автора

id name lastname middlename birth_date
1 Федор Достоевский Михайлович ...
2 Николай Гоголь ...
3 Лев Толстой Николаевич ...

Соответственно, у книги может быть несколько авторов, и автор может написать несколько книг, потому и нужна связанная таблица:

Табл `book_author`:

serial id
int book_id
int author_id

id book_id author_id
1 1 1
2 2 3
3 3 2
4 4 1

Задача: получить сгруппированный список Авторов и написанных ими книг в виде массива, т.е:

id author book_ids
1 1 [1, 4]
2 2 3
3 3 2

SQL выражение может быть таким:

SELECT array_to_json(array_agg(concat(book_id))) as book_ids, author_id
FROM "book_author"
GROUP BY author_id;

Если же составлять такой запрос в Yii2, то выглядить это может вот так:

$query = BookAuthor::find()
    ->select([
        new Expression("array_to_json(array_agg(concat(".BookAuthor::tableName().".book_id))) as book_ids"),
        BookAuthor::tableName().'.author_id',
    ])
    ->joinWith('author')
    ->groupBy([BookAuthor::tableName().'.author_id'])
    ->indexBy('author_id');

Либо можно передать в провайдер:

$dataProvider = new ActiveDataProvider([
    'query' => $query->all(),
]);

Стоит обратить внимание, что я добавил жадной загрузки в виде joinWith('author'). Эти связанные данные лучше одним запросом достать, если планируется делать вывод во представлении всего списка.

2017   ActiveDataProvider   array_agg   array_to_json   concat   joinWith   PostgreSQL   sql   yii2