I have the following SQL query:
select p.id, p.slug, p.post_author, b.id, b.is_deleted from posts p
left join blocks b
on
((b.blocked_id = p.post_author and blocker_id = <user id>)
or
(b.blocked_id = <user_id> and b.blocker_id = p.post_author))
and b.is_deleted = false
where b.id is null
order by posted_at desc limit 20
and need a corresponding objection orm statement, and I'm not sure how to bring in those bracket precedence. Below is what I have as a modifier so far:
getUnblockedUsersPosts: (builder) => {
builder.leftJoin('blocks as b', function(){
this.on('b.blocked_id', '=', 'posts.post_author')
.andOn('b.blocker_id', '=', parseInt(user.id))
.orOn('b.blocked_id', '=', parseInt(user.id))
.andOn('b.blocker_id', '=', 'posts.post_author')
.andOn('b.is_deleted', knex.raw('false'))
}).where('b.id', 'is', null)
},
So above query was solved by the below code, reference: https://knexjs.org/#Builder-join
getUnblockedUsersPosts: (builder) => {
builder.leftJoin('blocks as b', function(){
this.on(function(){
this.on(function(){
this.on('b.blocked_id', '=', 'posts.post_author')
.andOn('b.blocker_id', '=', parseInt(user.id))
})
this.orOn(function(){
this.on('b.blocker_id', '=', 'posts.post_author')
.andOn('b.blocked_id', '=', parseInt(user.id))
})
})
this.andOn('b.is_deleted', knex.raw('false'))
}).where('b.id', 'is', null)
}