sequelize.js - Sequelizejs - 搜索多对多关系

Question

如果用户有许多项目，反之亦然，我可以使用 user.getProjects() 获取所有用户项目 - 但是有什么方法可以搜索这种关系，以便您可以翻阅结果并进行过滤(例如按项目名称)？

谢谢。

https://gist.github.com/be3bafe8903d6b18f4ef.git

// connect
var Sequelize = require("sequelize")
var sequelize = new Sequelize('spike', 'anon', 'password', {
  logging: console.log
});

// models
var User = sequelize.define('User', {
  name: Sequelize.STRING
});

var Project = sequelize.define('Project', {
  name: Sequelize.STRING,
  status: Sequelize.TEXT
});

// associations
Project.hasMany(User, { as: 'User', as: 'contributors' });
User.hasMany(Project, { as: 'contributors' });

// execute
sequelize.sync({force: true}).then(function () {
  User.create({name: 'u1'}).then(function (user1) {
    Project.create({name: 'p1'}).then(function (project1) {
      user1.addContributor(project1).then(function () {
        user1.getContributors().then(function (projects) {
          console.log('User has '+projects.length+ ' projects');
        })
      });
    });
  });
});

Answer 1

答案很简单 - 遵循与其他查询相同的模型:

user1.getContributors({where: {name:'p1'}, offset: 1, limit: 2}).then(function (projects) {
    console.log('User has '+projects.length+ ' projects');
})

这产生了以下 SQL，它对结果集进行分页并对 PROJECT 名称进行过滤:

SELECT 
    `Project` . *
FROM
    (SELECT 
        `Project`.`id`,
            `Project`.`name`,
            `Project`.`status`,
            `Project`.`createdAt`,
            `Project`.`updatedAt`,
            `ProjectsUser`.`createdAt` AS `ProjectsUser.createdAt`,
            `ProjectsUser`.`updatedAt` AS `ProjectsUser.updatedAt`,
            `ProjectsUser`.`UserId` AS `ProjectsUser.UserId`,
            `ProjectsUser`.`ProjectId` AS `ProjectsUser.ProjectId`
    FROM
        `Projects` AS `Project`
    INNER JOIN `ProjectsUsers` AS `ProjectsUser` ON `Project`.`id` = `ProjectsUser`.`ProjectId`
        AND `ProjectsUser`.`UserId` = 1
    WHERE
        (`Project`.`name` = 'p1')
    LIMIT 1 , 2) AS `Project`;

而且，您可以从另一端执行相同的操作 - 例如查询具有用户的项目，并进行适当的过滤:

p2.getContributors({where: {name: 'u1'}}).then(function(users) {
  console.log('Project 1 has '+users.length+' user named u1');
});