Использование group by в Sequelize: синтаксис и примеры

Быстрый ответ

В ORM-библиотеке Sequelize для Node.js оператор GROUP BY группирует записи по указанному полю. Обычно он используется в связке с агрегатными функциями, например, COUNT(), SUM(), AVG() и т. д.

Вот пример SQL-запроса, генерируемого Sequelize для подсчёта количества записей в группах:

const countItems = await Model.findAll({
  attributes: [
    'groupColumn',
    [Sequelize.fn('COUNT', Sequelize.col('groupColumn')), 'itemCount']
  ],
  group: 'groupColumn'
});

Этот JavaScript-код переводится в SQL-запрос вроде этого:

SELECT groupColumn, COUNT(groupColumn) AS itemCount FROM Model GROUP BY groupColumn;

В этом коде синтаксис Sequelize применяется к таблице Model, где groupColumn – это название одного из столбцов.

Понимание механизма Group By

Чтобы в Sequelize выполнить группировку записей, в метод findAll передаются параметры группировки через свойство group.

Оставайтесь в курсе

Чтобы всегда иметь доступ к последним возможностям библиотеки Sequelize, следите за её обновлениями.

Подробнее об этом расскажет наш спикер на видео

Использование fn и col

Для вызова агрегатных функций и выборки полей в Sequelize предусмотрены функции sequelize.fn и sequelize.col.

const totalPrice = await Product.findAll({
  attributes: [
    'category',
    [sequelize.fn('SUM', sequelize.col('price')), 'totalPrice']
  ],
  group: ['category']
});

Работа с необработанными данными и псевдонимами

Чтобы применять необработанные SQL-запросы, в метод findAll передайте свойство raw: true. Для удобства чтения кода используйте псевдонимы.

const groupData = await Model.findAll({
  attributes: [
    [sequelize.fn('COUNT', sequelize.col('status')), 'StatusCount']
  ],
  group: ['status'],
  raw: true
});

Внимание к деталям

Не используйте зарезервированные слова SQL для названий фактических и псевдонимных имен. Обновляйте библиотеку Sequelize регулярно, чтобы быть в курсе последних изменений.

Более сложные сценарии использования

Для решения более сложных задач связанных с оператором Group By, следует активно использовать базовые возможности Sequelize.

Работа с ассоциациями

При группировке по ассоциациям, обратите внимание на корректную связь таблиц и выбор подходящих полей для группировки.

const orderCount = await Customer.findAll({
  attributes: [
    [sequelize.fn('COUNT', sequelize.col('orders.id')), 'orderCount']
  ],
  include: [{
    model: Order,
    attributes: []
  }],
  group: ['Customer.id']
});

Работа с зарезервированными словами

Чтобы исключить конфликты с зарезервированными словами, используйте метод literal.

const dataWithReservedKeywords = await Model.findAll({
  attributes: [
    'id',
    [sequelize.fn('COUNT', sequelize.col('"order"."id"')), 'orderCount']
  ],
  group: [sequelize.literal('1')]
});

Группировка по датам

Данные можно группировать и по датам, чтобы проводить анализ во времени.

const dailySales = await Sale.findAll({
  attributes: [
    [sequelize.fn('DATE', sequelize.col('createdAt')), 'date'],
    [sequelize.fn('SUM', sequelize.col('amount')), 'totalSales']
  ],
  group: [sequelize.fn('DATE', sequelize.col('createdAt'))]
});

Принципы эффективного использования группировки

Применяйте современные приемы для более эффективной работы с оператором Group By.

Индексация столбцов

Для ускорения работы SQL-запросов выполняйте индексацию полей, используемых в операторе Group By.

Использование GROUP BY вместе с having

Используйте функцию having для фильтрации сгруппированных данных после выполнения операции агрегации.

const havingExample = await Model.findAll({
  attributes: [
    'groupColumn',
    [sequelize.fn('COUNT', sequelize.col('dataColumn')), 'dataCount']
  ],
  group: 'groupColumn',
  having: sequelize.where(sequelize.fn('COUNT', sequelize.col('dataColumn')), '>', 5)
});

Группировка по нескольким столбцам

В Sequelize можно группировать данные одновременно по нескольким столбцам, передав их в group в виде списка.

const compositeGrouping = await Model.findAll({
  group: ['columnA', 'columnB']
});

Проверяйте соответствие запросов правилам SQL в вашей БД.

Визуализация

Представьте кухонный ящик, где лежат разные столовые приборы:

До Group By: [🍴, 🥄, 🍴, 🥄, 🍴, 🥄, 🔪, 🔪]

GROUP BY в Sequelize можно представить более наглядно как работу робота, который разделяет столовые приборы по типу:

GROUP BY 'CutleryType'

В результате работы робота мы получаем что-то вроде этого:

После Group By:
{
  Вилки (🍴): [🍴, 🍴, 🍴],
  Ложки (🥄): [🥄, 🥄, 🥄],
  Ножи (🔪): [🔪, 🔪]
}

С теперь все столовые приборы сгруппированы по типу, что облегчает их подсчёт.

Полезные материалы

1. Model Querying – Basics | Sequelize — описание работы с оператором группировки в Sequelize в официальной документации.
2. Newest 'sequelize.js+group-by' Questions – Stack Overflow — примеры и типовые ситуации, связанные с использованием GROUP BY в Sequelize.
3. Issues · sequelize/sequelize · GitHub — обзор проблем и улучшений, связанных с оператором GROUP BY в Sequelize.
4. SQL GROUP BY Statement — основы SQL для понимания GROUP BY.
5. Sequelize cheatsheet — шпаргалка с основным синтаксисом Sequelize, включая GROUP BY.
6. Reddit – Dive into anything — обсуждения и рекомендации разработчиков о том, как эффективно использовать GROUP BY в Sequelize.
7. Usage – Sequelize | The Node.js / io.js ORM for PostgreSQL, MySQL, SQLite and MSSQL — примеры выполнения операций группировки и агрегации с Sequelize с использованием GROUP BY.