Sequelize - how do I use a where clause inside a nested association?

0
votes

I have a series of Sequelize models that are all associated with each other -

  1. Userand Institution, which are both related to Event through Invitation.
  2. Userand Institution are also associated with each other through the UserDetail model.

I'm trying to query an event and eager load all the institutions that are associated with it. When eager loading the institution, I'm trying to include the users that are associated with that institution, but I only want the users that are also associated with the event. I've tried a query like the one below, but the where clause that says '$events.id$': req.params.eventId doesn't seem to work - I keep getting a missing FROM-clause entry for table \"events\" error on the response from Sequelize, but the SQL output that Sequelize gives me clearly has a FROM \"Events\" AS \"Event\" in it.

Any ideas what I can do to get this query to work?

Query

const event = await Event.findByPk(req.params.eventId, {
        include: [
          {
            model: Institution,
            as: 'institutions',
            attributes: ['id', 'name'],
            through: {
              model: Invitation
            },
            include: [
              {
                model: User,
                as: 'users',
                where: {
                  '$events.id$': req.params.eventId
                },
                attributes: ['id', 'name'],
                through: {
                  model: UserDetails,
                  attributes: []
                },
                include: [
                  {
                    model: Event,
                    as: 'events',
                    attributes: [],
                    through: {
                      model: Invitation,
                      attributes: []
                    }
                  }
                ]
              }
            ]
          }
        ]
      })

Models

// user.js model
User.belongsToMany(models.Institution, {
  through: models.UserDetails,
  as: 'institutions',
  foreignKey: 'userId',
  otherKey: 'institutionId'
})

User.belongsToMany(models.Event, {
  through: models.Invitation,
  as: 'events',
  foreignKey: 'userId',
  otherKey: 'eventId'
})

User.hasMany(models.Invitation, {
  as: 'invitations',
  foreignKey: 'userId'
})

// institution.js model

Institution.belongsToMany(models.User, {
  through: models.UserDetails,
  as: 'users',
  foreignKey: 'institutionId',
  otherKey: 'userId'
})

Institution.belongsToMany(models.Event, {
  through: models.Invitation,
  as: 'events',
  foreignKey: 'institutionId',
  otherKey: 'eventId'
})

Institution.hasMany(models.Invitation, {
  as: 'invitations',
  foreignKey: 'institutionId'
})

// event.js modedl
Event.belongsToMany(models.User, {
  through: models.Invitation,
  as: 'users',
  foreignKey: 'eventId',
  otherKey: 'userId'
})

Event.belongsToMany(models.Institution, {
  through: models.Invitation,
  as: 'institutions',
  foreignKey: 'eventId',
  otherKey: 'institutionId'
})

// invitation.js model
Invitation.belongsTo(models.User, {
  foreignKey: 'userId',
   onDelete: 'CASCADE'
})

Invitation.belongsTo(models.Institution, {
  foreignKey: 'institutionId',
  onDelete: 'CASCADE'
})

SQL Output

SELECT
   \"Event\".\"id\",
   \"Event\".\"name\",
   \"Event\".\"date\",
   \"Event\".\"description\",
   \"Event\".\"venue\",
   \"Event\".\"type\",
   \"Event\".\"notes\",
   \"Event\".\"images\",
   \"Event\".\"createdAt\",
   \"Event\".\"updatedAt\",
   \"institutions\".\"id\" AS \"institutions.id\",
   \"institutions\".\"name\" AS \"institutions.name\",
   \"institutions -> Invitation\".\"id\" AS \"institutions.Invitation.id\",
   \"institutions -> Invitation\".\"eventId\" AS \"institutions.Invitation.eventId\",
   \"institutions -> Invitation\".\"userId\" AS \"institutions.Invitation.userId\",
   \"institutions -> Invitation\".\"institutionId\" AS \"institutions.Invitation.institutionId\",
   \"institutions -> Invitation\".\"paid\" AS \"institutions.Invitation.paid\",
   \"institutions -> Invitation\".\"status\" AS \"institutions.Invitation.status\",
   \"institutions -> Invitation\".\"createdAt\" AS \"institutions.Invitation.createdAt\",
   \"institutions -> Invitation\".\"updatedAt\" AS \"institutions.Invitation.updatedAt\",
   \"institutions -> users\".\"id\" AS \"institutions.users.id\",
   \"institutions -> users\".\"name\" AS \"institutions.users.name\",
   \"institutions -> users -> UserDetails\".\"id\" AS \"institutions.users.UserDetails.id\",
   \"institutions -> users -> UserDetails\".\"userId\" AS \"institutions.users.UserDetails.userId\",
   \"institutions -> users -> UserDetails\".\"institutionId\" AS \"institutions.users.UserDetails.institutionId\",
   \"institutions -> users -> UserDetails\".\"contactPoint\" AS \"institutions.users.UserDetails.contactPoint\",
   \"institutions -> users -> UserDetails\".\"createdAt\" AS \"institutions.users.UserDetails.createdAt\",
   \"institutions -> users -> UserDetails\".\"updatedAt\" AS \"institutions.users.UserDetails.updatedAt\",
   \"institutions -> users -> events -> Invitation\".\"id\" AS \"institutions.users.events.Invitation.id\",
   \"institutions -> users -> events -> Invitation\".\"eventId\" AS \"institutions.users.events.Invitation.eventId\",
   \"institutions -> users -> events -> Invitation\".\"userId\" AS \"institutions.users.events.Invitation.userId\",
   \"institutions -> users -> events -> Invitation\".\"institutionId\" AS \"institutions.users.events.Invitation.institutionId\",
   \"institutions -> users -> events -> Invitation\".\"paid\" AS \"institutions.users.events.Invitation.paid\",
   \"institutions -> users -> events -> Invitation\".\"status\" AS \"institutions.users.events.Invitation.status\",
   \"institutions -> users -> events -> Invitation\".\"createdAt\" AS \"institutions.users.events.Invitation.createdAt\",
   \"institutions -> users -> events -> Invitation\".\"updatedAt\" AS \"institutions.users.events.Invitation.updatedAt\" 
FROM
   \"Events\" AS \"Event\" 
   LEFT OUTER JOIN
      (
( \"Invitations\" AS \"institutions -> Invitation\" 
         INNER JOIN
            \"Institutions\" AS \"institutions\" 
            ON \"institutions\".\"id\" = \"institutions -> Invitation\".\"institutionId\") 
         INNER JOIN
            (
               \"UserDetails\" AS \"institutions -> users -> UserDetails\" 
               INNER JOIN
                  \"Users\" AS \"institutions -> users\" 
                  ON \"institutions -> users\".\"id\" = \"institutions -> users -> UserDetails\".\"userId\"
            )
            ON \"institutions\".\"id\" = \"institutions -> users -> UserDetails\".\"institutionId\" 
            AND 
            (
               \"institutions -> users\".\"deletedAt\" IS NULL 
               AND \"events\".\"id\" = '1'
            )
         LEFT OUTER JOIN
            (
               \"Invitations\" AS \"institutions -> users -> events -> Invitation\" 
               INNER JOIN
                  \"Events\" AS \"institutions -> users -> events\" 
                  ON \"institutions -> users -> events\".\"id\" = \"institutions -> users -> events -> Invitation\".\"eventId\"
            )
            ON \"institutions -> users\".\"id\" = \"institutions -> users -> events -> Invitation\".\"userId\" 
      )
      ON \"Event\".\"id\" = \"institutions -> Invitation\".\"eventId\" 
      AND 
      (
         \"institutions\".\"deletedAt\" IS NULL
      )
WHERE
   \"Event\".\"id\" = '1';
2
node.jspostgresqlexpresssequelize.js
Try to put in first letter uppercase as the double quotes makes the column case sensitive. Something like this: Events. - William Prigol Lopes
Interesting. It still fails if I put '$Events.id$', it's just that the error changes to invalid reference to FROM-clause entry for table \"Events\" and it gives me this extra hint: "hint": "There is an entry for table \"Event\", but it cannot be referenced from this part of the query." - Instead of re-including event under the users query, is there any way I can pipe it down from the top-level Event.findByPk? - Umar Ghouse
Yes, by the way that the sequelize build the SQL. You can try pass this parameter: subQuery:false on your top-level object and you'll see the query without subselects, maybe this solve your problem. (stackoverflow.com/questions/43729254/…) - William Prigol Lopes
Unfortunately subQuery: false didn't work either :( I get the same errors as before. - Umar Ghouse
Can you put the query dump executing with subQuery: false? - William Prigol Lopes

2 Answers

0
votes

Usually many-to-many associations are get by a separate query that's why you cannot add a condition clause for a child association on a parent level.

You can move the condition to include with model: Event but that way you'll get all users with a filtered events by the condition. I suppose that's not what you try to achieve.

You can try to include sequelize.literal with a subquery of filtered events on a user level where like this:

model: User,
                as: 'users',
                where: sequelize.where(sequelize.literal('(EXISTS (SELECT 1 FROM Events e join Invitation i on <here is the rest of subquery>))'), true)
0
votes

The error is due to INNER JOIN, to remove this, do following things:

  1. pull out all the where clause inside include to outside.
  2. use a subQuery: false at outermost level

change your query as :

const event = await Event.findByPk(req.params.eventId, {
        subQuery: false,
        where: {
           ['$users.events.id$']: req.params.eventId
        },
        include: [
          {
            model: Institution,
            as: 'institutions',
            attributes: ['id', 'name'],
            through: {
              model: Invitation
            },
            include: [
              {
                model: User,
                as: 'users',
                attributes: ['id', 'name'],
                through: {
                  model: UserDetails,
                  attributes: []
                },
                include: [
                  {
                    model: Event,
                    as: 'events',
                    attributes: [],
                    through: {
                      model: Invitation,
                      attributes: []
                    }
                  }
                ]
              }
            ]
          }
        ]
      });

Hope it works!

worked for me :)