This is the 20th day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021

Sorting and grouping

Order and group parameters, which can be used with order BY and group BY

The order argument is used to get the sequelize method to sort the query. The columns themselves are arrays of the form column, direction (ASC, DESC, NULLS FIRST, etc.).

Subtask.findAll({
  order: [
    // Escape title and sort it in descending order against the list of valid directions
    ['title'.'DESC'].// Will be sorted in ascending order by maximum age
    sequelize.fn('max', sequelize.col('age')),

    // This will be sorted in descending order by maximum age
    [sequelize.fn('max', sequelize.col('age')), 'DESC'].// OtherFunction (' col1 ', 12, 'lalala') will be sorted in descending order
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12.'lalala'), 'DESC'].// The model name will be used as the association name sorted by the createdAt of the association model.
    [Task, 'createdAt'.'DESC'].// The model name will be used as the association name to sort through the createdAt of the association model.
    [Task, Project, 'createdAt'.'DESC'].// The association name will be used to sort by the createdAt of the association model.
    ['Task'.'createdAt'.'DESC'].// The associated name will be sorted by the createdAt of the nested association model.
    ['Task'.'Project'.'createdAt'.'DESC'].// Relational objects will be used to sort by the createdAt of the relational model. (Preferred method)
    [Subtask.associations.Task, 'createdAt'.'DESC'].// Relational objects will be used to sort by nested relational model createdAt. (Preferred method)
    [Subtask.associations.Task, Task.associations.Project, 'createdAt'.'DESC'].// A simple correlation object will be used to sort by the createdAt of the correlation model.
    [{model: Task, as: 'Task'}, 'createdAt'.'DESC'].// Will be sorted by the createdAt simple correlation object of the nested correlation model.
    [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt'.'DESC']],// Will be sorted in descending order by maximum age
  order: sequelize.literal('max(age) DESC'),

  // If the direction is ignored, the default is ascending, and the order will be ascending by the maximum age
  order: sequelize.fn('max', sequelize.col('age')),

  // If the direction is omitted, the order is ascending by age by default
  order: sequelize.col('age'),

  // Will sort randomly according to dialect (but not fn('RAND') or FN ('RANDOM'))
  order: sequelize.random()
});

Foo.findOne({
  order: [
    // Will return 'name'
    ['name'].// will return 'username' DESC
    ['username'.'DESC'].// Will return Max (' age ')
    sequelize.fn('max', sequelize.col('age')),
    // Will return Max (' age ') DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'].Otherfunction (' col1 ', 12, 'lalala') DESC will be returned
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12.'lalala'), 'DESC'].// Will return otherFunction (awesomeFunction (' col ')) DESC, this nesting may be infinite!
    [sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']]});Copy the code

The order array can have the following elements:

  • A string (which will be automatically referenced)

  • An array whose first element is referenced and whose second element is appended verbatim

  • An object with a RAW field:

    • rawThe content will be added verbatim without reference
    • Everything else will be ignored if not setraw, the query will fail

  • Call sequelize.fn (this will generate a function call in SQL)

  • Call sequelize.col (this will reference the column name)

grouping

The syntax for grouping is the same as for sorting, except that grouping does not accept sorting order as the last argument to the array (no ASC, DESC, NULLS FIRST, etc.), passing the string directly to the group

User.findAll({ group: 'name' });
// generate 'GROUP BY name'
Copy the code

Limiting and paging

The limit and offset parameters can be restricted/paginated and are usually used together with the order parameter.

// Extract 10 instances/rows
User.findAll({ limit: 10 });

// Skip 8 instances/rows
User.findAll({ offset: 8 });

// Skip 5 instances and get 5 instances
User.findAll({ offset: 5.limit: 5 });
Copy the code