使用 joins & 查询猫鼬中的过滤器 [英] Querying with joins & filters in Mongoose

问题描述

我是 Mongodb 的新手 &在我使用 MEAN 堆栈构建的 Web 应用程序中使用它.我的目标是通过连接两个表并对它们应用过滤条件来查询它们.例如:我有两个表 - Bike-BikeID、注册号、品牌、型号和;Appointment - Appointment Date,Status,Bike(ref Bike object) 我只想显示那些没有预约 status='Booked' 的自行车.我想在Mongoose中完成以下SQL.

I'm new to Mongodb & using it in web application that I'm building using MEAN stack. My goal is to query two tables by joining them and applying a filter condition on them. For eg: I have two tables - Bike-BikeID,Registration No.,Make,Model & Appointment - Appointment Date,Status,Bike(ref Bike object) and I want to show only those bikes who do not have an appointment with status='Booked'. I want to accomplish the following SQL in Mongoose.

Select bike.* from Bike inner join Appointment on Bike.BikeID = Appointment.BikeID and Appointment.Status != 'Booked'

我正在使用以下代码，但没有得到想要的结果.有人可以帮助我进行此查询.

I'm using the following code, but I'm not getting the desired results. Can someone help me with this query.

app.get('/api/getbikeappo*',function(req,res){
        var msg="";
        //.find({cust:req.query._id})
        //.populate('cust','email')
        ubBike.aggregate([
                {
                    $match:
                    {
                        cust : req.query._id
                    }
                },
                {
                    $lookup:
                    {
                        from: "appos",
                        localField: "_id",
                        foreignField: "bike",
                        as : "appointments"
                    }
                },
                {
                    $match:
                    {
                        "appointments" : {$eq : []}
                    }
                }
            ])
            .exec(function(err,bikes){
                 res.send(bikes);
                if(err) throw err;
            });
    }); 

<小时>

bikes - collection
{
    "_id": {
        "$oid": "57fb600fdd9070681de19c18"
    },
    "brand": "Splendor",
    "model": "Splendor",
    "year": "2002",
    "kms": 0,
    "regno": "TN02M8937",
    "cust": {
        "$oid": "57f8c44466cab97c1355a09a"
    },
    "__v": 0
}
{
    "_id": {
        "$oid": "57fb6025dd9070681de19c19"
    },
    "brand": "Activa",
    "model": "Activa",
    "year": "2016",
    "kms": 0,
    "regno": "TN14M3844",
    "cust": {
        "$oid": "57f8c44466cab97c1355a09a"
    },
    "__v": 0
}

appointment collection
----------------------
{
    "_id": {
        "$oid": "57fb6040dd9070681de19c1a"
    },
    "appoidt": {
        "$date": "2016-10-15T18:30:00.000Z"
    },
    "reqdt": {
        "$date": "2016-10-10T09:32:48.694Z"
    },
    "status": "Booked",
    "bike": {
        "$oid": "57fb600fdd9070681de19c18"
    },
    "cust": {
        "$oid": "57f8c44466cab97c1355a09a"
    },
    "__v": 0
}
-----------------
Expected output is 

{
    "_id": {
        "$oid": "57fb6025dd9070681de19c19"
    },
    "brand": "Activa",
    "model": "Activa",
    "year": "2016",
    "kms": 0,
    "regno": "TN14M3844",
    "cust": {
        "$oid": "57f8c44466cab97c1355a09a"
    },
    "__v": 0
}

推荐答案

您就快到了，您只需要合适的 $match 查询如下:

You were almost there, you just needed the right $match query which follows:

ubBike.aggregate([
    { "$match": { "cust": req.query._id } },
    {
        "$lookup": {
            "from": "appos",
            "localField": "_id",
            "foreignField": "bike",
            "as": "appointments"
        }
    },
    { "$match": { "appointments.status": { "$ne": "Booked" } } }
]).exec(function(err, bikes){
    if(err) throw err;
    res.send(bikes);    
});

这篇关于使用 joins &amp; 查询猫鼬中的过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！