Order and Limit by included model

[mmmmmrob]

What are you doing?

Ordering or limiting by an associated model. Example here would be:

find the 10 brands with the best reviewed stores

Other examples might be:

• find users who most recently posted
• find brands with stores closest to me
• find products that were most recently purchased

Lots of cases where you want to find a distinct set of a model, ordered by a property of an associated model.

'use strict'

const _ = require('lodash')
const Sequelize = require('sequelize')
const sequelize = new Sequelize(process.env.DATABASE_URL, { dialectOptions: { ssl: true }, logging: false })

// a brand name, like a store, or restaurant chain
sequelize.define('brand', {
  id: { type: Sequelize.INTEGER, autoIncrement: true, primaryKey: true },
  name: Sequelize.STRING
})

// an individual real-world location, a store, or restaurant
sequelize.define('store', {
  id: { type: Sequelize.INTEGER, autoIncrement: true, primaryKey: true },
  score: Sequelize.INTEGER,
  brandId: Sequelize.INTEGER
})

sequelize.models.brand.hasMany(sequelize.models.store)
sequelize.models.store.belongsTo(sequelize.models.brand)

const main = async () => {
  await sequelize.sync()
  await sequelize.models.store.destroy({ truncate: true, cascade: true })
  await sequelize.models.brand.destroy({ truncate: true, cascade: true })

  // create 22 test brands
  const testbrands = _.range(22).map(i => ({ name: `Brand ${i}` }))
  await sequelize.models.brand.bulkCreate(testbrands, { returning: true })
  const brands = await sequelize.models.brand.findAll()

  // create 4 stores, each with a random score, for each brand
  const teststores = brands.reduce((array, brand) => {
    const brandstores = _.range(4).map(i => ({ brandId: brand.id, score: brand.id * 10 + _.random(1, 9) }))
    return [...array, ...brandstores]
  }, [])
  const stores = await sequelize.models.store.bulkCreate(teststores)

  // find the 10 brands with the highest scores
  const nearestBrands = await sequelize.models.brand.findAll({
    attributes: ['name'],
    include: [
      {
        model: sequelize.models.store,
        order: [sequelize.col('score'), 'DESC']
      }
    ],
    limit: 10,
    order: [[sequelize.models.store, sequelize.col('score'), 'DESC']],
    logging: console.log
  })

  console.log(nearestBrands.map(b => [b.name, b.stores[0].score]))

  await sequelize.close()
}

main()

To Reproduce
Steps to reproduce the behavior:

1. run code sample above which creates model and test data
2. See response returns brands 9..0

What do you expect to happen?

Returns brands 21..12

SELECT *
FROM brands
JOIN (
  SELECT DISTINCT ON ("brandId") *
  FROM stores
  ORDER BY "brandId", score DESC
) AS stores
ON brands.id = stores."brandId"
ORDER BY stores.score DESC
LIMIT 10;

What is actually happening?

Returns brands 9..0

SELECT
  "brand".*,
  "stores"."id" AS "stores.id",
  "stores"."score" AS "stores.score",
  "stores"."brandId" AS "stores.brandId",
  "stores"."createdAt" AS "stores.createdAt",
  "stores"."updatedAt" AS "stores.updatedAt"
FROM (
  SELECT
    "brand"."id",
    "brand"."name"
  FROM
    "brands" AS "brand"
  LIMIT 10
) AS "brand"
LEFT OUTER JOIN "stores" AS "stores" ON "brand"."id" = "stores"."brandId"
ORDER BY "stores"."score" DESC;

Environment

Dialect:

• mysql
• postgres
• sqlite
• mssql
• any
  Dialect library version: XXX
  Database version: XXX
  Sequelize version: 4.35.2
  Node Version: 12.6.0
  OS: Linux
  If TypeScript related: TypeScript version: XXX
  Tested with latest release:
• No
• Yes, specify that version:

[papb]

I was able to reproduce in the latest version.

But if this would be "fixed", it would be a breaking change... I can also see usage for the current behavior, no? Perhaps this should be considered a feature instead of a bug?

[mmmmmrob]

@papb I can see it could be a breaking change. I'd be super happy to simply be told I'm approaching it wrong. The essence of the problem is how to I get a limited set of model A, sorted by a property of associated model B.

[securedeveloper]

@mmmmmrob same thing happened to me, was trying to debug and find what I was doing wrong, until I realised it doesn't work at all. Would love to create a feature PR.

[packlikez]

@papb I can see it could be a breaking change. I'd be super happy to simply be told I'm approaching it wrong. The essence of the problem is how to I get a limited set of model A, sorted by a property of associated model B.

Same problem here.

[papb]

@securedeveloper If you are willing to submit a PR, that would be great!! Even better if you make it a feature rather than a change.

[mmmmmrob]

Is there really no way to model and query this scenario using the current sequelize features?

It seems like such a common thing to need that I assumed my modelling/querying was the problem and someone would say "oh, you need to query like this..."

I mean, I'm surprised because sequelize is so complete, and "like this" is the answer I've got to every previous question.

[papb]

@mmmmmrob Good point. Perhaps there is a way to do it already... Although I am a maintainer, there are many things in Sequelize I don't know (yet)! @sushantdhiman can you take a look?

Have you tried Stack Overflow? Maybe you can ask and add a link here. Or try Slack as well...

[mmmmmrob]

@papb I ended up asking here because I'd asked several times over a few weeks on slack and got no reply. I think the question is a bit too high-level for slack.

[papb]

@mmmmmrob I see. Let's wait to see what sushantdhiman has to say about it.

[packlikez]

I workaround with order: [Sequelize.literal('score DESC')]

[mmmmmrob]

An interesting workaround is to inject a literal as an attribute:

const nearestBrands = await sequelize.models.brand.findAll({
    attributes: {
      include: [
        [
          sequelize.literal(`(SELECT score FROM stores WHERE stores."brandId" = brand.id ORDER BY score DESC LIMIT 1)`),
          'storeScore'
        ]
      ]
    },
    limit: 10,
    order: [[sequelize.literal('"storeScore"'), 'DESC']],
    logging: console.log
  })

Maybe a nicer way to do this would be inject a literal join. Is there any way to do that?

[packlikez]

Another way is set duplicating false every include then a normal order. It will join everything first then order maybe impact the performance in some case.

[packlikez]

But we still need order in include

[packlikez]

When I order with literal it's work but when I use [Model,'DESC'] it's put order only the outside

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[tsai121594]

I spent hours on this. Workaround that worked for me is,

order: [['includeTable1', 'includeTable2', 'column', 'ASC']]

This is three nested query. My query looked like
table.findAll(include: {..., as: includeTable1, include: {..., as:includeTable2}}, order: [['includeTable1', 'includeTable2', 'column', 'ASC']])

[ThierryVC]

I tried both sequelize@6.23.1 & sequelize@6.24.0 with mysql

Using limit, this did not work:

order: [
  [{ model: AdminNotice, as: 'adminNotice' }, 'sendOn', 'DESC'],
],
limit: 30,
offset: 0

translated to sql:

SELECT `Notice`.* FROM (
  SELECT
    `Notice`.`id`, 
    `adminNotice`.`noticeId` AS `adminNotice.noticeId`, 
    `adminNotice`.`sendOn` AS `adminNotice.sendOn` 
  FROM `Notices` AS `Notice` 
  INNER JOIN `AdminNotices` AS `adminNotice` ON `Notice`.`id` = `adminNotice`.`noticeId` 
  LIMIT 0, 30
) AS `Notice`
ORDER BY `adminNotice`.`sendOn` DESC;

with error: Unknown column 'adminNotice.sendOn' in 'order clause'

Commenting out the limit made it succeed, turning it into

SELECT 
 `Notice`.`id`,
 `adminCalendarNotice`.`noticeId` AS `adminCalendarNotice.noticeId`,
 `adminCalendarNotice`.`sendOn` AS `adminCalendarNotice.sendOn`
FROM `Notices` AS `Notice` 
INNER JOIN `AdminCalendarNotices` AS `adminCalendarNotice` ON `Notice`.`id` = `adminCalendarNotice`.`noticeId`
ORDER BY `adminCalendarNotice`.`sendOn` DESC 
LIMIT 0, 10000000000000;

But needing the limit, I found that this would work:

order: [
  [sequelize.literal('`adminNotice.sendOn` DESC')],
]

turning the sql into:

SELECT `Notice`.* 
FROM (
  SELECT `Notice`.`id`, `adminNotice`.`noticeId` AS `adminNotice.noticeId`, `adminNotice`.`sendOn` AS `adminNotice.sendOn`
  FROM `Notices` AS `Notice` 
  INNER JOIN `AdminNotices` AS `adminNotice` ON `Notice`.`id` = `adminNotice`.`noticeId` 
  ORDER BY `adminNotice.sendOn` DESC LIMIT 0, 30
) AS `Notice`
ORDER BY `adminNotice.sendOn` DESC;

[shubhanshu74156]

I tried both sequelize@6.23.1 & sequelize@6.24.0 with mysql

Using limit, this did not work:

order: [
  [{ model: AdminNotice, as: 'adminNotice' }, 'sendOn', 'DESC'],
],
limit: 30,
offset: 0

translated to sql:

SELECT `Notice`.* FROM (
  SELECT
    `Notice`.`id`, 
    `adminNotice`.`noticeId` AS `adminNotice.noticeId`, 
    `adminNotice`.`sendOn` AS `adminNotice.sendOn` 
  FROM `Notices` AS `Notice` 
  INNER JOIN `AdminNotices` AS `adminNotice` ON `Notice`.`id` = `adminNotice`.`noticeId` 
  LIMIT 0, 30
) AS `Notice`
ORDER BY `adminNotice`.`sendOn` DESC;

with error: Unknown column 'adminNotice.sendOn' in 'order clause'

Commenting out the limit made it succeed, turning it into

SELECT 
 `Notice`.`id`,
 `adminCalendarNotice`.`noticeId` AS `adminCalendarNotice.noticeId`,
 `adminCalendarNotice`.`sendOn` AS `adminCalendarNotice.sendOn`
FROM `Notices` AS `Notice` 
INNER JOIN `AdminCalendarNotices` AS `adminCalendarNotice` ON `Notice`.`id` = `adminCalendarNotice`.`noticeId`
ORDER BY `adminCalendarNotice`.`sendOn` DESC 
LIMIT 0, 10000000000000;

But needing the limit, I found that this would work:

order: [
  [sequelize.literal('`adminNotice.sendOn` DESC')],
]

turning the sql into:

SELECT `Notice`.* 
FROM (
  SELECT `Notice`.`id`, `adminNotice`.`noticeId` AS `adminNotice.noticeId`, `adminNotice`.`sendOn` AS `adminNotice.sendOn`
  FROM `Notices` AS `Notice` 
  INNER JOIN `AdminNotices` AS `adminNotice` ON `Notice`.`id` = `adminNotice`.`noticeId` 
  ORDER BY `adminNotice.sendOn` DESC LIMIT 0, 30
) AS `Notice`
ORDER BY `adminNotice.sendOn` DESC;

you can try using seperate: true in included model

[shubhanshu74156]

I spent hours on this. Workaround that worked for me is,

order: [['includeTable1', 'includeTable2', 'column', 'ASC']]

This is three nested query. My query looked like table.findAll(include: {..., as: includeTable1,seperate: true, include: {..., as:includeTable2}}, order: [['includeTable1', 'includeTable2', 'column', 'ASC']])

try using seperate: true

[m3hransh]

Based on this
https://stackoverflow.com/questions/43729254/sequelize-limit-and-offset-incorrect-placement-in-query
You can use

limit : limit,
subQuery:false 

Somehow avoiding nested query fixed the problem for me.

[jatin-garg-repo]

Based on this https://stackoverflow.com/questions/43729254/sequelize-limit-and-offset-incorrect-placement-in-query You can use

limit : limit,
subQuery:false 

Somehow avoiding nested query fixed the problem for me.

It works but it reduces the results and not give required limited counted results