how to join two collection through same column name in mongoose and derive data?

Question

i want to derive price based on same column name that is route_name from two models.how to do it?

1st model : -

var routeSchema = mongoose.Schema({
route_name: String,
from_city: String,
to_city : String,
stoppage_point: String,
arrival_time:String,
distance: Number,
journey_date:Date,
approx_time: Number,
status: {
    type: Boolean,
    default:true,
},

 }); 
 var routeModel = module.exports = mongoose.model('routes', routeSchema,"routes");

model method in which i want to get my data,

module.exports.get_route = function(from_city,to_city,journey_date){
return new Promise(function(resolve,reject){
    routeModel.find({'from_city':from_city,'to_city':to_city,'journey_date':journey_date,status:true},"_id route_name from_city to_city stoppage_point distance approx_time arrival_time journey_date", function(err,result){
        if(err){
            reject(Error(err));
        }
        resolve(result);
    });
})
};

2nd model : -

var ticketPriceSchema = mongoose.Schema({
route_name: String,
from_city: String,
to_city : String,
price: Number,

});
var ticketPriceModel = module.exports = mongoose.model('ticket', ticketPriceSchema,"ticket");

from this 2 modelschema i want to derive price from 2nd model also through route_name which is the same column in both schema.

in result i want to price also,

_id, route_name, from_city, to_city, stoppage_point, distance, approx_time, arrival_time, journey_date, price

Vivek Naik · Answer 1 · 2019-04-30T12:14:24.097

try using lookup operator like below

routeModel.aggregate()
                .match({ route_name: req.query.route_name })
                .lookup({ from: 'tickets', localField: "route_name", foreignField: 'route_name', as: 'ticket' })
.exec()
.then((route) => { console.log(route) })
.catch((err) => { console.log(err) });

localField is the matching field name in routeModel and foreignField is the matching field name in ticketPriceModel(when you do lookup the matching fields need not have same field name). check lookup Official doc says:

To each input document, the $lookup stage adds a new array field whose elements are the matching documents from the “joined” collection.

In simple words lookup returns an array of matching document from tickets collection. lookup will add all matching document from tickets collection in the document as array under field name 'ticket'.

I am just wondering why are you keeping price in a different schema(collection). I am guessing routes info will always accompany price field wherever needed. Why do you want to go an extra step to extract price field from a different schema(collection). I am no expert in mongo but everyone working with nosql will tell you to keep both fields in same collection. There are different ways in which you can model your nosql data based on how related data will be fetched. I see only one extra field 'price' in ticketPriceSchema.

it give me error like ReferenceError: promise is not defined — Arazu Gajera, Apr 30 '19 at 09:49
I cannot help without looking at your code. Consider above query as reference and make the necessary changes. Did you put then and catch clause after the exec() method? — Vivek Naik, Apr 30 '19 at 12:09

how to join two collection through same column name in mongoose and derive data?

1 Answers1