vendredi 5 février 2016

Sequelize: Getting MAX() of related table

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