在 Sequelize.js 中自定义或覆盖连接

2022-01-19 00:00:00 database javascript sql-server sequelize.js

我需要使用 Sequelize.js 和 MSSQL 创建自定义连接条件.具体来说，我需要根据 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]

我读您可以通过在您的范围定义.正如你所看到的，我已经用它贴上了我的示波器，试图让它发挥作用.

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

它对你有用吗??

相关文章