如何查询多对多关系

本文介绍了如何查询多对多关系的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我一直在通过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来组合查询a>并包含在羽毛-sequelize关联文档中:

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