Sails: Add support for .populate({ select: [] })

[anasyb]

The select is being ignored.. getting back the full records instead of just the titles.

Model.find().populate(assoc.alias, { select: ['title']});

Not sure if its only on mongo?

UPDATE @dmarcelino: changed this issue into a feature request.

[tjwebb]

I did not know you could select only certain attributes. Can you point to the docs on this?

[devinivy]

Possibly related to #509, which will be a part of 0.11.

[anasyb]

I have been reading the code which seems to be written as if this is the behaviour... populate.js and deferred.js

The only documentation I found is here

Maybe it's still work in progress?

[anasyb]

Do you guys know when that _insertJoinedResults which contains childRow = _.pick(childRow, select); is supposed to be called? I can't seem to be able to catch it with a breakpoint!

[dmarcelino]

I know waterline supports "populate where": .populate('taxis', { medallion: { '<': 2 }}) (find.populate.where.js#L38) but I think "populate select" would be a new feature.

[devinivy]

If someone thinks this isn't full answered, feel free to reopen. But as of now, there's no select criteria.

[atiertant]

shoud be a feature request

[anasyb]

subscribers: sorry for the buzz :P nothing interesting to read.
Although this was about select, I just wanted to add reference to this issue for anyone landing here... few _other_ things you can do with populate sub-criteria:
https://github.com/balderdashy/waterline/issues/334

[dmarcelino]

Thanks @anasyb, that's valuable information. Some of it is actually in the docs (https://github.com/balderdashy/waterline-docs/blob/master/query.md#populate) but sort is not mentioned. Care to do a PR? :smiley: :

Meanwhile at @atiertant's request I've changed this issue into a feature request and reopened it.

[andriussev]

I have created a simple project to test the issue and it seems that waterline with the disk adapter (the default one) does work great with populate's selects.
Switching it out to mysql removes the functionality and the select attribute is ignored.

[dmarcelino]

@nonpc, can you submit a patch to waterline-adapter-tests? There is already a find.populate.where.js suite of tests, so perhaps add a find.populate.select.js in https://github.com/balderdashy/waterline-adapter-tests/tree/master/interfaces/associations/manyToMany

[andriussev]

I have actually mostly fixed the issue but I don't feel good enough to do a pull request.
The fix is also not in the correct file since the query to join (it's just a seperate select) has "SELECT *..." hardcoded.

Changing the beginning of the query to:
https://github.com/nonpc/waterline-sequel/blob/8c7eada04f4dbe6e43ca3d88b4183e264bd0e06f/sequel/where.js#L192

Most of the logic resides here, a lot of copy-pasteing done from the select.js file:
https://github.com/nonpc/waterline-sequel/blob/8c7eada04f4dbe6e43ca3d88b4183e264bd0e06f/sequel/where.js#L234
Changes go until line 271.

Mind you, I've only tested it on MySQL.

I am actually not a fan of doing these seperate queries on simple joins so I myself am going to change all of the populate() to .query() and do all the joins myself until (if ever) all this gets fully solved.

[dmarcelino]

I see, if you do find a fix you're comfortable with please let us know, thanks.

[ghost]

:+1: this would be a good feature

[atiertant]

@nonpc the problem is you will need to correct it in 3 times :

• verify that populate add correct select in joins passed to adapter or PR waterline to correct it
• PR waterline-adapter-test a failling test.
• PR waterline-sequel to correct the hardcoded * .

@dmarcelino would you be ok with this ? do you think it's a feature or a bug ?
as select is a criteria and populate takes a criteria parameter,i think it's a bug ...

[dmarcelino]

The first 2 are definitely right. I'm not sure about "PR waterline-sequel to correct the hardcoded * ." as I'm not aware of such hardcode. I'll trust your judgement there.

Feature or bug... definitions are not always easy... :) Given that only recently select was documented and added to waterline-adapter-tests (balderdashy/waterline-adapter-tests#71: 26 days ago) I'll vote for feature.

[barroudjo]

Just to let you guys know: this feature will come in handy !

[iamvajid]

Can't we use the methods like :toJSON or other custom methods on models to override the returning result ? And will this work on an array of result ?

[shamasis]

This would significantly improve performance for deletions of highly nested populations.

[shamasis]

I've even tried modifying deffered.prototype.populate to send correct selects in the joins that it builds internally. Still no solution.

Is select on joins supported at all?

[atiertant]

@shamasis i think modifying deffered.prototype.populate is the best way but this hasn't been tested at all in any adapter so the we need :

• modifying deffered.prototype.populate to add correct select in joins passed to adapter.
• PR waterline-adapter-test to test that select in joins are correctly used.
• correct adapters ...

[shamasis]

@atiertant I've already implemented the correct join selects on a fork of waterline we maintain. Almost all adapters we tested does not respect the select criteria in joins. That's what I am working next. Will post updates and send PR when I can get around to it.

[devinivy]

That would be great. If there is a common pattern to convert the adapters, do share!

[sailsbot]

Thanks for posting, @anasyb. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

• review our contribution guide to make sure this submission meets our criteria (only _verified bugs_ with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
• create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

[nicco]

any news on this?

[sbeluzo]

this would be a nice feature :+1:

[tom2strobl]

As being new to waterline and therefore without understanding it's history I'm very surprised this is not implemented. I read it in the docs and used it without issues in the sails-memory implementation. It feels very weird to switch to a relational adapter and have this relational feature missing.

So basically .populate() only makes sense if you want the whole thing, right? Do I have other options than making manual nested find calls if I don't want the whole db object?

[78lab]

+1

[akmoulai]

I'd like to add my +1 because any non trivial API would highly benefit from this kind of functionality to avoid leaking too much information.

[shamasis]

Have taken a shot at it. It's not perfect since it does a bit of string manipulation at the ORM level. But it works. Any way I can get directions as to where in waterline the selects of join query instructions are prepared?

[alexventuraio]

I am pretty interested in this new feature on Sails.
Does any body have solved this need? Can anybody share a posible solution for this issue?

[shamasis]

This feature is useful. The sails-mysql-transaction project has a solution, but I feel it needs more work. It's very easy to select a subset of data and end up having bugs without even knowing it.

There should be some form of error if model instance is used to access a property that has not been selected. Otherwise, it's on the developer to keep track on what he has sent to the subselection.

[particlebanana]

select is not officially supported anywhere in the waterline query syntax. Would someone submit a PR to the Roadmap file to add this?

[didil]

+1

[shamasis]

@particlebanana - in our fork of waterline, I've added support for select in both strategy 1 and 2 joins. Ensuring that primary and foreign keys are also populated. Would you be interested in a PR? Maybe you could have a look at sails-MySQL-transactions code and let me know.

It involves changes in waterline as well as waterline-sequel.

[particlebanana]

Ok I added this to the roadmap. Projections are not officially supported in Waterline. It's something I'm interested in and should make it's way into core in the near future.

[rasekar2000]

+1

[Tronix117]

:+1:

[particlebanana]

https://github.com/balderdashy/waterline/pull/1310

[particlebanana]

This has been merged along with official support for .select() and will be published in the next version of Waterline (0.12.0).

[didil]

:+1:

[alexventuraio]

Excellent I'll love it.
@particlebanana Can you please share an example please, I'm working with sails v0.12.1. I would like to testing it.

[xueenda]

I think there is another bug for this select. I have a query need to populate two objects like

  .populate('partner', { select: ['_id', 'name', 'avatar']})
  .populate('user', { select: ['_id', 'name', 'avatar']})

Here is the response:
{
"partner": {
"name": "Jessica",
"avatar": "http://www.movenoticias.com/wp-content/uploads/2015/11/Jessica-Alba-850x594.jpg",
"id": "56be42c3c282eaecad0f53f3"
},
"user": {},
...
}

The user object isn't populate. But when I remove the select from populating user, it will return the full user object.

Can someone take a look? I am install sails.js and waterline both.

"sails": "~0.12.3",
"waterline": "^0.12.1"

[atiertant]

@xueenda if you want some help, you should post your models definitions and some sample to reproduce bug... which adapter do you use? did you tryed with an other one?

[billyshena]

@particlebanana Any news about supporting .select() with a new version of Sails?

[driftman]

@billyshena i just tried with populate and it is working like a charm v 0.12.3 populate('relation', { select: ['wantedFields', ...] });

[asergey87]

+1

[DavidVeloso]

@particlebanana Pls, Any news about supporting .populate({ select: [] }) with Sails v0.12 (sails-hook-orm) ?

[khalilifar]

Still no support for this ?

[netr0m]

As @driftman pointed out, it works perfectly.
Model.find().populate('relationship', { select: ['attribute', 'attribute', 'attribute']}).exec(...)

[rstrange1992]

@mortea15 I am using Sails v0.12.13 with the following code
Page.find(query, select).populate('structures', { select: ['structure_id'] }).exec(function(err, result) { and the select in the populate does not work, it seems to be ignored.

[netr0m]

@rstrange1992 as far as I can tell, it doesn't work with MySQL, and perhaps other DBs. I switched to MongoDB, which works fine.

[rstrange1992]

@mortea15 It doesn't seem to be working with Postgresql either, just so that other people know.

[atiertant]

it should work with MYSQL, Postgresql and others with the adapter offshore-sql

[anshumanr]

This still does not work for mysql on sails 0.12.14

[luizcarraro]

same as @anshumanr

[jevho]

Same for for mysql on sails 0.12.14
Can be waterline updated separately to fix this?

[abrantes01]

Hello guys, any news on this issue ?

[tskweres]

is this supported?

[TahaBoulehmi]

What happened to this feature? Is it supported? It doesn't work on Sails 1.2.3.

[TahaBoulehmi]

I made some tests. It works currently only with a population of one-to-many relationship.

I made a pull request to solve the one-to-one relationship issue:
https://github.com/balderdashy/waterline/pull/1613

[marcoshmendes]

I made some tests. It works currently only with a population of one-to-many relationship.

I made a pull request to solve the one-to-one relationship issue:

1613

Any news about it?

[TahaBoulehmi]

I made some tests. It works currently only with a population of one-to-many relationship.
I made a pull request to solve the one-to-one relationship issue:

1613

Any news about it?

I'm still waiting for someone from the sails team to review the pull request. If you really need to solve the issue immediately just update lib/waterline/utils/query/forge-stage-two-query.js like this:

https://github.com/balderdashy/waterline/pull/1613/commits/682f1e3adc244f4ebf95e06e6593e9ff73a4b016

I did it in most of my projects and until now I didn't face any problems with the fix. But as the pull request has not yet been accepted, please make sure to have a backup.