Sequelize with NodeJS can't join tables with limit
Actually I found a solution myself. I think this is a bug in sequelize framework.
In the node_modules/sequelize/lib/dialect/abstract/query_generator.js there is a "selectQuery" function which has the following line:
subQuery = limit && (options.hasIncludeWhere || options.hasIncludeRequired || options.hasMultiAssociation) && options.subQuery !== false
First of all there is an option subQuery that could be passed as false to remove the subquery generation. Sequelize documentation does not have a word about it. But moreover if you pass subQuery:false in the findAll object it's not going to work because for some reason it's getting as underfined to the selectQuery function.
I tried something like:
return models.property.findAll({ where: ["price>=?", 300000], include: [ { model:models.entity_area, where: { area_id:1 } } ], limit:12, subQuery:false})
and still got options.subQuery=undefined.
So i had to change the function in query_generator.js to be something like:
subQuery = limit && (options.hasIncludeWhere || options.hasIncludeRequired || options.hasMultiAssociation) && options.subQuery !== false && options.doSubQuery===true
So now by default it's not doing this ugly subquery unless i specify explicitely doSubQuery:true. And finally i got the proper query without subquery with limit.
models.property.findAll({ where: [...], include: [{...}], limit:12},{ subQuery:false})
I had this problem recently using sequelize 4.28.6, this is what worked for me
User.findAll({ where: { $Tasks$: null, }, include: [ { model: Task, // required: false, }, ], limit: 3, subQuery: false,})
@yuriscom answer still works but, i didnt want to edit the sequelize codebase since the issue has been fixed and adding subQuery: false,
works