I'm writing the following sort of query using 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();
currently this is generating a query like:
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
Instead of the two contacts conditions being added to the query as part of the normal where clause, I need it to be added to the join condition like:
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 (...)
I'm having trouble finding how to do this in the documentation. Does anyone have any suggestions?
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();