Mongoid query embedded document and return parent

Question

I have this document, each is a tool:

{ 
    "_id" : ObjectId("54da43aea96ddcc40915a457"), 
    "checked_in" : false, 
    "barcode" : "PXJ-234234", 
    "calibrations" : [
        {
            "_id" : ObjectId("54da46ec546173129d810100"), 
            "cal_date" : null, 
            "cal_date_due" : ISODate("2014-08-06T00:00:00.000+0000"), 
            "time_in" : ISODate("2015-02-10T17:46:20.250+0000"), 
            "time_out" : ISODate("2015-02-10T17:46:20.250+0000"), 
            "updated_at" : ISODate("2015-02-10T17:59:08.796+0000"), 
            "created_at" : ISODate("2015-02-10T17:59:08.796+0000")
        }, 
        {
            "_id" : ObjectId("5509e815686d610b70010000"), 
            "cal_date_due" : ISODate("2015-03-18T21:03:17.959+0000"), 
            "time_in" : ISODate("2015-03-18T21:03:17.959+0000"), 
            "time_out" : ISODate("2015-03-18T21:03:17.959+0000"), 
            "cal_date" : ISODate("2015-03-18T21:03:17.959+0000"), 
            "updated_at" : ISODate("2015-03-18T21:03:17.961+0000"), 
            "created_at" : ISODate("2015-03-18T21:03:17.961+0000")
        }, 
        {
            "_id" : ObjectId("5509e837686d610b70020000"), 
            "cal_date_due" : ISODate("2015-03-18T21:03:51.189+0000"), 
            "time_in" : ISODate("2015-03-18T21:03:51.189+0000"), 
            "time_out" : ISODate("2015-03-18T21:03:51.189+0000"), 
            "cal_date" : ISODate("2015-03-18T21:03:51.189+0000"), 
            "updated_at" : ISODate("2015-03-18T21:03:51.191+0000"), 
            "created_at" : ISODate("2015-03-18T21:03:51.191+0000")
        }
    ], 
    "group" : "Engine", 
    "location" : "Here or there", 
    "model" : "ZX101C", 
    "serial" : NumberInt(15449), 
    "tool" : "octane analyzer", 
    "updated_at" : ISODate("2015-09-30T20:43:55.652+0000"), 
    "description" : "Description...", 
}

Tools are calibrated periodically. What I want to do is grab tools that are due this month.

Currently, my query is this:

scope :upcoming, -> { where(:at_ats => false).where('calibrations.0.cal_date_due' => {'$gte' => Time.now-1.day, '$lte' => Time.now+30.days}).order_by(:'calibrations.cal_date_due'.asc) }

However, this query gets the tool by the first calibration object and it needs to be the last. I've tried a myriad of things, but I'm stuck here.

How can I make sure I'm querying the most recent calibration document, not the first (which would be the oldest and therefore not relevant)?

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

You should look into aggregation framework and $unwind operator.

This link may be of help. This link may be helpful. It contains an example of use of 'aggregation framework' for get the last element of the array, that is, the most recent in your case.

edited May 23 '17 at 12:15

Community

1
1

answered Nov 11 '15 at 09:06

hecnabae

407
4
21

Yes I agree. Can you provide a specific solution? I don't feel this answer directly addresses my question. – Kevin Brown Nov 11 '15 at 12:21
Ok, this is the aggregated query to get last date: `db.getCollection('tools').aggregate([ {$unwind: "$calibrations"}, {$group: {_id: "$_id", last_date:{$last:"$calibrations.cal_date_due"}}} ])` – hecnabae Nov 11 '15 at 12:32
Looks good. Now all I need to do is filter documents within 30 days from now. Also, how do I make the results an object in rails? I'm trying to make this a scope, but I don't think mongoid will scope on an aggregation. – Kevin Brown Nov 11 '15 at 13:05
Sorry I don't know ruby on rails. I can only help you with MongoDB. – hecnabae Nov 11 '15 at 14:26
This is solid. Can you help me with one more thing? I want to add to the aggregation, a match on "$group", so I can filter by a specific tool group... – Kevin Brown Nov 11 '15 at 22:54
Of course, to filter by a specific tool group, you should add a $match stage before $unwind stage: `db.getCollection('tools').aggregate([ {$match:{"group":"Engine"}}, {$unwind: "$calibrations"}, {$group: {_id: "$_id", group:{$first:'$group'}, last_date:{$last:"$calibrations.cal_date_due"}} } ])` Note that I added a field `group:{$first:'$group'}` in order to show the tool group. – hecnabae Nov 12 '15 at 07:50
Yup, good job. My syntax is all wrong, but this is a solid answer. Thanks for your help! – Kevin Brown Nov 12 '15 at 14:08

Mongoid query embedded document and return parent

1 Answers1