Additional join condition in Objection.js

Question

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?

Answer 1

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();