Ещё более продвинутое использование ORM Sequelize

1. Как использовать ORM Sequelize
2. Продвинутое использование ORM Sequelize
3. Ещё более продвинутое использование ORM Sequelize <- Вы здесь

Сегодня я собираюсь ещё больше углубиться в ОРМ sequelize. И хотя прошлого поста должно было хватить почти всем, осталась ещё пачка незатронутых тем.

И так приступим:

Инлайн SQL код (например в where условии)

Это не то же самое, что прямой SQL запрос. Например, нам нужно остаться в рамках sequelize модели, но условия переписать вручную, это можно сделать через sequelize.literal:

  let test1 = await commentsModel.findAll({
    where: sequelize.literal(`id > 2`)
  });

Результат:

SELECT `id`, `posts_id`, `userName`, `comment`, `createdAt`, `updatedAt` FROM `comments` AS `comments` WHERE id > 2;

sequelize.literal можно миксовать со стандартными условиями через логические операторы:

  let test1 = await commentsModel.findAll({
    where: {
      [ Op.and ]: [
        {
          posts_id: [ 1,2,3,4 ]
        },
        sequelize.literal(`id > 2`)
      ]
    }
  });

Результат:

SELECT `id`, `posts_id`, `userName`, `comment`, `createdAt`, `updatedAt` FROM `comments` AS `comments` WHERE (`comments`.`posts_id` IN (1, 2, 3, 4) AND id > 2);

Атрибуты и колонки

По умолчанию, на любой чих вроде findAll/findOne, sequelize возвращает все доступные колонки таблицы, то есть делает обычный "SELECT *". Если нам нужны только конкретные колонки, то нужно указать их через параметр "attributes", например:

  let test1 = await commentsModel.findAll({
    attributes: [ 'id', 'posts_id' ]
  });

Этот код выполнит запрос:

SELECT `id`, `posts_id` FROM `comments` AS `comments`;

Виртуальные колонки

Если нужно сделать селект по виртуалной колонке, нам в первую очередь приходит на помощь всё тот же sequelize.literal:

  let test1 = await postsModel.findAll({
    attributes: [
      'id',
      'title',
      [sequelize.literal(`(SELECT COUNT(comments.id) FROM comments WHERE comments.posts_id=posts.id)`), 'comments_count']
    ]
  });

Результат:

SELECT `id`, `title`, (SELECT COUNT(comments.id) FROM comments WHERE comments.posts_id=posts.id) AS `comments_count` FROM `posts` AS `posts`;

А вот во вторую очередь нам приходят на помощь:

SQL функции в sequelize

Функция вызывается через sequelize.fn, например вот так:

  let test1 = await postsModel.findAll({
    attributes: [
      'id',
      'title',
      [ sequelize.fn('strftime', '%M/%Y', sequelize.col('posts.createdAt')), 'mydate' ],
    ]
  });

Результат:

SELECT `id`, `title`, strftime('%M/%Y', `posts`.`createdAt`) AS `mydate` FROM `posts` AS `posts`;

Group by запросы

  let test1 = await commentsModel.findAll({
    attributes: [
      [ sequelize.fn('strftime', '%M', sequelize.col('createdAt')), 'mydate' ],
    ],
    group: 'mydate'
  });

Выполнится запрос:

SELECT strftime('%M', `createdAt`) AS `mydate` FROM `comments` AS `comments` GROUP BY `mydate`;

Если Вы исользуете postgresql, то с радостью сообщаю, что есть старый баг, который не позволит выполнить следующий запрос:

  let test1 = await commentsModel.findAll({
    where: {
      userName: 'ololoev'
    },
    attributes: [
      [ sequelize.fn('strftime', '%M', sequelize.col('comments.createdAt')), 'mydate' ],
    ],
    include: [
      {
        model: postsModel,
        as: 'post',
        attributes: [],
        where: {
          id: [1,2,3,4]
        },
        required: true
      }
    ],
    group: 'mydate'
  });

На всех поддерживаемых СУБД этот запрос преобразуется в следующий SQL:

SELECT strftime('%M', `comments`.`createdAt`) AS `mydate` FROM `comments` AS `comments` INNER JOIN `posts` AS `post` ON `comments`.`posts_id` = `post`.`id` AND `post`.`id` IN (1, 2, 3, 4) WHERE `comments`.`userName` = 'ololoev' GROUP BY `mydate`;

Но именно на постгре, добавляется comments.id колонка, которая всё ломает:

SELECT `comments`.`id, strftime('%M', `comments`.`createdAt`) AS `mydate` FROM `comments` AS `comments` INNER JOIN `posts` AS `post` ON `comments`.`posts_id` = `post`.`id` AND `post`.`id` IN (1, 2, 3, 4) WHERE `comments`.`userName` = 'ololoev' GROUP BY `mydate`;

Олсо, чтобы его воспроизвести, не обязательно юзать виртуальную колонку, это так, под руку подвернулось. Ишью по этому поводу: https://github.com/sequelize/sequelize/issues/7534

Union запросы

Не поддерживаются, и нет планов по поддержке: https://github.com/sequelize/sequelize/issues/4293 integers/bigint парсятся в строку на sequelize/pgsql

Это древний баг. Не самого сикулайза, а модуля "pg". Ишью на гите: https://github.com/sequelize/sequelize/issues/1774. Чтобы его победить нужно, перед всем другим кодом, работающим с бд, вызвать код:

require('pg').defaults.parseInt8 = true;