在Sequelize.js中自定义或覆盖联接 [英] Custom or override join in Sequelize.js

问题描述

我需要使用带有MSSQL的 Sequelize.js 创建自定义联接条件.具体来说，我需要基于 TableA 和 TableB 中的列中的 COALESCE 值加入 TableB ，并最终得到像这样的加入条件:

I need to create a custom join condition using Sequelize.js with MSSQL. Specifically, I need to join TableB based on a COALESCE value from columns in TableA and TableB and end up with a join condition like this:

LEFT OUTER JOIN [TableB]
    ON [TableB].[ColumnB] = COALESCE (
        [TableC].[ColumnC],
        [TableA].[ColumnA]
    )

我在联接中满足了 OR 子句的要求:

I'd settle for an OR clause in my join:

LEFT OUTER JOIN [TableB]
    ON [TableB].[ColumnB] = [TableA].[ColumnA]
    OR [TableB].[ColumnB] = [TableC].[ColumnC]

我已阅读，您可以通过在您的计算机中包含 required:false 来实现这种行为范围定义.如您所见，我已经在尝试使用它的示波器了.

I read that you can achieve behaviour like this by including required: false in your scope definition. As you can see, I've plastered my scope with it attempting to get this to work.

我能得到的最好的是这个(注意 AND 子句):

The best I could get is this (note the AND clause):

LEFT OUTER JOIN [TableB]
    ON [TableB].[ColumnB] = [TableA].[ColumnA]
    AND [TableB].[ColumnB] = COALESCE (
        [TableC].[ColumnC],
        [TableA].[ColumnA]
    )

如果我使用的是MySQL，我想我可以简单地使用 JOIN 中 SELECT 中的 COALESCE 值，这很好但在我先前的研究中，读到需要重新计算该值.

If I were using MySQL, I think I could simply use the COALESCE value from the SELECT in the JOIN and be good to go but in my prior research read that it was required to recalculate the value.

我为 TableA 提供了简化的模型定义:

I've included a stripped down model definition for TableA:

export default (sequelize, DataTypes) => sequelize.define('TableA',
    {
        // Attributes omitted..
    },
    {
        classMethods: {
            associate ({
                TableB,
                TableC
            }) {
                this.belongsTo(TableB, {
                    foreignKey: 'ColumnA',
                    targetKey: 'ColumnB'
                });

                this.belongsTo(TableC, {
                    foreignKey: 'ColumnA',
                    targetKey: 'ColumnC'
                });
            },
            attachScope ({
                TableB,
                TableC
            }) {
                this.addScope('defaultScope', {
                    attributes: [
                        ...Object.keys(this.attributes),
                        [
                            sequelize.fn(
                                'COALESCE',
                                sequelize.col('[TableC].[ColumnC]'),
                                sequelize.col('[TableA].[ColumnA]')
                            ),
                            'ColumnA'
                        ]
                    ],
                    include: [
                        {
                            model: TableB,
                            where: {
                                ColumnB: sequelize.fn(
                                    'COALESCE',
                                    sequelize.col('[TableC].[ColumnC]'),
                                    sequelize.col('[TableA].[ColumnA]')
                                )
                            },
                            required: false
                        },
                        {
                            model: TableC,
                            required: false
                        }
                    ],
                    required: false
                }, { override: true });
            }
        }
    }
);

我们将不胜感激，如果需要任何其他信息，请告诉我.

Any assistance would be greatly appreciated, and if any additional information is required please let me know.

注意:我正在使用旧数据库，但是不幸的是无法更改数据结构.

推荐答案

我遇到了同样的问题，最后我使用SQL纯查询解决了这个问题.

Hi I had the same issue and finally I solved using a SQL pure query.

示例:

const query = `SELECT s.*, us.UserId \
                FROM UserSections AS us \
                RIGHT JOIN Sections AS s ON (s.id = us.SectionId) \
                WHERE s.parentId = ${sectionId}`;

return db.query(query, { type: db.QueryTypes.SELECT });

问题在于此函数将返回IMyType而不是IMyTypeInstance

The problem is that this function will return a IMyType instead of IMyTypeInstance

它能为您工作吗?

这篇关于在Sequelize.js中自定义或覆盖联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！