node.js - sequelize中如何设置复杂的join条件？

Question

我正在尝试使用 sequelize 对数据库查询进行编码。这正是我要转录的句子:

SELECT matches.uid_prop, matches.uid_player, matches.porcentaje, users.uid, profile_users.name, profile_users.profile, profile_users.birthday, profile_users.location
FROM (matches JOIN users ON (((matches.uid_prop = users.uid) AND (matches.uid_player = 11)) OR ((matches.uid_player = users.uid) AND (matches.uid_prop = 11)))) JOIN profile_users USING(uid)
WHERE (matches.uid_player = 11) OR (matches.uid_prop = 11);

我不知道是否应该更改关联以根据需要构建连接条件

Matches.belongsTo(User, {as: 'match_prop', foreignKey: 'uid_prop'});
User.hasMany(Matches, {as: 'user_prop', foreignKey: 'uid_prop'});

Matches.belongsTo(User, {as: 'match_player', foreignKey: 'uid_player'});
User.hasMany(Matches, {as: 'user_player', foreignKey: 'uid_player'});

或添加一个 where 并在包含匹配项和用户之间指定关系。如果是这样的话，我怎么能从与用户一起制作的包含中的 where 语句中引用匹配字段，以便我可以进行比较，例如 User.uid 和 Matches.uid_prop。我试过这种表示法，但没有用:$Matches.uid_prop$

Matches.findAll({
                attributes: ['porcentaje'],
                where: {
                    $or: [
                        {uid_prop: uid},
                        {uid_player: uid}
                    ]
                },
                include: [{
                    model: User,
                    attributes: ['uid'],
                    include: [{
                        model: Profile_user,
                        attributes: ['name', 'profile', 'birthday', 'location']
                    }]
                }]
            })

Answer 1

我想你所需要的只是required : true ( INNER JOIN ) like :

Matches.findAll({
                    attributes: ['porcentaje'],
                    where: {
                        $or: [
                            {uid_prop: uid},
                            {uid_player: uid}
                        ]
                    },
                    include: [{
                        model: User,
                        attributes: ['uid'],
                        required : true , //<-------- HERE -------
                        include: [{
                            model: Profile_user,
                            attributes: ['name', 'profile', 'birthday', 'location']
                        }]
                    }]
                })

您的查询正在创建 LEFT JOIN，要使其完全成为 INNER JOIN 力，您可以使用 required : true。