Sequelize.js:不带关联的联接表 [英] Sequelize.js: join tables without associations

问题描述

是否有一种方法可以连接未使用续集使用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上关联，则序列化将类似于以下内容:

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条件，但如果不定义 方法(在页面上搜索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):

使用左联接急切加载的关联列表.支持的{包括:[Model1，Model2，...]}或{包括:[{模型:Model1，如:'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屋！