How to query many to many relation in sequelize

问题:

I've been using feathersjs/nodejs over postgres db via sequelize. In my db i have Users table and Events table. They are twice in relation:

events.belongsTo(models.users, {
  foreignKey: {
    name: 'creatorId',
    allowNull: false
  },
  onDelete: 'CASCADE',
  as: 'creator'
});

events.belongsToMany(models.users, {
  through: 'event_participants',
  as: 'participants',
  foreignKey: 'eventId',
  otherKey: 'userId'
});
models.users.belongsToMany(events, {
  through: 'event_participants',
  as: 'events'
});

Everything works just fine, table is created and with include im getting users inside of event as participants. Problem is querying by association. I'm trying to fetch events for current user, so I need events where creator is current user AND events where one of participants is current user. Problem is the second part 'querying where current user is one of participants'. I was expecting something like this

'api/events?$or[0][creatorId]=currUserId&$or[1][participants][$contains]=currUserId'

but its not working cause there is no such column as 'participants' its being included so i cant query it. So for now I'm just fetching all events and filtering them for current user in after hooks, but it just seems wrong. What is the right way to do this?

And yea I know I can get events where user is one of participants by including them into user and fetching him but problem with that is i cant sort that data all together, its being sorted separately and another problem is doin pagination on frontend.

回答1:

Querying for nested associations is not part of Feathers common query syntax. In the case of Sequelize the query should be assembled according to your needs using params.sequelize and includes as shown in the feathers-sequelize associations documentation:

// GET /my-service?name=John&include=1
function (context) {
   if (context.params.query.include) {
      const AssociatedModel = context.app.services.fooservice.Model;
      context.params.sequelize = {
         include: [{
           model: AssociatedModel
           // normal Sequelize where query here
          }]
      };
      // delete any special query params so they are not used
      // in the WHERE clause in the db query.
      delete context.params.query.include;
   }

   return Promise.resolve(context);
}

回答2:

With the help of @daff 's answer I found my solution like this... Error that I was getting can be found here.

const { authenticate } = require('@feathersjs/authentication').hooks;

function includeBefore(hook) {
  currUserId = hook.params.user.id;
  userModel = hook.app.services.users.Model
  hook.params.sequelize = {
    where: {
      $or: [
        {
          creatorId: currUserId
        },
        {
          '$participants.id$': currUserId  //no idea what are '$$' for but it made it work
        }
      ]
    },
    include: [
      {
        model: userModel,
        as: 'creator',
      }, {
        model: userModel,
        as: 'participants',
        duplicating: false //fixed error that I was getting 
      }
    ],
  }
  return hook;
}


module.exports = {
  before: {
    all: [
      authenticate('jwt'),
      hook => includeBefore(hook)
    ],
.........