Filtering relationships

[easyaspieee]

Hi, after trying a lot of solutions I'm comming here for help.

I have a model setup:

var AccountSchema = new Schema({
  name: String,
  heartbeats: [ {
    type: Schema.Types.ObjectId, ref: 'Heartbeat',
  }, ],
  sessions: {
    type: [ {
      type: Schema.Types.ObjectId, ref: 'Session',
    }, ],
  },
})

var SessionSchema = new Schema({
  startTime: Date,
  endTime: Date,
  account: { type: Schema.Types.ObjectId, ref: 'Account', },
})

and I define the resource as:

Session.odataResource = new Resource({
  rel: '/odata/sessions',
  model: Session,
  count: true,
  $expand: 'account',
})

And I'm trying to make a query for sessions, that have an account with a specific name. Is that possible with the current version? The expand works correctly, there's no problem with that. My attempts are things like "/odata/sessions?$filter=account.name eq 'something'", but they either end up getting errors due to incorrect syntax, or just return an empty list.

Thanks for you answer!

[adamspe]

Sorry, but unfortunately no, this is a limitation of MongoDB that normal querying doesn't support a concept similar to a SQL JOIN so you can't query "through" references like this. The $expand functionality really just uses Mongoose's populate functionality (and $expand needs a bit of enhancement if I can ever find any free time).

You can only build filters based on the schema of the collection in question so obj.attr eq 'foo' will work fine so long as that's part of the schema of the collection in question (where obj refers to a nested object or array of objects, the latter matching any object in the array). The module supports basic instance relationships like /session/<id>/account but this of course also doesn't suit your use case.

MongoDB only supports the concept of JOIN through the aggregation pipeline's $lookup operator so off the top of my head it feels like that might be the best way to fill this gap in your case.

E.g. (WARNING: this is off the top of my head so no promises it will run without some tweaks) Going raw ES5'ish JS though there are now Typescript definitions (require generics hoops be jumped through when defining Mongoose models, extra interfaces, etc.).

Session.odataResource = new Resource({
...
}).staticLink('byAccountName',function(req,res) {
  // making an assumption about how to get the "Account" model
  // but just Account is probably just as appropriate since it looks as if
  // you're putting the resource instance ON the model as a property
  Account.odataResource.getModel().aggregate([
  {
    $match: {
      name: req.query.name // no error checking here....
    }
  }, {
    $lookup: {
      from: 'Session', 
      localField: 'sessions', 
      foreignField: '_id', 
      as: 'sessions'
    }
  }, {
    $unwind: {
      path: '$sessions'
    }
  }, {
    $replaceRoot: {
      newRoot: '$sessions'
    }
  }]).then(function(sessions){
    // neither route below will honor your "$expand", the results would be raw session objects
    // send the raw objects as is
    // res.send(sessions);
    // return a list response similar to other odata-resource responses with _links
    Session.odataResource.listResponse(req,res,sessions);
    // alternatively the pipeline could maybe just return the session _ids and you could run
    // another query to get the expanded results without duplicating logic but...
  })
  .catch(function(err) {
    Resource.sendError(res,500,'Aggregation error',err);
  });
});

And then use like /odata/sessions/byAccountName?name=Account A

There might be other solutions but this is an idea that comes to mind. Hopefully it helps.

p.s. I mention it in the project notes/documentation but the module is only "odata'ish" in nature (I probably should have given it another name or rename it since it will never entirely behave exactly like the specification and doesn't strive to) I just wanted to lift/reuse some of the well defined parameters as opposed to inventing them from scratch and not doing as thorough a job.

[easyaspieee]

Well, I didn't initially understand what all the static links are needed for, but now I get it. Your solution works very well, thank you very much :)