sequelize: Deep eager load chains does not produce valid sql, regression from sequelize 3

What are you doing?

When upgrading from sequelize 3, i noticed that some lookups does no longer produce valid sql.

'use strict';

const Sequelize = require('sequelize');
const Op = Sequelize.Op;

let Model, ModelB, ModelC, ModelD, ModelE;

/**
 * @param {boolean} useEnum
 */
async function test(useEnum) {
  const db = new Sequelize('test', 'user', 'user', {
    host: '127.0.0.1',
    dialect: 'postgresql',
    "quoteIdentifiers": true, 
    "underscored": true
  });  

  Model = db.define('Model', { not_id : Sequelize.INTEGER }, {});
  ModelB = db.define('ModelB', { model_id : Sequelize.INTEGER }, {});
  ModelC = db.define('ModelC', { modelB_id : Sequelize.INTEGER }, {});
  ModelD = db.define('ModelD', { modelC_id : Sequelize.INTEGER, modelE_id : Sequelize.INTEGER }, {});
  ModelE = db.define('ModelE', {}, {});

  Model.hasMany(ModelB, {foreignKey: 'model_id'});
  ModelB.belongsTo(Model, {foreignKey: 'model_id'});

  ModelB.hasMany(ModelC, {foreignKey: 'modelB_id'});
  ModelC.belongsTo(ModelB, {foreignKey: 'modelB_id'});

  ModelC.hasMany(ModelD, {foreignKey: 'modelC_id'});
  ModelD.belongsTo(ModelC, {foreignKey: 'modelC_id'});

  ModelD.belongsTo(ModelE, {foreignKey: 'modelE_id'});
  ModelE.hasMany(ModelD, {foreignKey: 'modelE_id'});

  await db.sync({ force: true });

  await populateDb();

  console.log('Find that does not reproduce the issue')
  const t1 = await Model.findOne({where: {id:1}, include:[
    {
      model:ModelB,
      required: true,
      include:[
        {
          model:ModelC,
          required: true,
          include:[
            {
              model:ModelD,
              required: true,
              include:[
                {
                  model:ModelE,
                  required: true
                }
              ]
            }
          ]
        }
      ]
    }
  ]});

  console.log('Find that reproduces the issue')
  const t2 = await Model.findOne({where: {not_id:2}, include:[
    {
      model:ModelB,
      required: true,
      include:[
        {
          model:ModelC,
          required: true,
          include:[
            {
              model:ModelD,
              required: true,
              include:[
                {
                  model:ModelE,
                  required: true
                }
              ]
            }
          ]
        }
      ]
    }
  ]});

  await db.close();
}

async function populateDb() {
  const model = await Model.create({ not_id:2});
  const modelB = await ModelB.create({ model_id:model.id });
  const modelC = await ModelC.create({ modelB_id:modelB.id });

  const modelE = await ModelE.create({ });

  const modelD = await ModelD.create({ modelC_id:modelC.id, modelE_id:modelE.id });
}

async function main() {
  try {
    console.log('Bad sql');
    await test();
  } catch (err) {
    console.error(err);
    process.exit(1);
  }
}

main();

What do you expect to happen?

I want the findOne to generate valid sql

What is actually happening?

SequelizeDatabaseError: missing FROM-clause entry for table “ModelBs->ModelCs->ModelDs->ModelE”

Output, either JSON or SQL

'SELECT "Model".*, "ModelBs"."id" AS "ModelBs.id", "ModelBs"."model_id" AS "ModelBs.model_id", "ModelBs"."createdAt" AS "ModelBs.createdAt", "ModelBs"."updatedAt" AS "ModelBs.updatedAt", "ModelBs->ModelCs"."id" AS "ModelBs.ModelCs.id", "ModelBs->ModelCs"."modelB_id" AS "ModelBs.ModelCs.modelB_id", "ModelBs->ModelCs"."createdAt" AS "ModelBs.ModelCs.createdAt", "ModelBs->ModelCs"."updatedAt" AS "ModelBs.ModelCs.updatedAt", "ModelBs->ModelCs->ModelDs"."id" AS "ModelBs.ModelCs.ModelDs.id", "ModelBs->ModelCs->ModelDs"."modelC_id" AS "ModelBs.ModelCs.ModelDs.modelC_id", "ModelBs->ModelCs->ModelDs"."modelE_id" AS "ModelBs.ModelCs.ModelDs.modelE_id", "ModelBs->ModelCs->ModelDs"."createdAt" AS "ModelBs.ModelCs.ModelDs.createdAt", "ModelBs->ModelCs->ModelDs"."updatedAt" AS "ModelBs.ModelCs.ModelDs.updatedAt" FROM (SELECT "Model"."id", "Model"."not_id", "Model"."createdAt", "Model"."updatedAt", "ModelBs->ModelCs->ModelDs->ModelE"."id" AS "ModelBs.ModelCs.ModelDs.ModelE.id", "ModelBs->ModelCs->ModelDs->ModelE"."createdAt" AS "ModelBs.ModelCs.ModelDs.ModelE.createdAt", "ModelBs->ModelCs->ModelDs->ModelE"."updatedAt" AS "ModelBs.ModelCs.ModelDs.ModelE.updatedAt" FROM "Models" AS "Model" WHERE "Model"."not_id" = 2 AND ( SELECT "ModelBs"."model_id" FROM "ModelBs" AS "ModelBs" INNER JOIN "ModelCs" AS "ModelCs" ON "ModelBs"."id" = "ModelCs"."modelB_id" INNER JOIN "ModelDs" AS "ModelCs->ModelDs" ON "ModelCs"."id" = "ModelCs->ModelDs"."modelC_id" INNER JOIN "ModelEs" AS "ModelCs->ModelDs->ModelE" ON "ModelCs->ModelDs"."modelE_id" = "ModelCs->ModelDs->ModelE"."id" WHERE ("ModelBs"."model_id" = "Model"."id") LIMIT 1 ) IS NOT NULL LIMIT 1) AS "Model" INNER JOIN "ModelBs" AS "ModelBs" ON "Model"."id" = "ModelBs"."model_id" INNER JOIN "ModelCs" AS "ModelBs->ModelCs" ON "ModelBs"."id" = "ModelBs->ModelCs"."modelB_id" INNER JOIN "ModelDs" AS "ModelBs->ModelCs->ModelDs" ON "ModelBs->ModelCs"."id" = "ModelBs->ModelCs->ModelDs"."modelC_id";'

__Dialect:postgres __Dialect version:(PostgreSQL) 9.6.5 __Database version:(PostgreSQL) 9.6.5 __Sequelize version:4.38.0 __Tested with latest release:4.38.0

Note : Your issue may be ignored OR closed by maintainers if it’s not tested against latest version OR does not follow issue template.

About this issue

Original URL
State: open
Created 6 years ago
Reactions: 20
Comments: 68 (15 by maintainers)

Most upvoted comments

Again, subQuery: false doesn’t fix the problem - while the generated query does work, limit and offset are broken (ie. returning less results than there are, so in 100 correct results, 10 offset, 10 limit, there may - and in fact most of the time will be - less than the expected 10 results).

There is no workaround, apart from creating a custom query. Please, this is a major issue!

+49

zaubara on Mar 21, 2019

Again, subQuery: false doesn’t fix the problem - while the generated query does work, limit and offset are broken (ie. returning less results than there are, so in 100 correct results, 10 offset, 10 limit, there may - and in fact most of the time will be - less than the expected 10 results).

There is no workaround, apart from creating a custom query. Please, this is a major issue!

I there any progress in this? As I see it, it is major issue and seems there is no activity half of the year. (separate: true is not a solution if you use a m:n relations)

+36

l2ysho on Mar 11, 2020

For those suffering from this issue, here’s the workaround I’ve found that is working for me and can work for you:

Event.findAll({
        where: {
            deleted: false,
            '$applications.user_id$': req.user.id
        },
        subQuery: false,  // <----------- this line
        include: [{
            model: Application,
            required: true
        }]
    })

It’s not documented as far as I know though.

+36

serge1peshcoff on Mar 5, 2019

Exactly… Still waiting for a fix

+26

1valdis on Apr 24, 2019

in my case separate: true is working for me inside include object. separate: true will build another query for relations. so that limit and offsetwill excellently work for the parent model.

If separate: true, runs a separate query to fetch the associated instances, only supported for hasMany associations

Example-

db.Users.findAndCountAll({
    distinct: true,
    limit,
    offset,
    include:[
        {
            separate: true, //<- magic
            model: Friends
        },
        {
            separate: true, //<- magic
            model: School,
            include: [
                {
                    model: Class
                }
            ]
        },
    ]
})

+23

yashdabi on Jul 11, 2019

Again, subQuery: false doesn’t fix the problem - while the generated query does work, limit and offset are broken (ie. returning less results than there are, so in 100 correct results, 10 offset, 10 limit, there may - and in fact most of the time will be - less than the expected 10 results).

There is no workaround, apart from creating a custom query. Please, this is a major issue!

In version 6.6.2, this issue remains unsolved!!!

+16

Imfdj on Apr 12, 2021

By the way, subQuery: false fixes SQL, but breaks limit/offset. The only option I found so far to make them work together is to make a custom subquery using QueryGenerator.

+13

1valdis on Oct 29, 2018

Any update on this? separate: true is not working.

honestly, make a raw query

+12

l2ysho on Aug 6, 2020

+10

leo3349 on Oct 28, 2019

I’m working on it but it’s going to be a little while before it’s ready. I’m redesigning findAll from the ground up to make sure all relevant options interact well with each-other (this is the current draft).

ephys on Oct 26, 2022

Hello, any updates to this? Would really love to see a fix.

blankstar85 on Feb 4, 2019

I can confirm that unfortunately the issue is still present in latest Sequelize (6.17). My only advice right now if you’re encountering this issue is to use a raw query.

We’re a bit short on maintainers and the ones that are active already have their hands very full so I can’t promise when a fix will be made available.

If someone is willing to open a PR that includes tests & a fix, we’ll gladly merge it!

ephys on Mar 4, 2022

Really useless ORM, can’t support basic things. Waste of time.

RavenXce on Mar 4, 2022

Can’t believe this hasn’t been addressed after all this time, sequelize isn’t worth the hassle honestly. Jesus. Wrote the raw query in so little time yet wasted ages trying to sort with sequelize

droplessdeclan on Mar 4, 2022

in my case separate: true is working for me inside include object. separate: true will build another query for relations. so that limit and offsetwill excellently work for the parent model.

If separate: true, runs a separate query to fetch the associated instances, only supported for hasMany associations

Example-
db.Users.findAndCountAll({
    distinct: true,
    limit,
    offset,
    include:[
        {
            separate: true, //<- magic
            model: Friends
        },
        {
            separate: true, //<- magic
            model: School,
            include: [
                {
                    model: Class
                }
            ]
        },
    ]
})

Thank you work’s to me 😃

AntonioDev-GolStats on Jan 18, 2020

in my case separate: true is working for me inside include object. separate: true will build another query for relations. so that limit and offsetwill excellently work for the parent model.

If separate: true, runs a separate query to fetch the associated instances, only supported for hasMany associations

Example-
db.Users.findAndCountAll({
    distinct: true,
    limit,
    offset,
    include:[
        {
            separate: true, //<- magic
            model: Friends
        },
        {
            separate: true, //<- magic
            model: School,
            include: [
                {
                    model: Class
                }
            ]
        },
    ]
})

This works for me. Thank you!

boobo94 on Sep 30, 2019

I’m working on it but it’s going to be a little while before it’s ready. I’m redesigning findAll from the ground up to make sure all relevant options interact well with each-other (this is the current draft).

well It looks really promising 👍

l2ysho on Nov 3, 2022

in my case separate: true is working for me inside include object. separate: true will build another query for relations. so that limit and offsetwill excellently work for the parent model.

If separate: true, runs a separate query to fetch the associated instances, only supported for hasMany associations

Example-
db.Users.findAndCountAll({
    distinct: true,
    limit,
    offset,
    include:[
        {
            separate: true, //<- magic
            model: Friends
        },
        {
            separate: true, //<- magic
            model: School,
            include: [
                {
                    model: Class
                }
            ]
        },
    ]
})

Yes, it is working Thanks

pradipb255 on Jan 28, 2020

in my case separate: true is working for me inside include object. separate: true will build another query for relations. so that limit and offsetwill excellently work for the parent model.

If separate: true, runs a separate query to fetch the associated instances, only supported for hasMany associations

Example-
db.Users.findAndCountAll({
    distinct: true,
    limit,
    offset,
    include:[
        {
            separate: true, //<- magic
            model: Friends
        },
        {
            separate: true, //<- magic
            model: School,
            include: [
                {
                    model: Class
                }
            ]
        },
    ]
})

It work’s to me, I was almost getting crazy with it. Thanks

rodrigocipriani on Nov 10, 2019

I still have the same problem in version 5.22.3 separate: true does not work for n: n relationships

HevertonPires on Oct 3, 2020

@tresdev the StackOverflow question (can’t provide it, sorry, I didn’t save it) told so. I decided to check twice and didn’t find anything here: http://docs.sequelizejs.com/class/lib/model.js~Model.html#static-method-findAll

I’ll open another issue on that 😄

serge1peshcoff on Mar 6, 2019

@papigers, I see the similarities, hopefully enough information will soon be gathered so someone that maintains the project can figure out the issue, or issues that may well be the case. If you are looking for workarounds, what I ended up doing was to split the query into 2 smaller chained lookups, which in the end may not be that bad for performance since scheduling of the big sql might not be optimal. Another thing that could perhaps help is to split the where clause up and move it into the includes, not sure if that has any effect, but it looks like the missing table is used in the where part of the generated query.

linpekka on Sep 6, 2018

collin-pham on Mar 23, 2022

This effectively makes it impossible to make any sort of non trivial queries.

Was there any progress on this on the last two years ?

nope

l2ysho on Jan 23, 2021

Hi there,

Did anyone achieve to solve this? I’m using v6.3.4.

Using separated: true didn’t work in my case

Thanks.

Raw query for the moment.

boobo94 on Jan 15, 2021

@justraman It was not solved!

HevertonPires on Dec 9, 2020