Sails: .populate() where with options and IN criteria not working

Created on 20 Mar 2014  ·  32Comments  ·  Source: balderdashy/sails

Using the following query the operations don't build the criteria correctly. 

Model.find()
.populate('foo', { where: [ '1' ], skip: 0, limit: 30, sort: undefined })
.exec(/***/)

cc @mikermcneil

picture particlebanana

Most helpful comment

Populate is not working with where condition.

Application.findOne({
      id: appId,
      status_id: {'!': Status.DELETED}
       select :['user_rating','created_at']
    })
      .populate('candidate',
        {
        status_id: {'!': Status.DELETED},
        select: [
          'first_name',
          'last_name',
          'email',
          'phone',
          'mobile',
          'town'
        ]

       .populate('job',{
         status_id: {'!': Status.DELETED},
        select : ['title']
       })
picture HaiderMalik12 on 9 Mar 2017
👍3

All 32 comments

Should this work?

Game.findOne(id).populate('messages', { limit: 5, sort: 'createdAt DESC' }).exec(/***/);

It seems to ignore the limit and sort.

m19 picture m19 on 4 Jun 2014

+1 on what @m19 said. @particlebanana I tried the same and it's ignoring limit and sort. Funnily enough, I'm using the exact same model name as him, too.

var messageCriteria = {
    limit: 1,
    sort : 'createdAt asc',
  }
,  findQuery = sails.models['thread'].find();

findQuery.populate('from').populate('to').populate('messages', messageCriteria);

findQuery.exec(function (error, results) { /* Magic */ });

results are not limited to 1, nor are they sorted by createdAt.

RWOverdijk picture RWOverdijk on 25 Jun 2014

@particlebanana bump

RWOverdijk picture RWOverdijk on 30 Jun 2014

Any particular adapter? just tried with Waterline 0.10.0-rc14 and sails-disk and the following query seems to be working:

User.find().populate('pets', { limit: 2, sort: 'createdAt DESC' }).exec(console.log);
particlebanana picture particlebanana on 30 Jun 2014

@particlebanana Mongo in my case. ^0.10.0-rc5. Sails at ~0.10.0-rc7

RWOverdijk picture RWOverdijk on 30 Jun 2014

I'm also using Mongo.

m19 picture m19 on 30 Jun 2014

Just tested it out, seems like limit is working for me but sort isn't. If I pull down master of sails-mongo it looks like @mikermcneil worked on moving the join logic into the adapter and I'm seeing the correct results when using that. I'll have to dig in a bit before publishing to npm to make sure everything is looking good. Mind trying the master branch and see if you get any luck?

particlebanana picture particlebanana on 30 Jun 2014

@particlebanana Not at all.

rwoverdijk@battlestation ~/projects/nodejs/islnew/tier1
$ sudo npm install sails-mongo#master

> [email protected] install /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/node_modules/kerberos
> (node-gyp rebuild 2> builderror.log) || (exit 0)

  CXX(target) Release/obj.target/kerberos/lib/kerberos.o
  CXX(target) Release/obj.target/kerberos/lib/worker.o
  CC(target) Release/obj.target/kerberos/lib/kerberosgss.o
  CC(target) Release/obj.target/kerberos/lib/base64.o
  CXX(target) Release/obj.target/kerberos/lib/kerberos_context.o
  SOLINK_MODULE(target) Release/kerberos.node
  SOLINK_MODULE(target) Release/kerberos.node: Finished
[email protected] node_modules/sails-mongo
├── [email protected]
├── [email protected]
├── [email protected]
└── [email protected] ([email protected], [email protected])
rwoverdijk@battlestation ~/projects/nodejs/islnew/tier1
$ sails lift
warn: The package.json in the current directory (/Users/rwoverdijk/projects/nodejs/islnew/tier1) indicates a dependency on Sails ~0.10.0-rc7, but the locally installed Sails (`./node_modules/sails`) is 0.10.0-rc8.

warn: You may consider reinstalling Sails locally (npm install sails@~0.10.0-rc7).
warn: The adapter `sails-mongo` appears to be designed for an earlier version of Sails.
warn: (it has a `registerCollection()` method.)
warn: Since you're running Sails v0.10.x, it probably isn't going to work.
warn: To attempt to install the updated version of this adapter, run:
warn: npm install [email protected]
error: There was an error attempting to require("sails-mongo")
error: Is this a valid Sails/Waterline adapter?  The following error was encountered ::
error: Adapter is not compatible with the current version of Sails.
RWOverdijk picture RWOverdijk on 30 Jun 2014

I jsut installed everything on master (sails-mysql, sails-disk, sails-mongo and sails). Everything is dying now, sorry.

/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/lib/mongodb/connection/base.js:245
        throw message;
              ^
Error: Unknown rule: model
    at Object.matchRule (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/node_modules/anchor/lib/match/matchRule.js:37:11)
    at Anchor.to (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/node_modules/anchor/index.js:76:45)
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/core/validations.js:137:33
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:116:25
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:24:16
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/core/validations.js:121:66
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:108:13
    at Array.forEach (native)
    at _each (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:32:24)
    at Object.async.each (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:107:9)
    at validate (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/core/validations.js:119:11)
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:108:13
    at Array.forEach (native)
    at _each (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:32:24)
    at Object.async.each (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:107:9)
    at Validator.validate (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/core/validations.js:149:9)
    at async.series.runner (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/validate.js:40:25)
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:548:21
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:224:13
    at iterate (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:131:13)
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:142:25
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:226:17
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:553:34
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/validate.js:34:11
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:139:25
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/validate.js:28:13
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/utils/schema.js:137:39
    at runner (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/validate.js:26:11)
    at iterate (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:131:13)
    at Object.async.eachSeries (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:147:9)
    at async.series.runner (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/validate.js:32:15)
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:548:21
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:224:13
    at iterate (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:131:13)
    at async.eachSeries (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:147:9)
    at _asyncMap (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:223:9)
    at Object.mapSeries (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:213:23)
    at Object.async.series (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:546:19)
    at module.exports.validate (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/validate.js:21:11)
    at async.series.runner (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/dql.js:69:14)
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:548:21
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:224:13
    at iterate (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:131:13)
    at async.eachSeries (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:147:9)
    at _asyncMap (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:223:9)
    at Object.mapSeries (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:213:23)
    at Object.async.series (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:546:19)
    at module.exports.create (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/dql.js:65:11)
    at Deferred.exec (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/deferred.js:252:16)
    at userModel.register (/Users/rwoverdijk/projects/nodejs/islnew/tier1/api/models/User.js:77:32)
    at performerUpdateDone (/Users/rwoverdijk/projects/nodejs/islnew/tier1/api/services/ImportService.js:250:24)
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/dql.js:275:11
    at Object.async.each (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/async/lib/async.js:104:20)
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails/node_modules/waterline/lib/waterline/query/dql.js:250:15
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/lib/adapter.js:476:23
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/lib/adapter.js:377:17
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/lib/mongodb/cursor.js:163:16
    at commandHandler (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/lib/mongodb/cursor.js:709:16)
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/lib/mongodb/db.js:1847:9
    at Server.Base._callHandler (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/lib/mongodb/connection/base.js:445:41)
    at /Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/lib/mongodb/connection/server.js:478:18
    at MongoReply.parseBody (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/lib/mongodb/responses/mongo_reply.js:68:5)
    at null.<anonymous> (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/lib/mongodb/connection/server.js:436:20)
    at emit (events.js:95:17)
    at null.<anonymous> (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/lib/mongodb/connection/connection_pool.js:201:13)
    at emit (events.js:98:17)
    at Socket.<anonymous> (/Users/rwoverdijk/projects/nodejs/islnew/tier1/node_modules/sails-mongo/node_modules/mongodb/lib/mongodb/connection/connection.js:439:22)
    at Socket.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:748:14)
    at Socket.emit (events.js:92:17)
    at emitReadable_ (_stream_readable.js:410:10)
    at emitReadable (_stream_readable.js:406:5)
    at readableAddChunk (_stream_readable.js:168:9)
    at Socket.Readable.push (_stream_readable.js:130:10)
    at TCP.onread (net.js:528:21)

The rule "model" might be my association. Did that change?

RWOverdijk picture RWOverdijk on 30 Jun 2014

Updated again, still broken. Tried debugging but I couldn't find the cause.

RWOverdijk picture RWOverdijk on 2 Jul 2014

Could we at least re-open this issue so it doesn't get forgotten?

Update

Just tried again with master, and rc15 (waterline). Still no luck, limit is being ignored. Here's the code of the controller, to make it easier:

/**
 * MessageController
 */

module.exports = {
  inbox: function (req, res) {
    var userId = req.session.user
      , threadCriteria = {
          where  : {
            or: [
              {
                to: userId
              },
              {
                from: userId
              }
            ]
          },
          sort   : 'updatedAt desc' // updatedAt gets upped on new reply.
        }
      , messageCriteria = {
          limit: 1,
          sort : 'createdAt desc'
        }
      , findQuery = sails.models['thread'].find().where(threadCriteria);

    findQuery.populate('from').populate('to').populate('messages', messageCriteria);

    findQuery.exec(function (error, results) {
      if (error) {
        return res.json({error  : error});
      }

      res.json(sails.services['messageservice'].flatten(userId, results));
    });
  }
};
RWOverdijk picture RWOverdijk on 7 Jul 2014

Bump. - _It's starting to get slow here_

RWOverdijk picture RWOverdijk on 15 Jul 2014

I have been playing around with the population of assocations too. I am also experience issues with it. I am currently using sails-postgresql and trying to .populate('replies', {sort: 'sentAt ASC'}) only the whole ORDER criteria is being ignored. For example, I am having the following code:

    Conversation.find(1)
        .populate('firstMessage')
        .populate('lastMessage')
        .populate('replies', {sort: 'sentAt ASC'})
        .exec(function(err, conversations) {
            if (err) {
                return res.serverError(err);
            }

            return res.ok(conversations);
        })

The following queries are being generated:

App 3529 stdout: Query: SELECT "conversations"."subject", "conversations"."type", "conversations"."id", "conversations"."createdAt", "conversations"."updatedAt", "conversations"."first_message_id", "conversations"."last__message_id", "conversations"."conversation_id" FROM "conversations" WHERE "conversations"."id" = $1 [ 1 ]
App 3529 stdout: Query: SELECT "messages"."sender", "messages"."senderName", "messages"."senderProfileType", "messages"."conversation", "messages"."sentAt", "messages"."message", "messages"."id", "messages"."createdAt", "messages"."updatedAt", "messages"."reply_message_id" FROM "messages" WHERE "messages"."id" IN ($1) [ 1 ]
App 3529 stdout: Query: SELECT "messages"."sender", "messages"."senderName", "messages"."senderProfileType", "messages"."conversation", "messages"."sentAt", "messages"."message", "messages"."id", "messages"."createdAt", "messages"."updatedAt", "messages"."reply_message_id" FROM "messages" WHERE "messages"."id" IN ($1) [ 13 ]
App 3529 stdout: Query: SELECT "messages"."sender", "messages"."senderName", "messages"."senderProfileType", "messages"."conversation", "messages"."sentAt", "messages"."message", "messages"."id", "messages"."createdAt", "messages"."updatedAt", "messages"."reply_message_id" FROM "messages" WHERE "messages"."conversation" IN ($1) [ 1 ]

The last query above tries to get all the Message-records for the replies-population only as you can see it's missing the expected ORDER BY "messages"."sentAt" ASC

I am using the following packages:
"sails": "^0.10.0-rc9",
"sails-mongo": "^0.10.0-rc5",
"sails-postgresql": "^0.10.0-rc4",

weyert picture weyert on 24 Jul 2014

I know you're all very busy, but another bump.

RWOverdijk picture RWOverdijk on 1 Aug 2014

This should have received a major update yesterday if using a sequel adapter. We have a new project called waterline-sequel that works with the cursor to run UNION queries when sort is applied. I tested it pretty thoroughly with both mysql and postgresql and it passes all the waterline-adapter-tests. It's on the master branch but they should be pushing to npm today.

particlebanana picture particlebanana on 1 Aug 2014

@particlebanana Does mongo qualify as one? If so I'm more than willing to test it on master.

RWOverdijk picture RWOverdijk on 1 Aug 2014

@RWOverdijk mongo uses wl cursor which achieves the same effect (basically skip the integrator and xD/A join stuff currently in core altogether)

mikermcneil picture mikermcneil on 1 Aug 2014

Awesome. I'll wait for the master branch push thingy. Could you perhaps bump here when that's done?

RWOverdijk picture RWOverdijk on 1 Aug 2014

@RWOverdijk this should be ready to test

mikermcneil picture mikermcneil on 1 Aug 2014

I'll test this today and leave my findings here.

RWOverdijk picture RWOverdijk on 4 Aug 2014

@mikermcneil Tested. This specific issue seems to be resolved. It limits to 1 result, and _(seems to)_ sort the records. I can't be sure about the sorting because my result set is rather small but if it seems to break I'll let you know in a new issue.

Something else seems to be acting up now though. I have this:

  var userId = req.session.user
      , threadCriteria = {
          where  : {
            or: [
              {
                to: userId
              },
              {
                from: userId
              }
            ]
          },
          sort   : 'updatedAt desc' // updatedAt gets upped on new reply.
        }
      , messageCriteria = {
          limit: 1,
          sort : 'createdAt desc'
        }
      , findQuery = sails.models['thread'].find().where(threadCriteria);

    findQuery.populate('from').populate('to').populate('messages', messageCriteria);

And it doesn't sort based on my threadCriteria. It only works if I change:
findQuery.populate('from').populate('to').populate('messages', messageCriteria);
to:
findQuery.populate('from').populate('to').sort('updatedAt desc').populate('messages', messageCriteria);`.

So to clarify, the sort on messageCriteria does work, and the sort on threadCriteria doesn't.

I'm pretty sure there's just something wrong with my criteria object...

RWOverdijk picture RWOverdijk on 4 Aug 2014

Never mind. I'm a big dumb dumb. Someone pointed out that I was using .where(), which I completely missed. Issue resolved, thanks!

RWOverdijk picture RWOverdijk on 4 Aug 2014

i just want to know whether .populate() method is working in sailsjs version 0.9.17.because my model layer is working in sailsjs version 0.11 but this method creating problem in version 0.9.x.its giving error in console like
object[object object] has no method 'populate'.
kindly any help?

pakTech786 picture pakTech786 on 8 Mar 2015

@nailaandleeb Populations were introduced with 0.10.0. It won't work with 0.9 I'm afraid.

RWOverdijk picture RWOverdijk on 8 Mar 2015

@RWOverdijk thanx i doubted about it but can't find answer anywhere.

pakTech786 picture pakTech786 on 8 Mar 2015

sort still isn't working for me.
I'm using
[email protected]
[email protected]

My controller has:
Job.find({}).populate('status', {sort: 'weight ASC'}).exec(function (error, result) { ....

The SQL from my postgres log is:
SELECT "job"."id", "job"."name", "job"."createdAt", "job"."updatedAt", "job"."status", "__status"."id" AS "status___id", "__status"."colour" AS "status___colour", "__status"."status" AS "status___status", "__status"."weight" AS "status___weight", "__status"."createdAt" AS "status___createdAt", "__status"."updatedAt" AS "status___updatedAt" FROM "job" AS "job" LEFT OUTER JOIN "job_status" AS "__status" ON "job"."status" = "__status"."id"

It's missing the Order by.

richardwardza picture richardwardza on 17 Mar 2015

What else would be good is to have
Job.find({}).populate('status').sort('job.weight ASC').exec(function (error, result) {

and have all the jobs sorted by the status.weight column.

richardwardza picture richardwardza on 22 May 2015
👍1

Populate is not working with where condition.

Application.findOne({
      id: appId,
      status_id: {'!': Status.DELETED}
       select :['user_rating','created_at']
    })
      .populate('candidate',
        {
        status_id: {'!': Status.DELETED},
        select: [
          'first_name',
          'last_name',
          'email',
          'phone',
          'mobile',
          'town'
        ]

       .populate('job',{
         status_id: {'!': Status.DELETED},
        select : ['title']
       })
HaiderMalik12 picture HaiderMalik12 on 9 Mar 2017
👍3

I think this is actually broken @particlebanana

Here are my details: https://stackoverflow.com/questions/44135368/waterline-populate-and-where-is-not-working

joncodo picture joncodo on 23 May 2017

@joncodo This doesn't restrict the parent records. It's a left join (or individual query) that always returns the parent, but simply doesn't return child records if they don't match. Is this also not working for you?

RWOverdijk picture RWOverdijk on 23 May 2017

Right. I want a join that is only valid where the criteria matches. I want records where child.org_id == 12 in a query like Parent.populate('child', {org_id: 12})

You are saying that a query like this returns all? How is the where not applied?

joncodo picture joncodo on 23 May 2017

@joncodo I'm saying that it works, but the children will be filtered, not the parents. This requires a custom query. Otherwise you can take a look at wetland ORM (which has a sails hook), where such queries are easier to write.

Maybe this changed with more recent versions of waterline, I don't know.

RWOverdijk picture RWOverdijk on 23 May 2017
Was this page helpful?
0 / 5 - 0 ratings

Related issues

Alirezamohammadi picture Alirezamohammadi  ·  4Comments
svmn picture svmn  ·  4Comments
alxndrsn picture alxndrsn  ·  4Comments
randallmeeker picture randallmeeker  ·  4Comments
Sytten picture Sytten  ·  4Comments