MongoDB - OneToMany with foreign key in child

Question

I'm using mongodb v.2.6.11 and mongoose.

I have two models with a OneToMany relationship. In my parent, TypeA, (Abbreviated TA) I have no references to the child. In my child, TypeB (abbreviated TB) I have an id reference to the parent.

Example Schema:

var TASchema = {
    name: {type: String, required: "TASchema.name is required", index: {unique: true, dropDups: true}}
}; 

var TBSchema  = {
    start: {type: Number, required: "TBSchema.language is required"},
    stop: {type: Number, required: "TBSchema.stop is required"},
    TA: {type: Schema.Types.ObjectId, ref: 'tbschema'},
}

What I want to do: Select all TB that have the "start" variable within a timespan of the two variables "ts_start" and "ts_stop" (they're timestamps). So something like: start : {$gte: ts_min, $lte : ts_max}.

Example output:

[
    {
       name: "Ta object 1",
       tbs: [{start : 1, stop2}, {start: 2, stop: 3}] 
    },
    {
       name: "Ta object 2",
       tbs: [{start : 1, stop2}, {start: 2, stop: 3}] 
    }
]

I want to keep the structure where the query returns an array of TA where every TA contains an array of TB:s. But I cannot use populate since the TA doesn't have any reference to the child (because there can be too many to keep them as sub documents).

So how is this achievable? Am I thinking wrong or what should I do to output the specified query as in my example output?

select your TB documents by given criteria with populating TA. Reformat the response array of TBs by grouping by TA. You can try to achieve this with $group request, but it's easier to process documents with javascript and request would be faster — vmkcom, Nov 23 '15 at 09:35

chridam · Accepted Answer · 2015-11-23T10:43:08.490

2

To piggyback off @vmkcom, try the aggregation framework to achieve this with $match and $group pipeline steps, use the result returned from the pipeline operation to then populate the TA fields but with a change in schema design for the TA model. This would be necessary to populate the results from the aggregation, so add an array of refs to TB schema in your TA schema:

var TASchema = {
    name: { 
        type: String, 
        required: "TASchema.name is required", 
        index: {unique: true, dropDups: true}
    },
    tbs: [{ type: Schema.Types.ObjectId, ref: 'tbschema' }]
};

The implement something like the following (untested):

var pipeline = [
    { 
        "$match": { 
            "start": { "$gte": ts_min, "$lte": ts_max }
        }
    },
    { 
        "$group": {
            "_id": "$TA",
            "tbs": {
                "$push": {  "start": "$start", "stop": "$stop" }
            }           
        }
    }
];
TBModel.aggregate(pipeline,
    function(err, results) {
        if (err) throw err; 
        var results = result.map(function(doc) { return new TAModel(doc) });        
        TBModel.populate(results, {"path": "TA"}, function(err, docs) {
            if (err) throw err;
            console.log(JSON.stringify(docs, undefined, 4));
        });
    }
);

edited Nov 23 '15 at 10:43

answered Nov 23 '15 at 09:56

chridam

100,957
23
236
235

This is almost perfect. But how do I populate data from TBA (eg name) into the aggregated object? – Victor Axelsson Nov 23 '15 at 10:01
@VictorAxelsson Haven't yet tested it but you could use the populate method on TBModel to populate data from TA, as shown in the answer. More on the [documentation](http://mongoosejs.com/docs/api.html#model_Model.populate). – chridam Nov 23 '15 at 10:08
I don't quite understand what the path thing does and how I include for example the name of TA into the "outer" objected (that have the array of TB:s) – Victor Axelsson Nov 23 '15 at 10:14
The path option in the populate method refers to the path(s) to populate i.e. populated paths are no longer set to their original `_id` , their value is replaced with the mongoose document returned from the database by performing a separate query under the hood before returning the results. If you only want the name of TA returned for the populated documents, you can accomplish this by passing the field name syntax as the `select` property value in the second parameter to the populate method. – chridam Nov 23 '15 at 10:22
I don't know why, but the populate function doesn't seems to be doing anything for me. Nothing happens whatever I put in the path value and the select doesn't do anything either. There is no difference if I use populate on the TA or the TB either. – Victor Axelsson Nov 23 '15 at 10:35
A change in schema would make this possible since the populate method require mongoose documents, I have updated my answer to reflect this. – chridam Nov 23 '15 at 10:47

MongoDB - OneToMany with foreign key in child

1 Answers1