findAll with include and multiple conditions throws "missing FROM-clause entry for table" error

[MtDalPizzol]

Hi guys! I have one table which keeps track of some "Businesses" and another one wich says wich of this Businesses are "ClosedBusinesses". I need to get the businesses that are not closed. Here's my code:

    model.Business.findAll({
        where: Sequelize.and(
            { filler_level_id: lv }, 
            {"ClosedBusiness.business_id": null }),
        include: [
            {
                model: model.ClosedBusiness,
                attributes: ['business_id','status'],
                required: false
            }, {
               model: model.RealEstate,
               attributes: ['id', 'address']
            }
        ],
    }).then(function(business) {
        fn(null, business);
    });

I'm getting the following error:

SequelizeDatabaseError: missing FROM-clause entry for table "ClosedBusiness"
    at module.exports.Query.formatError (/home/ubuntu/workspace/app/node_modules/sequelize/lib/dialects/postgres/query.js:301:16)
    at null.<anonymous> (/home/ubuntu/workspace/app/node_modules/sequelize/lib/dialects/postgres/query.js:64:21)
    at emit (events.js:95:17)
    at Query.handleError (/home/ubuntu/workspace/app/node_modules/pg/lib/query.js:99:8)
    at null.<anonymous> (/home/ubuntu/workspace/app/node_modules/pg/lib/client.js:166:26)
    at emit (events.js:95:17)
    at Socket.<anonymous> (/home/ubuntu/workspace/app/node_modules/pg/lib/connection.js:109:12)
    at Socket.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:765:14)
    at Socket.emit (events.js:92:17)

And here is the query I need:

SELECT * FROM business 
    LEFT JOIN closed_business ON (
        business.id = closed_business.business_id)
    WHERE closed_business.business_id IS NULL
    AND
        business.filler_level_id = 2

[mickhansen]

What does the failing query look like?

[mickhansen]

Combining regular where and include based where in top level where isn't supported at the moment i'm afraid, we're working on a solution.

[MtDalPizzol]

Sequelize thows the error before it can even log the query. =/
Any other way I can work around this?

[mickhansen]

include.where won't since you need a negative query.
You could probably try a raw where. .and({filter_level: ...}, ['RAW QUERY']})

[MtDalPizzol]

Hmmm... I think I'll just create a "closed" attribute on the Business table. At least until the solution you guys are working on came out....

[jez]

Not sure if it'd be best to report here or open a new issue, but I just got this with a nested include combined with an ORDER BY

var includeParams;

includeParams = [
  {
    model: models.Spot,
    include: [
      {
        model: models.User,
        as: 'Holder'
      }
    ]
  }, {
    model: models.User,
    as: 'Owners'
  }
];

models.Queue.findAll({
  include: includeParams,
  order: 'Queue.displayName ASC'
}).then(function(queues) {
  return res.json(queues);
});

And I only get the error on PostgreSQL (SQLite doesn't complain, even though it looks like the query is the same).

[janmeier]

@jez Please show the model definitions and associations as well

[jez]

I've put up my models in this gist.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[satnam-sandhu]

Still an issue :(

[renefournier]

Would be nice to fix. (Version three can do this, btw.) In the meantime, guess we're back to writing raw queries.

[aliciagyt]

Still an issue :/