Objection.js: Querying a parent model by related properties

[jeff3yan]

I'm trying to filter a parent model based on it's related properties on PostgresQL, e.g.
Country _hasMany_ City _hasMany_ Customers

I want to query all customers who live in a country.

I thought the query would look something like this:

  .query()
  .eagerAlgorithm(Customer.JoinEagerAlgorithm)
  .eager('[city.country]')
  .modifyEager('[city.country]', builder => builder.where('city:country:name', 'United States'))

The eager query works as expected, including the related models on the customer rows.

However, using the alias in the where query results in postgres throwing an error about the column not existing. It looks like this is a known behavior of postgres, since column aliases cannot be used in where queries.

Is this a bug, or have I read the documentation wrong?

[koskimas]

You need to apply the filter on the root query like this:

Customer
  .query()
  .eagerAlgorithm(Customer.JoinEagerAlgorithm)
  .eager('city.country')
  .where('city:country.name', 'United States')

If you don't need the city and country objects you can simply use joinRelation like so:

Customer
  .query()
  .joinRelation('city.country')
  .where('city:country.name', 'United States')

[jeff3yan]

@koskimas that works perfectly, wasn't aware that this was the case. Originally thought it was an issue with objection-find.

Great to hear nested filtering exists with this type of syntax natively, as it's perfect to (partially) expose via API.

[Rfrixy]

Is there a way to make such filters in withGraphFetched also?

[jeff3yan]

@Rfrixy what exactly is the filter you want to apply?

[Rfrixy]

@jeff3yan ty for asking.

I have an Object A related to Object B and B is related to C..

I fetch graph for A - { B: { C }} , so the whole nested result is returned.

Now I want all 'A's where there exists a C = 1...
I do NOT want to filter Cs where C is 1.

[jeff3yan]

@Rfrixy I don't think there's a built-in method to do this arbitrarily for _any_ relation types.

However if the relationship above is exactly how you specified (A _belongsTo_ B _belongsTo_ C), then you could something like:

A.query()
  .leftJoinRelation('b.c')
  .eager('b.c')
  .where('b:c.columnNameInC', 1)

Just a bit of shameless self-promotion, but I do the exact thing in objection-filter - on arbitrarily defined relations.