sequelize: Sequelize adding ORDER BY to subquery and main query causing ER_BAD_FIELD_ERROR
What you are doing?
When I add these two scopes, my publishedAt order gets added twice, once to the subquery (which is what i want), and once to the encasing query which causes a ER_BAD_FIELD_ERROR
FIELD_ERROR: Unknown column ‘Ticket.published_at’ in ‘order clause’
If I remove it from the outer query, the query works as expected, so my question is why would it be being added to both queries?
// Scope 1
published: function() {
var data = {
where: {
status: 'published',
publishedAt: {
$lt: models.sequelize.fn('NOW')
}
},
order: [
['publishedAt', 'DESC']
]
};
return data;
},
// Scope 2
decorated: function() {
var includes = {
include: [{
model: models.Asset,
as: 'FeaturedAsset'
}, {
model: models.Category
}, {
model: models.User,
as: 'Author'
}]
};
return includes;
}
What do you expect to happen?
I wanted it to order the inner query
What is actually happening?
It added the order to both inner and outer query.
SELECT ... FROM (
SELECT ... FROM `tickets` AS `Ticket` WHERE (`Ticket`.`deleted_at` IS NULL AND (`Ticket`.`status` = 'published' AND `Ticket`.`published_at` < NOW() AND `Ticket`.`channel_id` = 3))
ORDER BY `Ticket`.`published_at` DESC LIMIT 0, 6) AS `Ticket` ...
ORDER BY `Ticket`.`published_at` DESC;
Dialect: mysql Database version: 5.6 Sequelize version: latest
About this issue
- Original URL
- State: closed
- Created 8 years ago
- Reactions: 1
- Comments: 32
http://docs.sequelizejs.com/manual/querying.html#ordering
Same problem here! Someone has a solution?
For me same issue I have tried with the latest version (5.7.0).
It happens the same to me. I have a relationship “through”. When I order the elements in order of added, this error occurs to me. Above all along with limit and offset.
Any updates until now? it is been 6 years passed since the issue came up
Any updates until now? it is been 5 years passed since the issue came up
yes use in the order this
Is there any solution available for this, In my case I cannot do SubQuery as false, because I also have to apply the limit.
still an issue on sequelize 6.29, can someone give an update?
does adding
required:true
to either user or address help?I have the same issue. It’s not valid to add the clause to both queries, so I’m not sure why it’s happening. This issue needs to be reopened.
Is there a fast way to solve this issue?