Sequelize.js:连接没有关联的表 [英] Sequelize.js: join tables without associations

问题描述

有没有办法在 sequelize 中加入没有使用 include 定义的关联的表?这不是 this 的副本.我说的是完全没有关联但有我想加入的列的表.

Is there a way to join tables that don't have associations defined using include in sequelize? This is not a duplicate of this. I am talking about tables that are not associated at all but having columns that I want to join on.

例子:

select * from bank
left outer join account 
    on account.bank_name = bank.name

上述查询将返回表 bank 中的所有记录，无论是否存在适用指定约束的 account 记录.

The above query will return all records in table bank regardless of the existence of an account record where the specified constraints apply.

如果模型 bank 和 account 在 account.bank_name = bank.name 上关联，则在 sequelize 中将如下所示:

In sequelize this would look something like the following, if models bank and account were associated on account.bank_name = bank.name:

bank.findAll({
    include: [{
        model: account,
        required: false,
    }]
})

但是，如果模型没有关联怎么办?有没有办法编写我自己的自定义 on 部分或等效部分:

However, what if the models are not associated? Is there a way to write my own custom on section or equivalent:

bank.findAll({
    include: [{
        model: account,
        required: false,
        on: {
            bank_name: Sequelize.col('bank.name')
        }
    }]
})

我隐约记得读过一些关于此的内容，但我现在似乎无法在任何地方找到该文档.如果您能指出文档中的正确部分，我们将不胜感激.

I vaguely remember reading something about this but I cannot seem to find that doc anywhere now. If you can point to the correct section in the docs it would be greatly appreciated.

推荐答案

似乎虽然可以定义自定义 on 条件，但无法 include 没有先定义 associations 的关系. 的文档措辞暗示了这一点findAll 方法(在页面上搜索options.include):

It seem that while it is possible to define a custom on condition, it is not possible to include relations without defining associations first. This is implied by the documentation wording for findAll method (search for options.include on page):

使用左连接急切加载的关联列表.支持的是 { include: [ Model1, Model2, ...]} 或 { include: [{ model: Model1, as: 'Alias' }]} 或 { include: ['Alias']}.如果您的关联是用 as 设置的(例如 X.hasMany(Y, { as: 'Z }，您需要在预加载 Y 时在 as 属性中指定 Z).

A list of associations to eagerly load using a left join. Supported is either { include: [ Model1, Model2, ...]} or { include: [{ model: Model1, as: 'Alias' }]} or { include: ['Alias']}. If your association are set up with an as (eg. X.hasMany(Y, { as: 'Z }, you need to specify Z in the as attribute when eager loading Y).

options.include[].on 上的文档更加简洁:

为加入提供您自己的 ON 条件.

Supply your own ON condition for the join.

我最终使用 Postgres 视图作为解决方法解决了我的问题.也可以注入 原始查询 并绕过续集限制，但我只会将其用作开发/原型设计，并在生产中提出更强大/更安全的东西；诸如视图或存储过程之类的东西.

I ended up solving my problem using Postgres views as a workaround. It is also possible to inject a raw query and bypass sequelize limitations but I would use that only as a development / prototyping hack and come up with something more robust / secure in production; something like views or stored procedures.

这篇关于Sequelize.js:连接没有关联的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！