I have three tables, a Users table, a Profiles table and an Actions table. A User hasOne Profile, and a User hasMany Actions. I would like to retrieve a list of Users, with the Profiles, and the latest Action they took (I'm really only interested in the time of their last action, if that makes a difference).
So far, my query looks like this:
var params = {
where: {active: true},
include: [
{model: this.dbconn.Profile, where: {visible: true}},
{model: this.dbconn.Action, attributes: [
[Sequelize.fn('max', Sequelize.col('Actions.createdAt')), 'seen']
]}
],
};
self.dbconn.User.findAll(params);
This only gives me a single row of results, presumably the lone User who most recently had an Action. The query that it generates looks a little like this:
SELECT `User`.*,
`Profile`.`id` AS `Profile.id`,
(insert other Profile fields here),
`Actions`.`id` AS `Actions.id`,
max(`Actions`.`createdAt`) AS `Actions.seen`
FROM (
SELECT `User`.`id`, (insert other User fields here)
FROM `Users` AS `User`
WHERE `User`.`active` = 1
LIMIT 10
) AS `User`
INNER JOIN `Profiles` AS `Profile`
ON `User`.`id` = `Profile`.`UserId`
AND `Profile`.`visible` = 1
LEFT OUTER JOIN `Actions` AS `Actions`
ON `User`.`id` = `Actions`.`UserId`;
I have discovered that if I take that query and add a GROUP BY `User`.`id` to the end of the query by hand, I get back what I expect - Users joined on Profiles joined on their latest Action date. However, adding group: 'User.id' to params adds the GROUP BY clause to the subquery, which isn't useful.
Is there a way to get what I'm after with Sequelize?
Aucun commentaire:
Enregistrer un commentaire