Is it possible to do a query on a joined relationship field

Is it possible to do something like the following:

var person1 = DB.person.all().include(“lnk_city.lnk_suburb”).where(“lnk_city.lnk_suburb = ?”, view.suburb).toArray();

Hi @chansmann

If I understand your data schema and question correctly, you are wanting to query all DB.person objects that share the same lnk_city and lnk_suburb relationship?

For that, you may format your query like so:
DB.person.where('ink_city_id = ? and ink_suburb_id = ?', view.suburb.id, view.city.id).include('ink_city', 'ink_suburb').toArray();

Let us know if this resolves.

Travis

Hi Travis

Thanks for your response so far. Let me re-phrase the question.
On the Journey documentation, at https://docs.journeyapps.com/reference/get-started/journeyapps-fundamentals/accessing-the-database/querying-db-objects, there is an example of the “include” function:

DB.person.all().include(‘city.province.country’, ‘company’).toArray();

My question is, applied to this example, can I do a where clause on this nested relationship?
eg.
DB.person.all().include(“city.province.country”).where(“city.province.country = ?”, value)

I am assuming that there is a “person” object here, that has a relationship with a “city” object, that has a relationship with a “province” object, which has a relationship with a “country” object.

Does this make sense?

@chansmann thanks for clarifying. It isn’t possible to traverse two relationships in a .where() query. For this example a best practice could be to add a person belongs-to country relationship then include the province with the resulting person query collection.

  1. Perform a .where() on the higher order collection (country), and map the resulting ids into an Array
  2. Perform the person query using the in operator. Here is an example with a made up active_business boolean field on country:
var active_countries = (DB.country.where('active_business = ?', true)).map(function (country) {
     return country.id;
})
var cities_with_active_business = DB.city.where('country_id in ?', active_countries).include('province');

Another tip is you want to chain the .include to the end so that the query engine includes only the belongs-tos for the objects within your query results:
DB.person.where().include()

Hi Trevor

I had some difficulty with your example, as the example uses stored ids, which we don’t have in our data models. I tried the ‘in’ operator in the where clause for the belongs-to field on our side, but could not get it to work.

In the end, as we don’t expect that much data for this problem, I used a for loop to implement the query.

Thanks for you quick reply.

Hi @chansmann

Glad you found a path forward for this case. Feel free to post additional query questions as they come up and we’ll be happy to provide input.

Thanks - Travis