Objection.js: How to add condition to a relation join

[demisx]

Hi. I have another newbie question. I've got this relation defined, i.e. any given ad has one owner through a join table:

    owner: {
      relation: BaseModel.HasOneThroughRelation,
      modelClass: '../user/user.model',
      join: {
        from: 'Ads.id',
        through: {
          from: 'UsersAds.adId',
          to: 'UsersAds.userId',
          extra: ['type', 'anonEmail'],
        },
        to: 'Users.id',
      },
    },

However, I need to limit this relation to "UsersAds"."type"='ao'. I've tried adding filter: property, but that adds where clause to the select * from "Users" query and I need the condition added to the join itself. Something like this:

select "Ads"."id", "Ads"."title", "owner"."id", "owner"."email", "owner_join"."type" from "Ads" 
  left join "UsersAds" as "owner_join" on "owner_join"."adId" = "Ads"."id" and "owner_join"."type" = 'ao'
  left join "Users" as owner on  "owner_join"."userId" = "owner"."id";

I've read the documentation a couple of times, but still not clear how to achieve this. Any help will be highly appreciated.

[demisx]

Sorry, don't mean to rush - just got a deadline hanging over me. Can you please tell me if this is even doable in current Objection.js? I've read https://vincit.github.io/objection.js/ a couple of times already, but can see yet how this can be achieved. Feel free to point me out to any other docs I need to read up on. Thank you so much!

[devinivy]

Might this work?

   filter: { 'UserAds.type': 'ao' }

If not, can you show exactly what you've attempted as filter values?

[demisx]

@devinivy Doesn't look like it. It simply ads a where clause to the select query (select "Users".* from "Users" where "UserAds"."type" = ?) like this and fails with an error.

select "Ads"."id", "Ads"."title", "Ads"."description", "Ads"."extras", "Ads"."photos", "Ads"."location", 
...
left join "UsersAds" as "owner_join" on "owner_join"."adId" = "Ads"."id" 
left join (select "Users".* from "Users" where "UserAds"."type" = ?) as "owner" on "owner_join"."userId" = "owner"."id" where "Ads"."id" = ?'
---
{ error: missing FROM-clause entry for table "UserAds"
    at Connection.parseE (/Users/dmoore/projects/dimaslist/dimaslist-org-api/node_modules/pg/lib/connection.js:553:11)
    at Connection.parseMessage (/Users/dmoore/projects/dimaslist/dimaslist-org-api/node_modules/pg/lib/connection.js:378:19)
    at Socket.<anonymous> (/Users/dmoore/projects/dimaslist/dimaslist-org-api/node_modules/pg/lib/connection.js:119:22)

I've also tried adding this to relation with the same result as above:

filter: (qb: QueryBuilder<User>) => qb.where('UserAds.type', 'ao')

modify: (qb: QueryBuilder<User>) => {
    qb.where(owner_join.'type', 'ao')
},

[demisx]

As a temporary workaround, I've commented out .leftJoinRelation('owner') and using left joins directly in the select query (see below). Would be nice not have to do it each time and use "owner" relation instead:

```ts
const ad = await Ad.select([
'Ads.id',
...
// .leftJoinRelation('owner')
.leftJoin('UsersAds as owner_join', (join) => {
join.on('Ads.id', '=', 'owner_join.adId').andOn(raw('owner_join.type = ?', 'ao'))
})
.leftJoin('Users as owner', (join) => {
join.on('owner_join.userId', '=', 'owner.id')
})

[jeff3yan]

Don't think this is possible at the moment. Not sure if this is applicable to you, but is it possible to use a view to pre-filter out the join table? Then you could temporarily use this view to join through, then swap it out into application logic if it's implemented in the future.

[demisx]

@jeff3yan Thank you for the great idea. I am going to give it a try!

[demisx]

Creating a PG view and putting that condition in there indeed worked. Thanks @jeff3yan!