Deep queries on associations

[smihaljenovic]

Waterline version: 0.13.1 Node version: 8.9.4 NPM version: 5.7.1 Operating system: MacOs High Sierra 10.13.3

---

I'm not sure if it is a bug or still missing featue. I've been searching group on Gitter, issues on GitHub found some discussions about this issue, but still didn't found a solution.

I have one-to-one relation Users and UserDetails. They are connected with association: details: { model: 'userDetails' }

I tried to sort and paginate my result set by userDetails parameter, eg. displayName.

This is what I tried, and error I get: await User.find({ where: { deleteFlag: false }, limit: 10, skip: 10, sort: 'details.displayName ASC' }).populateAll();

` An error occurred:

{ UsageError: Invalid criteria. Details: Could not use the provided sort clause: Cannot use dot notation as the target for a sort comparator without enabling experimental support for "deep targets". Please try again with .meta({enableExperimentalDeepTargets:true}).

at Object.fn (.../crmApi/api/controllers/user/get.js:75:16) at wrapper (.../crmApi/node_modules/@sailshq/lodash/lib/index.js:3250:19) at Deferred.parley.now.flaverr.name [as _handleExec] (.../crmApi/node_modules/machine/lib/private/help-build-machine.js:1025:27) at Deferred.exec (.../crmApi/node_modules/parley/lib/private/Deferred.js:679:10) at Deferred.switch (.../crmApi/node_modules/machine/lib/private/help-build-machine.js:1306:16) at Object._requestHandler [as user/get] (.../crmApi/node_modules/machine-as-action/lib/machine-as-action.js:1148:27) at .../crmApi/node_modules/sails/lib/router/bind.js:247:46 at routeTargetFnWrapper (.../crmApi/node_modules/sails/lib/router/bind.js:391:9) at .../crmApi/node_modules/sails/lib/router/bind.js:454:14 at Layer.handle [as handle_request] (.../crmApi/node_modules/express/lib/router/layer.js:95:5) at next (.../crmApi/node_modules/express/lib/router/route.js:137:13) at Route.dispatch (.../crmApi/node_modules/express/lib/router/route.js:112:3) at Layer.handle [as handle_request] (.../crmApi/node_modules/express/lib/router/layer.js:95:5) at .../crmApi/node_modules/express/lib/router/index.js:281:22 at param (.../crmApi/node_modules/express/lib/router/index.js:354:14) at param (.../crmApi/node_modules/express/lib/router/index.js:365:14) at param (.../crmApi/node_modules/express/lib/router/index.js:365:14) at param (.../crmApi/node_modules/express/lib/router/index.js:365:14) at Function.process_params (.../crmApi/node_modules/express/lib/router/index.js:410:3) at next (.../crmApi/node_modules/express/lib/router/index.js:275:10) at next (.../crmApi/node_modules/express/lib/router/route.js:127:14) at hasToken (.../crmApi/api/policies/hasToken.js:24:10) name: 'UsageError', code: 'E_INVALID_CRITERIA', details: 'Could not use the provided sort clause: Cannot use dot notation as the target for a sort comparator without enabling experimental support for "deep targets". Please try again with .meta({enableExperimentalDeepTargets:true}).' } `

Then I added .meta({enableExperimentalDeepTargets:true}), and got error: ` An error occurred:

{ UsageError: Invalid criteria. Details: Could not use the provided sort clause: Cannot use dot notation to sort by a nested property of details because the corresponding attribute is not capable of holding embedded JSON data such as dictionaries ({}) or arrays ([]). Dot notation is not currently supported for sorting across associations (see https://github.com/balderdashy/waterline/pull/1519 for details).

at Object.fn (.../crmApi/api/controllers/user/get.js:75:16) at wrapper (.../crmApi/node_modules/@sailshq/lodash/lib/index.js:3250:19) at Deferred.parley.now.flaverr.name [as _handleExec] (.../crmApi/node_modules/machine/lib/private/help-build-machine.js:1025:27) at Deferred.exec (.../crmApi/node_modules/parley/lib/private/Deferred.js:679:10) at Deferred.switch (.../crmApi/node_modules/machine/lib/private/help-build-machine.js:1306:16) at Object._requestHandler [as user/get] (.../crmApi/node_modules/machine-as-action/lib/machine-as-action.js:1148:27) at .../crmApi/node_modules/sails/lib/router/bind.js:247:46 at routeTargetFnWrapper (.../crmApi/node_modules/sails/lib/router/bind.js:391:9) at .../crmApi/node_modules/sails/lib/router/bind.js:454:14 at Layer.handle [as handle_request] (.../crmApi/node_modules/express/lib/router/layer.js:95:5) at next (.../crmApi/node_modules/express/lib/router/route.js:137:13) at Route.dispatch (.../crmApi/node_modules/express/lib/router/route.js:112:3) at Layer.handle [as handle_request] (.../crmApi/node_modules/express/lib/router/layer.js:95:5) at .../crmApi/node_modules/express/lib/router/index.js:281:22 at param (.../crmApi/node_modules/express/lib/router/index.js:354:14) at param (.../crmApi/node_modules/express/lib/router/index.js:365:14) at param (.../crmApi/node_modules/express/lib/router/index.js:365:14) at param (.../crmApi/node_modules/express/lib/router/index.js:365:14) at Function.process_params (.../crmApi/node_modules/express/lib/router/index.js:410:3) at next (.../crmApi/node_modules/express/lib/router/index.js:275:10) at next (.../crmApi/node_modules/express/lib/router/route.js:127:14) at hasToken (.../crmApi/api/policies/hasToken.js:24:10) name: 'UsageError', code: 'E_INVALID_CRITERIA', details: 'Could not use the provided sort clause: Cannot use dot notation to sort by a nested property of details because the corresponding attribute is not capable of holding embedded JSON data such as dictionaries ({}) or arrays ([]). Dot notation is not currently supported for sorting across associations (see https://github.com/balderdashy/waterline/pull/1519 for details).' } `

Then I tried this: await User.find({ where: { deleteFlag: false }, limit: 10, skip: 10 }) .populate('details', { sort: 'displayName ASC' }) .meta({enableExperimentalDeepTargets:true}).log(); And got error: ` An error occurred:

{ UsageError: Invalid populate(s). Details: Could not populate details because of ambiguous usage. This is a singular ("model") association, which means it never refers to more than one associated record. So passing in subcriteria (i.e. as the second argument to .populate()) is not supported for this association, since it generally wouldn't make any sense. But that's the trouble-- it looks like some sort of a subcriteria (or something) was provided! (Note that subcriterias consisting ONLY of omit or select are a special case that does make sense. This usage will be supported in a future version of Waterline.)

Here's what was passed in: { sort: 'displayName ASC' }

at Object.fn (.../crmApi/api/controllers/user/get.js:75:16) at wrapper (.../crmApi/node_modules/@sailshq/lodash/lib/index.js:3250:19) at Deferred.parley.now.flaverr.name [as _handleExec] (.../crmApi/node_modules/machine/lib/private/help-build-machine.js:1025:27) at Deferred.exec (.../crmApi/node_modules/parley/lib/private/Deferred.js:679:10) at Deferred.switch (.../crmApi/node_modules/machine/lib/private/help-build-machine.js:1306:16) at Object._requestHandler [as user/get] (.../crmApi/node_modules/machine-as-action/lib/machine-as-action.js:1148:27) at .../crmApi/node_modules/sails/lib/router/bind.js:247:46 at routeTargetFnWrapper (.../crmApi/node_modules/sails/lib/router/bind.js:391:9) at .../crmApi/node_modules/sails/lib/router/bind.js:454:14 at Layer.handle [as handle_request] (.../crmApi/node_modules/express/lib/router/layer.js:95:5) at next (.../crmApi/node_modules/express/lib/router/route.js:137:13) at Route.dispatch (.../crmApi/node_modules/express/lib/router/route.js:112:3) at Layer.handle [as handle_request] (.../crmApi/node_modules/express/lib/router/layer.js:95:5) at .../crmApi/node_modules/express/lib/router/index.js:281:22 at param (.../crmApi/node_modules/express/lib/router/index.js:354:14) at param (.../crmApi/node_modules/express/lib/router/index.js:365:14) at param (.../crmApi/node_modules/express/lib/router/index.js:365:14) at param (.../crmApi/node_modules/express/lib/router/index.js:365:14) at Function.process_params (.../crmApi/node_modules/express/lib/router/index.js:410:3) at next (.../crmApi/node_modules/express/lib/router/index.js:275:10) at next (.../crmApi/node_modules/express/lib/router/route.js:127:14) at hasToken (.../crmApi/api/policies/hasToken.js:24:10) name: 'UsageError', code: 'E_INVALID_POPULATES', details: 'Could not populate details because of ambiguous usage. This is a singular ("model") association, which means it never refers to more than one associated record. So passing in subcriteria (i.e. as the second argument to .populate()) is not supported for this association, since it generally wouldn\'t make any sense. But that\'s the trouble-- it looks like some sort of a subcriteria (or something) was provided!\n(Note that subcriterias consisting ONLY of omit or select are a special case that does make sense. This usage will be supported in a future version of Waterline.)\n\nHere\'s what was passed in:\n{ sort: \'displayName ASC\' }' } `

Am I missing something, or this is still not implemented?

[sailsbot]

@smihaljenovic Thanks for posting, we'll take a look as soon as possible.

---

For help with questions about Sails, click here. If you’re interested in hiring @sailsbot and her minions in Austin, click here.

[ReallyNotARussianSpy]

+1 for implementing this

[oaksofmamre]

hey @smihaljenovic in your last query I notice some spaces that probably shouldn't be there. Also try it without the .log() since it looks like the query isn't being run in the console but in your code

[Paskalouis]

Hi,

any update for this issue ? I may need the waterline to support this.

[johnabrams7]

@smihaljenovic @sreed101 @oaksofmamre @Paskalouis - Hey everyone, we're currently moving all the Waterline issues to Sails (balderdashy/sails) for greater progress monitoring. Thanks for all the input so far, I'm unsure of the current status on this one for Waterline but this will now have greater community exposure in our primary Sails issues hub.

[xarmin-dev]

Just ran into this exact situation myself as well and it does appear to be a bug. I'm surprised more people haven't run into this, it seems like it would be a pretty common issue.

[johnabrams7]

Hi @wh1t3kn1ght, thanks for the update. What version of waterline standalone or sails(waterline integrated) are you using that reproduces this situation? If you have a waterline standalone or sails app repo of this, even better. Checking the latest status of this with the team.

[xarmin-dev]

This is Sails v1.1.0. I did some more digging and found the cause of this. It turns out it's super simple. My issue may be different, but was giving me very similar errors.

My code is running two functions:

await Model.find(query).populateAll();
await Model.count(query.where);

It seems that when the populateAll() function is used, Waterline modifies the "query" object using its reference. Otherwise, it appears to be cloned to prevent mutation of the original object passed as a parameter.

Hence, the original input:

{ where: { adoptedAt: 0, owner: [ 1 ] } }

Is mutated into the following (passed in turn to Model.count in my code):

{ and: [ { 'pet.adoptedAt': 0 }, { 'pet.owner': { in: [ 1 ] } } ] }

Which of course will fail, as "pet.adoptedAt" and "pet.owner" don't exist on the model.

[xarmin-dev]

Just tested using Sails 1.2.3, and the behavior is still present. I couldn't find anything about it in the documentation and the query reference doesn't appear to be consistently modified by Waterline, so I assume this is unintended. I'll find a workaround for now.

[johnabrams7]

Thanks the further insight with later versions of Sails. Will definitely note this in our plans to have it looked into further. Appreciate the efforts on a workaround, will provide any updates from our end as well. In the meantime, I also welcome additional community workarounds / experiences.

[vishalbiradar]

I am also getting the same issue in sails 1.0 version. Any solution for this issue.

[xarmin-dev]

Hi @vishalbiradar,

Waterline mutates the query argument passed to the find() function when using populate(). One possible solution is to clone the query object before passing it to Waterline:

const query = { where: { adoptedAt: 0, owner: [ 1 ] } };
const clonedQuery = JSON.parse(JSON.stringify(query)); // Deep clone query

await Model.find(clonedQuery).populateAll(); // Mutates clonedQuery
await Model.count(query.where); // Does not mutate query

// query can continue to be used after this point
// clonedQuery is mutated can not be used after this point

Another possible solution is to change the order of the functions you're calling:

const query = { where: { adoptedAt: 0, owner: [ 1 ] } };

await Model.count(query.where); // Does not mutate query
await Model.find(query).populateAll(); // Mutates query

// query is mutated and can not be used after this point

Hope that helps!

[johnabrams7]

@vishalbiradar @Paskalouis @smihaljenovic Hey everyone, what particular adapter(s) are you using with this one? @wh1t3kn1ght Thanks for the workaround info!

[Paskalouis]

@vishalbiradar @Paskalouis @smihaljenovic Hey everyone, what particular adapter(s) are you using with this one? @wh1t3kn1ght Thanks for the workaround info!

Hi, I was using postgreSQL adapter. https://github.com/balderdashy/sails-postgresql

[johnabrams7]

@Paskalouis Thanks for the adapter info.

Deep query is essentially an experimental feature that isn’t supported in sails-postgresql, so this syntax isn't recommended to be used with associations. It's intended exclusively for JSON, so this particular error message shouldn’t really be displayed - we can update this with a better error message.

For further context: deep query is useful in a database table, as it allows you to sort by a piece of the cell value and it’s natively supported in Mongo (and likely also PostgreSQL), but it's not supported in the ORM.

[TheAdamGalloway]

This bug still exists. I'm using the mysql adapter.

[eashaw]

Hi @TheAdamGalloway, enableExperimentalDeepTargets is not supported in sails-mysql.

[TheAdamGalloway]

@eashaw It feels like this should be made more clear, as I have been getting the error mentioned while using the mysql adapter.

[luisarganaraz]

Hi! is there any update about this bug? I need to sort a collection by one attribute from the associated model and I get the same error. I would be great to have this working. I can´t figure it out in other way.

[eashaw]

Hi @luisarganaraz, this feature is experimental and we recommend using sendNativeQuery() instead.

[iborschca]

I tweaked config/blueprints.js and added enableExperimentalDeepTargets = true for my particular action