Query on Included Model Properties

[zbarbuto]

This is a follow-on from my comment in strongloop/loopback#517. It would be great if there was some way to perform queries on models through related model properties easily through the default query parameters.

The closest issues I could find were:
#94
#166

Consider the following models:
Store, Customer and Order.
Store hasMany Customers. Customer hasMany orders.

And consider I want to execute the following two queries:

1. Find me stores with male customers who have made purchases over $100
2. Find me customers who have not made any purchases.

There does not seem to be a way to do either of these without executing SQL directly via ds.connector.execute.

Case 1 for example, you could use the following:

Store.find({
  include: {
    relation: 'customer',
    filter: {
      where: { gender: 'male'}
      include: {
        relation: 'order',
        scope: {
          where: { purchasePrice: { gt: 100 } }
        }
      }
    }
  }
});

But this doesn't really do what I want because it will just return all stores and will have a series of dead paths that need to be pruned out manually. It would be great if we could add a flag to ONLY include the base model where the paths exist.

In case 2, I don't even believe this is possible through the default query methods.

Ideally, we could include a relation as something and then query on properties of that relation as is possible with SQL and a required property to flag that you only really care about models where the relation matching the query exists.

Case 2 might look like

Customer.find({
  include: { relation: 'order', required: true}
})

Case 1 might look like.

Store.find({
  include: { relation: 'customer', as: 'c', required: true,
   scope: { include: { relation: 'order', as: 'o', required: true } }
  },
  where: { 'c.gender': 'male', 'o.price': {gte: 100} }
});

[jeff3yan]

The required property seems appropriate, particularly as it's already an established feature using ORMs such as sequelize.

[jannyHou]

@zbarbuto I am trying to help you figure out what's the proper queries for your requirements, could you fork this repo https://github.com/strongloop/loopback-sandbox and replace with your code to reproduce your problem? The sample app with some sample data would be enough for debug. Thanks!

[zbarbuto]

Apologies haven't had a chance to get to the sandbox yet. Will be doing so tomorrow hopefully.

[zbarbuto]

I have setup a sandbox with the test case here:
https://github.com/zbarbuto/1042-loopback-query-included-properties

There is a boot script that seeds some random stores as well as customers for each store and purchases for some customers.

Cheers

[jannyHou]

@zbarbuto Thanks for the sandbox really appreciate it.
We can not realize your scenario by one query now since we don't support JOIN. We are planning to add this feature but it requires a lot of refactor.

If I understand correctly, the two cases you mentioned, one using require and the other one using as, are suggestions of the signature of JOIN. Very valuable ideas!

BTW, we are using issue: strongloop/loopback#683 to keep track of applying filter on properties, part of JOIN feature, you can take a look if interested.

I feel we'd better to create an issue for JOIN and then move and gather all related discussions there:
#1092

[zbarbuto]

Thanks for the prompt responses @jannyHou . I didn't find loopback 683 in my searching because I assumed it was a datasource juggler issue. It's a shame that this is still an issue given that that ticket was created two years ago as I know these types of queries come up all the time. Hopefully loopbackio/loopback-connector#42 can be merged soon..

Given you have created #1092 I am happy to move discussion there and close this issue.