sequelize: Nested include with limit produce broken query
What are you doing?
Hi Everyone
First thank you so much for this amazing orm, 🙂 have been using for years.
currently am having a problem with a simple query.
basically i want to get chatRoom ordered by the last message (nested include)
so am having a simple m:n relationship with 3 tables chatUser, Chat(rooms), messages.
Models are as below
// code here
// message model
var Message = sequelize.define('Message', {
msg: { type: DataTypes.STRING, allowNull: false },
provider: {type: DataTypes.STRING},
msgType: {type: DataTypes.STRING, defaultValue: "text"} //other value 'song'
});
Message.associate = function (models) {
models.Message.belongsTo(models.Chat, {
foreignKeyConstraint: true,
foreignKey: 'chatId'
});
models.Message.belongsTo(models.User, {
foreignKeyConstraint: true,
foreignKey: 'userId'
});
}
//chat model
var Chat = sequelize.define('Chat', {
hash: { type: DataTypes.STRING, allowNull: false },
name: { type: DataTypes.STRING }
});
Chat.associate = function (models) {
models.Chat.hasMany(models.UserChat, {
foreignKeyConstraint: true,
foreignKey: 'chatId'
});
models.Chat.hasMany(models.Message, {
foreignKeyConstraint: true,
foreignKey: 'chatId'
});
}
// UserChat used to store rooms id for each room participant
var UserChat = sequelize.define('UserChat', {
});
UserChat.associate = function (models) {
models.UserChat.belongsTo(models.User, {
foreignKeyConstraint: true,
foreignKey: 'userId'
});
models.UserChat.belongsTo(models.Chat, {
foreignKeyConstraint: true,
foreignKey: 'chatId'
});
}
// am trying to have a query to fetch chat rooms ordered by last message sent, so i would do that
// include the db models
var models = require('../models');
models.UserChat.findAll({
offset: offset,
limit: 20,
where: {
userId: userId
},
order: [[{ model: models.Chat }, { model: models.Message }, 'createdAt', 'DESC']], // order by last message
include: [
{
model: models.Chat, include:
{ model: models.Message, order: [['createdAt', 'DESC']], limit: 1 } // only include the last message
}
]
})
What do you expect to happen?
i should be having list of UserChats
UserChat: [{ // ordered by last message sent
Chat: {
Messages: [{
}] // array but limited to the last message
}
}]
What is actually happening?
i got error message “Unknown column ‘Chat->Messages.createdAt’ in ‘order clause’”
Output, SQL
"SELECT `UserChat`.`id`, `UserChat`.`createdAt`, `UserChat`.`updatedAt`, `UserChat`.`chatId`, `UserChat`.`userId`, `Chat`.`id` AS `Chat.id`, `Chat`.`hash` AS `Chat.hash`, `Chat`.`name` AS `Chat.name`, `Chat`.`createdAt` AS `Chat.createdAt`, `Chat`.`updatedAt` AS `Chat.updatedAt` FROM `UserChat` AS `UserChat` LEFT OUTER JOIN `Chat` AS `Chat` ON `UserChat`.`chatId` = `Chat`.`id` WHERE `UserChat`.`userId` = 216 ORDER BY `Chat->Messages`.`createdAt` DESC LIMIT 0, 20;"
i even tried to remove the orderby in the nested include, add subQuery:false but with no luck the problem only fix if i removed the limit in the nested include which will fetch all messages in that chat room!!!
Dialect: mysql Sequelize version: 4.28.6
Any thoughts will be much appreciated 🙂
Thanks, Mohamed
About this issue
- Original URL
- State: closed
- Created 7 years ago
- Comments: 15 (1 by maintainers)
I actually just fixed my issue by setting
subQuery: falsein the findAll object. @Mohamed-Habshey you might want to try that. not sure if it will fix that in your case, but worth a shotHi @maxbaun
Thank you for your suggestion, subQuery will work for 1:1 relation however won’t work on 1:m as my case, because subQuery do the limit on how many modals will be included in the result; in your case, it will be how many (Projects+ Sorts) not how many Pojects
so it could be working for your case however in mine i have to include list of users in that chat group as well. so for ex. when fetching chat groups with the limit =4 and we have a chat group that has 6 users, i will got 1 chat group only not 4 groups cause the limit won’t be on the chat groups it will be calculated on how many modals included (chat group and last message and 2 users only) so when i got the next page(offset) i will see this chat group again with the other 2 users
so this solution isn’t valid for me 🙂 i thought i should explain it here to help others as well,
also as @rlindgren references this is an old issue with sequlieze,
Hope it got fixed sooner
Thanks, Mohamed
I Had a similar problem @Mohamed-Habshey, and solved it by adding two orders, the first one for the key your really want to order, then a second one, refering to the main model. It did the trick for me, although I was using mssql… but it’s worth a shot. It would look like:
#8360 could help, using separate: true in hasMany associations
anyone? 🙂
@tatianacmh Unfortunately after revisiting this issue after some time, for the case where A–HasMany–>B and we want to return all Model A (with limit) where
using
separate: truein the include for B means that there is no FROM-clause entry for B, leaving us back at square one. Thanks for the suggestion though!@tatianacmh This seems to be working for me so far!
Hi @blaisax
Thank you so much for your suggestion, however unfortunately it didn’t work for me producing same error, am using MySQL.
Thanks again 🙂 Regards.