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?

What is the difference in those queries? Doesn't query planner optimize them to be the same? — Mikael Lepistö, Sep 27 '21 at 09:22
At the time I was running into a difference, but I forgot to include it in the question and I forget what the problem was now. According to https://dataschool.com/how-to-teach-people-sql/difference-between-where-and-on-in-sql/ it appears there is a slight difference, but it sounds like I should be leaning towards the syntax that Knex is creating here instead of trying to add conditions to the join clause. — Chad, Sep 28 '21 at 14:12

score 0 · Answer 1 · answered Feb 18 '22 at 06:59

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

filtering with subqueries has a limit: one can filter a subquery using other JOINed tables. That's how SQL works. So even though in many cases it is possible to get the result using "subqueries", there are a lot of cases when you really need to use an addition `ON` condition with references to one of the already joined tables. — maxkoryukov, Aug 26 '23 at 20:54

Additional join condition in Objection.js

1 Answers1