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

sql

Сделать UPDATE с JOIN в PostgreSQL

Обычный update усложним связью с другой таблицей и получится следующее:

UPDATE TableA AS a
SET param_from_table_a=FALSE
FROM TableB AS b
WHERE b.id=a.param_id AND a.amount <> 0; 
2017   PostgreSQL   sql   sql update   update & join

Преобразовать timestamp в формат date

Когда нужно преобразовать имеющийся timestamp в date да и с часовым поясом.

Вот так:

SELECT id, to_timestamp(create_at) at time zone 'Europe/Moscow' FROM "table" WHERE ...
2017   date   PostgreSQL   sql   Timestamp

Конвертируем поле json в jsonb в PostgreSQL

На чистом SQL это можно сделать так:

ALTER TABLE table_name ALTER COLUMN column_with_json SET DATA TYPE jsonb USING column_with_json::jsonb

Если делать миграцию на Yii2, то можно сделать так:

public function safeUp()
{
    $this->execute('ALTER TABLE table_name ALTER COLUMN column_with_json SET DATA TYPE jsonb USING column_with_json::jsonb');
}
2017   alter column   alter table   json   jsonb   migrate   php   PostgreSQL   sql

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'). Эти связанные данные лучше одним запросом достать, если планируется делать вывод во представлении всего списка.

PostgreSQL: преобразование типа поля VARCHAR в INTEGER

Стандартными средствами sql это можно сделать так:

ALTER TABLE table_name ALTER COLUMN column_name TYPE integer USING (column_name::integer);

А миграцией в Yii2 можно сделать так:

public function safeUp()
{
    $this->execute('alter table {{%table_name}} alter column column_name TYPE integer USING (column_name::integer);');
}

В итоге мы получим преобразованное поле Integer.

UPD, в некоторых случаях необходимо сделать:

update "table_name" set column_name=null;
2017   alter table   PostgreSQL   sql   yii2

Несколько COUNT в одном SQL-запросе

Например, необходимо из таблицы заказов вытащить кол-во доступных текущих заказов, и кол-во заказов, доступных через час и сгруппировать по Покупателям.
Таблица «order» — таблица заказов, таблица «user» — таблица пользователей.
Сделаем 2 алиаса для таблицы заказов order1 и order2.
current_time и current_time_hour — это значения времени, которые биндятся к запросу. В нашем случае, это текущее время и время через час, соответственно.

SELECT "user".*, COUNT(DISTINCT "order1".id) as count, COUNT(DISTINCT "order2".id) as count_hour 
FROM "user" 
LEFT JOIN "order" as "order1" ON "user".id="order1".user_id
LEFT JOIN "order" as "order2" ON "user".id="order2".user_id
WHERE "order1"."updated_at"<=:current_time AND "order2"."updated_at"<=:current_time_hour
GROUP BY "user".id

Важным момонтом здесь является использование DISTINCT при выполнении COUNT.

2017   count   group by   sql

Получить чистый sql-запрос из AR и AQ в Yii2

Собираем запрос, используя Active Record или Active Query и чтобы убедиться в правильности запроса смотрим на сам SQL.

// собираем AR
$orders = Order::find()->where(['status' => 1])->orderBy('date ASC');

// Получим sql в чистом виде:
$orders->createCommand()->getRawSql();

И увидим результат:

SELECT * FROM "order" WHERE "status"=1 ORDER BY "date"
2017   active query   active record   AR   php   sql   yii2