如何在sequelize中查询多对多关系 [英] How to query many to many relation in sequelize

问题描述

我一直在通过 sequelize 在 postgres db 上使用 feathersjs/nodejs.在我的数据库中，我有用户表和事件表.它们有两倍的关系:

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.

推荐答案

查询嵌套关联不是 Feathers 通用查询语法的一部分.在 Sequelize 的情况下，应根据您的需要使用 params.sequelize 并包括 feathers-sequelize 关联文档中所示::p>

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);
    }

这篇关于如何在sequelize中查询多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！