This is my subquery:
const subquery = async () => {
return await knex.select('*')
.from('contracts')
.where('estate_id', '=', estateid)
}
And this is how I'm inserting it into the join statement:
const data = await knex('clients')
.join(await subquery(), 'clients.client_id', 'contracts.client_id')
But this result in an error, my question is what is the proper way to write a subquery and then include it inside a join statement, using async/await.
In the docs there is a mention on subqueries but is not for the join case and I fail at trying to adapt it to a join case:
knex('users').where('votes', '>', 100)
const subquery = knex('users')
.where('votes', '>', 100)
.andWhere('status', 'active')
.orWhere('name', 'John')
.select('id');
knex('accounts').where('id', 'in', subquery)
No need to await the subquery, you can just pass a function representing the subquery as the first argument to the join. i.e.
const data = await knex('clients')
.join(function() {
this.select('*')
.from('contracts')
.where('estate_id', '=', estateid);
}, 'clients.client_id', 'contracts.client_id');
Note that the reference to knex changes from knex to this once inside the subquery. If you want to keep the subquery separate you should be able to do it like
const subquery = function() {
this.select('*')
.from('contracts')
.where('estate_id', '=', estateid)
}
const data = await knex('clients')
.join(subquery, 'clients.client_id', 'contracts.client_id')