CODEWITHSUNDEEP
knex.js
bjlevine
bjlevine

Reputation: 984

Knex.js subselect for 'in' clause as part of join

Banging my head against the wall on this one. Here is one 'on' clause from a fairly complex join:

on kr.id = urm.role_id and kr.client in (select id from client where client.client_id = ?)

I'm trying to find a more elegant way of constructing the subquery, but the best I've come up with so far for the clause after the 'and' is:

.andOn('kr.client','in', knex.raw('select id from client where client.client_id = $2'))

but I feel like there should be some way to form this using andOnIn and a subselect.

Thanks in advance for your advice!

Upvotes: 1

Views: 587

Answers (1)

hawk
hawk

Reputation: 299

I am using objection.js and for me andOnIn + subquery just works. It should also work with vanilla knexjs. Unfortunately, it seems that typings for andOnIn + subqueries are missing, so you may need to // @ts-ignore that or perhaps submit a PR with a fix for typings.

query.leftJoin('kr`, (join) => {
  join
    .on('kr.id', 'urm.role_id')
    .andOnIn('kr.client', knex('client').select('id').where(...));
});

Upvotes: 1

Related Questions