mongodb多表联查

产品表order,一单一产品

{_id:"",pid:"",pay_status:1}

产品表

{_id:"",name:"产品A"}

现希望得到

{_id:"订单编号",name:"产品名称"}

尝试使用$lookup进行联查

db.order.aggregate([
{$lookup:{from:"product",//连接的表
localField:"pid",//左表关联字段:order.pid
foreignField:"_id",//关联表字段:product._id
as:"products"}}
]);

但查询结果为空，因为order.pid是string,product._id是ObjectId，类型不同，无法进行匹配

db.order.aggregate([{
    $project:{pid:{$toObjectId:"$pid"}}},
    {$lookup:{from:"product",localField:"pid",foreignField:"_id",as:"products"}}
    ]);
     /*使用$project配合$toObjectId将order.pid转换成ObjectId，但使用$project后，等于一个新的文档，除_id外，其他会被过滤掉，因此按需添加:pay_status:1=> $project:{pid:{$toObjectId:"$pid"},pay_status:1}}*/

添加筛选条件

db.order.aggregate([{
    $project:{pid:{$toObjectId:"$pid"},batch:1}},
    {$lookup:{from:"product",localField:"pid",foreignField:"_id",as:"products"}},
    {$match:{"products._id":ObjectId("5c911bb322717e34115e87b7")}}
    ]);

YII2代码示例

      $pipelines  = [
           ['$project'=>['pid'=>['$toObjectId'=>'$pid']]],
           ['$lookup'=>['from'=>'product','localField'=>'pid','foreignField'=>'_id','as'=>'products']],
           ['$match'=>['products._id'=>new \MongoDB\BSON\ObjectId("5c911bb322717e34115e87b7")]]
       ];
       $datas = \com\models\Order::find()->getCollection()->aggregate($pipelines);