sequelize: limit + offset doesn't work together with where clause in include
The models I’ve created:
var Post = sequelize.define('post', {
title: Sequelize.STRING,
content: Sequelize.TEXT
}),
User = sequelize.define('user', {
username: Sequelize.STRING
}),
Interest = sequelize.define('interest', {
name: Sequelize.STRING
})
User.hasMany(Post, {as: 'posts', foreignKey: 'userId'})
Post.belongsTo(User, {as: 'author', foreignKey: 'userId'})
User.belongsToMany(Interest, {as: 'interests', through: 'users_interests'})
Interest.belongsToMany(User, {as: 'users', through: 'users_interests'})
inserted data:
sequelize.sync({force: true}).then(function(){
var chainer = new Sequelize.Utils.QueryChainer()
async.auto({
users: function(callback) {
User.bulkCreate([
{username: "tyler"},
{username: "skyler"},
{username: "hector"}
]).done(function(){
User.findAll().done(callback)
})
},
posts: ['users', function(callback, result) {
Post.bulkCreate([
{title: "a", content: "aaa"},
{title: "b", content: "bbb"}
]).done(function(){
Post.findAll().then(function(posts) {
chainer.add(posts[0].setAuthor(result.users[1]))
chainer.add(posts[1].setAuthor(result.users[0]))
chainer.run().done(callback)
})
})
}],
interests: function(callback) {
Interest.bulkCreate([
{name: "walking"},
{name: "sleeping"},
{name: "hunting"},
]).done(function(){
Interest.findAll().done(callback)
})
},
users_interests: ['users', 'interests', function(callback, result) {
chainer.add(result.users[0].setInterests([result.interests[1]]))
chainer.add(result.users[2].setInterests([result.interests[0], result.interests[2]]))
chainer.run().done(callback)
}]
})
}).catch(function(error) {
console.error(error)
})
now I like to get all posts from users who have the interest sleeping, using limit and offset. I do it as follows:
Post.findAll(
{
limit: 3, offset: 0,
include: [{model: User, as: 'author', where: {}, include: [
{model: Interest, as: 'interests', where: {name: 'sleeping'}}
]}]
})
.then(function(posts) {
res.json(posts)
})
.catch(function(error) {
next(error)
})
error:
Unknown column 'user.userId' in 'where clause'
SequelizeDatabaseError: Unknown column 'user.userId' in 'where clause'
at module.exports.Query.formatError (/home/ich/express/node-app/node_modules/sequelize/lib/dialects/mysql/query.js:158:16)
at Query.results.on.on.on.alreadyEnded (/home/ich/express/node-app/node_modules/sequelize/lib/dialects/mariadb/query.js:99:30)
at Query.emit (events.js:95:17)
at Client.fnQueryErr (/home/ich/express/node-app/node_modules/mariasql/lib/Client.js:129:36)
at Client.emit (events.js:95:17)
at Client._processQueries (/home/ich/express/node-app/node_modules/mariasql/lib/Client.js:340:20)
at /home/ich/express/node-app/node_modules/mariasql/lib/Client.js:239:38
at process._tickCallback (node.js:442:13)
It works when I cut out limit and offset, OR the the where clause in the included. The same applies for findAndCountAll()
node v0.10.35 sequelize@2.0.0-rc8 mariasql@0.1.21
About this issue
- Original URL
- State: closed
- Created 9 years ago
- Reactions: 8
- Comments: 55 (12 by maintainers)
In my case, adding
duplicating: falsesolves the issue.Observations
It prevents it from generating subquery
Code Sample
I have the exact same issue and it is driving me nuts 😦 Pagination with some joins and a where clause is basically impossible while this bug is not fixed. Can this get a higher priority please?
@holographix then do a PR and solve it, it’s an open source project, you can do this any time. It’s a shame that in 2017 people think that anything is granted and they deserve stuff. Instead of this kind of behavior you can read all the comments above and learn something useful, like how to ask questions and say things nicely.
This is a rather big issue as the only “workaround” would be to load the entire table and splice the array. Try to do that on a table with millions of entries… I hope you have some ram and cpu time to spare… This happens on EVERY kind of pagination attempt, if the data is somehow associated and has a where in it.
I also don’t get what’s so hard about this to change? The limit/offset currently are applied to the include subquery, it just has to be applied to the main query (or the last inner join). There is NO need for another query or something. The offset/limit currently is simply applied to the wrong sub query. This is a clear bug in sequelize and not just a feature request.
Example:
Wrong:
correct:
Generated by:
you can use
duplicating: falseon the included tables and probably solve the problem. LE: or you can usedistinct: truewhere needed.I also have same kind of issue when adding limit and offset after an include. It seems the JOINed tables are missing in sub-query in the generated SQL.
Here is my case (using sequelize 2.0.0-rc8):
The SQL
The error
I’d like to add that you indeed can get around this problem by simply adding your limit as a string to the order
Personally I’m happy with that and haven’t stumpled upon an issue.
@smithaitufe
inside of the include models worked for me too
Thank you guys for the thread.
Still, not mention of it in the official Docs.
Okay, I created a small test that isolates the issue for me. It is actually not the limit,offset but the cause has its root in the fact that I use camel case for the fields in my javascript and underscores in my database. If I then offset/limit, there is no way I can order anymore. Run the following code and see for yourself. I am getting
Unhandled rejection SequelizeDatabaseError: Unknown column 'post.title_string' in 'order clause'and it does not matter whether I use “title_string” or “titleString” in the order clause.So for me the quick solution would be to stop using camel case field names in my code.
for my own case, when I remove the LEFT OUTER JOIN by adding
required: truefor every includes i need it worked.Great, thanks @janmeier. I’ll give it a try. In other projects I’ve often ended up pulling in knex for building raw queries when I need to circumvent the ORM, maybe I don’t have to this time.
Has this bug been fixed? I’m having the same issue with version 3.23.4
Generated SQL statement
and the error
Models:
I have examined the pagination problem with associated where clause problems in this article. https://malicaliskan.medium.com/e50dbc9de01d You can find why the problem occurs, in what conditions and what can be an easy solution to the issue in the article.
https://github.com/sequelize/sequelize/issues/9869#issuecomment-510393362
This is fixed by https://github.com/sequelize/sequelize/pull/9188 , I request thread participants to verify this