时间戳

首页 > 解决方案 > Objection.js 中的附加连接条件

javascript - Objection.js 中的附加连接条件

问题描述

我正在使用 Objection.js Node.js ORM 编写以下类型的查询：

return Account.query()
    .alias('accounts')
    .whereIn('accounts.ID', accountIds)
    .where('accounts.DELETE_FLAG', 'N')
    .where('accounts.ISDELETED', false)
    .withGraphJoined('contacts')
    .where('contacts.DELETE_FLAG', 'N')
    .where('contacts.ISDELETED', false)
    .execute();

目前这正在生成一个查询，如：

select accounts.*, contacts.* from accounts
left join contacts on (accounts.ID = contacts.ACCOUNTID)
where accounts.ID in (...)
    and contacts.DELETE_FLAG = 'N'
    and contacts.ISDELETED = false

我不需要将两个contacts条件作为普通 where 子句的一部分添加到查询中，而是需要将其添加到连接条件中，例如：

select accounts.*, contacts.* from accounts
left join contacts on (accounts.ID = contacts.ACCOUNTID)
    and (contacts.DELETE_FLAG = 'N')
    and (contacts.ISDELETED = false)
where accounts.ID in (...)

我无法在文档中找到如何执行此操作。有没有人有什么建议？

标签： javascriptsqlnode.jsmariadbobjection.js

解决方案

You can use modifyGraph, the query will be a little different in the sense that it will not add filters to the left join but will use a sub-select as join. Nonetheless the result will be the same as filtering in join with the plus of maintaining the graph feature:

return Account.query()
.alias('accounts')
.whereIn('accounts.ID', accountIds)
.where('accounts.DELETE_FLAG', 'N')
.where('accounts.ISDELETED', false)
.withGraphJoined('contacts')
.modifyGraph('contacts', , builder => {
    builder.where('DELETE_FLAG', 'N')
           .where('ISDELETED', false)
 )

.execute();

推荐阅读