Sequelize:用不同的条件查询同一个连接表

Sequelize: Query same join table with different conditions(Sequelize:用不同的条件查询同一个连接表)

本文介绍了Sequelize:用不同的条件查询同一个连接表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有两个模型 ContactThread,它们通过连接表 ThreadContacts 表示多对多关系.

I have two models Contact and Thread with a many to many relationship represented across a join table ThreadContacts.

我需要编写一个查询来找到一个与确切的联系人列表有关联的线程.例如,我可能有一个 contact_id 的列表[1,2,3,4],我需要找到与这 4 个联系人相关联的 Thread.

I need to write a query to find a Thread which has associations with an exact list of Contacts. For example, I might have a list of contact_id's [1,2,3,4], and I need to find a Thread that is associated with these exact 4 contacts.

我尝试在 findAll 查询中包含 Contact:

I have tried including Contact on a findAll query:

Thread.findOne({
    include: [{
        model: Contact,
        where: { id: $in: [1, 2, 3, 4] },
    }],
})

当然这不起作用,因为它会返回一个线程,该线程的 ThreadContact 具有 4 个 id 中的任何一个.

Of course this doesn't work because it'll return a thread that has a ThreadContact with any of the 4 ids.

我需要这样的东西:

Thread.findAll({
    include: contactIds.map(id => ({
        model: Contact,
        where: { id },
    }),
})

但是这也不起作用,因为它包含相同模型的重复.

However this also doesn't work because it is including duplicates of the same model.

我在这里有什么选择?我很难找到解决方案.

What are my options here? I'm having a difficult time finding a solution for this.

推荐答案

在 sequelize 中编写更复杂的连接查询时,我通常最终使用 原始查询接口.它看起来有点复杂,但希望它有意义:

When writing more complicated join queries in sequelize, I usually end up using the raw query interface. It looks a bit complicated, but hopefully it makes sense:

  • 选择线程并加入 ThreadContact 表
  • Thread.id
  • 分组
  • 使用 array_agg 在联系人 ID 上.所以我们现在有一个包含每个线程的所有关联联系人的数组.
  • 然后过滤到聚合数组包含"(由 @> 表示)您输入的过滤器的位置.请参阅 postgres 数组函数.
  • Select the Threads and join with the ThreadContact table
  • Group by Thread.id
  • Aggregate the group using array_agg on the contact ids. So we now have an array of all associated contacts for each thread.
  • Then filter to where the aggregated array 'contains' (as represented by @>) your inputted filter. See postgres array functions.

结果将是与至少这 4 个联系人关联的所有线程.

The result will be all Threads which are associated with at least those 4 contacts.

sequelize.query(`
  SELECT Thread.*
  FROM Thread
  INNER JOIN ThreadContact
    ON Thread.id = ThreadContact.threadId
  GROUP BY Thread.id
  HAVING array_agg(ThreadContact.contactId) @> ARRAY[:contactIds];
`, {
  model: Thread,
  mapToModel: true,
  type: sequelize.QueryTypes.SELECT,
  replacements: {contactIds: [1, 2, 3, 4]},
});

另请注意,列名可能与您的模型定义不正确,我只是对它们的外观做了一些假设.

Also note that the column names may be incorrect from how your model is defined, I just made some assumptions on how they would look.

这篇关于Sequelize:用不同的条件查询同一个连接表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:Sequelize:用不同的条件查询同一个连接表

基础教程推荐