Need a way to query associated object's attributes

[gregorypierce]

If an object has an associated collection, it is not clear how (or even if its possible) to query into that associated object stuff.

Pet has an Owner
Owner has an emailAddress

How do I select all of the Pets whose owner has an emailAddress == "foo@bar.com"

While its easy to do 2 queries to accomplish this:

1. Find the owner who has an emailAddress == "foo@bar.com" and get that persons id
2. Find all Pets who have the id from query 1

It seems that there would be a better syntax for this:

Pet.findAll({ "owner.emailAddress" : "foo@bar.com" })

Now whether or not this is internally optimized or just syntactic sugar doesn't matter, its much easier to read and write.

Further, if a Pet has a breed how would I select the email addresses of all of the owners whose Pet.breed == "beagle"

Owner.findAll({"pet.breed" : "beagle"})

[ArdentZeal]

Owner.findOne({ emailAddress: "foo@bar.com" }).populate('pets').exec(function(err, owner) {
console.log(owner.pets);
});

[gregorypierce]

Thanks ArdentZeal. I guess what I'm really asking for is would it be possible to get the Mongoose-ish query syntax as well for when we have more complex relational models as this query starts to become nested hell as the relationship gets more complex:

Vets have Pets, Owners, state
Pets have Owners, breed
Owners have Vets, Pets, emailAddress, billingMethod

For the Vets in the state of GA, find the owners emailAddress of the pets whose breed is 'beagle' and whose billingMethod is 'visa'.

Would like to be able to simply do:

Owner.findAll([{"pet.breed": "beagle"}, {billingMethod: "visa"}, {vet.state: "GA"}]).then(function(err,owner){
console.log( owner.emailAddress );
});

So essentially pass in a bag of criteria and have it just walk through the associations/joins/etc and give me what I'm asking for instead of having to perform populates on "my" side. With it being done on the driver side it seems that would allow the query to be optimized better by the driver writers for the particular database.

[particlebanana]

Currently we only support populations not deep queries. The examples you post would only work in mongoose if you are assuming embedded documents. We don't do any embedded records currently so as far as I know even in mongoose you would need to use the populate syntax (I may be off on this?).

We do have populate criteria so you would still get all the owners and you could filter them if they are missing values for the populated values.

Owner.find({ billingMethod: 'visa' })
.populate('pet', { breed: 'beagle' })
.populate('vet', { state: 'GA' })
.exec(function(err, owners) {});

@mikermcneil has a doc with some examples of doing deep query syntax like what you are looking for but I can't find it right now. Mike any idea where that was?

Lets get the populations release out the door as it works for a lot of situations then we can take a pass at making the query interface more fully featured so we can do these complex queries.

The biggest part will be the operation building in the situation where owners live in postgresql, pets live in mongo and vets live in redis or a situation similar to that.

[gregorypierce]

Appreciate your time taking a look at it, and I agree that the multi-datasource query is definitely going to be the most complicated of the problems to solve.

In the interim, the syntax that you have proposed isn't all that bad. It's not as concise as the dot-notation, but not entirely offensive either.

[Globegitter]

As I mentioned in #855 I needed this feature as well with the special case of filtering for null or rather not: null. Which basically means I only want to find records that have a certain relation attached to them. Ideally this would work with and without actually populating that association in the original model.

[eddieajau]

Ok, so how can I help (I can start on this yesterday as it's under the "extremely desirable" label for a current project)? Is there anyone that can guide me to the first places to start looking at how to start scoping the work? In terms of API, are we talking about following what is suggested for Waterline2?

Thanks in advance.

[dmarcelino]

Hey @eddieajau, your help would be most appreciated! I'm not sure how much we can reuse waterline2 since a lot has changed in the last couple of months and waterline2 hasn't been touched since December.

@devinivy, @tjwebb, @particlebanana, what are your thoughts on this?

[devinivy]

@eddieajau I'm not sure what the spec for this was in waterline2. Does someone have a link to a proposed spec? I like this dot notation just fine!

As for scoping work, it's fairly broad. I think ideal first steps would be to,

1. Write this as syntactic sugar, without any adapter-level optimizations, but perhaps keeping them in mind for the future. This must be in place regardless, as waterline should able to act as a shim when an adapter cannot optimize on it's own. (Consider a key-value store such as redis trying to accomplish this task, for example.)
2. Simply imagine how we can delegate optimizations to the adapter more readily. In the long-haul, I imagine @dmarcelino's adapter "features" could become important in telling waterline what functionality it needs to shim. See Add cross adapter tests: final proposal waterline-adapter-tests#64.

Eventually we can support adapter-level/same-connection optimizations, but that's the hard part! I'm sure that whomever implements the waterline shim for this feature will have some concrete insights into how to implement adapter-level optimizations. Investigate the adapter join method and see how useful it might be, a la https://github.com/balderdashy/sails-mysql/blob/master/lib/adapter.js#L506.

I think that writing the shim is a big enough project on its own, honestly!

Edit
All this said, I think the shim does need to be as performant as possible, within reason. Even though it does work for smallish use-cases, I wouldn't really want to see the solution alluded to above, of performing full populates then filtering the result set in memory. I'd prefer ten roundtrips to the database with small result sets to one native join yielding a giant result set that needs processing in-memory.

[dmarcelino]

After reading #469, #1030 and #1102 I'm wondering if we shouldn't also support querying by FKey without the need for dot '.' notation. It seems some people expect the following syntax to work:

  Pet.find({ "owner" : [1, 2] });

This does seem to work in some adapters apparently but it's not included in any tests or docs that I know of.

[stuk88]

+1

[atiertant]

we are thinking about adding this in deep populate (when merged) balderdashy/waterline#1052
@particlebanana @devinivy filter results was our first idea but takes a lot of memory... this could be solved by doing something like this :
with the previous exemple:

Owner.findAll([{"pet.breed": "beagle"}, {billingMethod: "visa"}, {vet.state: "GA"}])

should become:

vet.findAll({select:['Owner_id'],state: "GA"})         // => [55,56,57,58,80]
pet.findAll({select:['Owner_id'],breed: "beagle"})   // => [25,32,57,58,205]

Owner.findAll(id : [57,58],billingMethod: "visa"})

what do you think about this solution ? have you got a better one?

[devinivy]

The way I see this, Node.js is pretty good at I/O, and not so good at CPU-intensive tasks. So the tradeoff of making more queries is fine with me. I think there still may be a sweet spot between extra queries and extra filtering that we're not hitting yet. This is a hard problem! It would be great if there were some indicator which side of a many-to-many relationship is most efficient. Perhaps we should take dominant sides into account here.

[atiertant]

@devinivy could you explain me what you would like to optimize in many-to-many ?
@devinivy if you wouldn't like CPU operations,and if @dmarcelino 's request is working,we could do request in series like this:

Owner.findAll([{"pet.breed": "beagle"}, {billingMethod: "visa"}, {vet.state: "GA"}])

become

vet.findAll({select:['Owner'],state: "GA"})         // => [55,56,57,58,80]
pet.findAll({select:['Owner'],breed: "beagle",Owner: [55,56,57,58,80]}) // => [57,58]
Owner.findAll(id : [57,58],billingMethod: "visa"})

of course it use less node cpu but request must be done in series so it takes more time...