mongodb - $lookup 与 php mongodb
问题描述
我在 mongo db 中有一个学生表,例如
{
"_id": ObjectId("5baa85f61d7859401000002a"),
"Name": "Bella Dave",
"RollNo": 12,
"Class": "Ist",
"TransportDetails": [
{
"RouteId": ObjectId("5baa93a21d7859401000002b"),
"StopId": "abc123",
"Status": "Inactive"
},
{
"RouteId": ObjectId("5baa818d1d78594010000029"),
"StopId": "abc456",
"Status": "Active"
}
]
}
我有类似的路由表
{
"Name": "New york City",
"StopDetails": [
{
"StopId": "abc123",
"Name": "Block no 1"
},
{
"StopId": "abc567",
"Name": "Block no 2"
}
]
我在下面写了聚合查询,例如
$cursor = $this->db->TblStudent->aggregate([
[
'$addFields' => [
'ActiveRouteId' => [
'$map' => [
'input' => '$TransportDetails',
'as' => 'item',
'in' => [
'$cond' => [
['$eq' => ['$$item.Status', "Active"]],
'$$item.RouteId',
false
]
]
]
]
]
],
[
'$addFields' => [
'ActiveStopId' => [
'$map' => [
'input' => '$TransportDetails',
'as' => 'item',
'in' => [
'$cond' => [
['$eq' => ['$$item.Status', "Active"]],
'$$item.StopId',
false
]
]
]
]
]
],
array(
'$lookup' => array(
'from' => 'TblRoute',
'localField' => 'ActiveRouteId',
'foreignField' => '_id',
'as' => 'RouteDetails'
)
),
array(
'$lookup' => array(
'from' => 'TblRoute',
'localField' => 'ActiveStopId',
'foreignField' => 'StopDetails.StopId',
'as' => 'StopDetails'
)
),
])->toArray();
return $cursor;
基本上,我必须获取活动的路线和停止信息以及学生数据。因此,我使用 $addFields 和 $map 运算符成功获取了 ActiveRouteId 和 ActiveStopId。基于 ActiveRouteId,我正在执行 $lookup 以获取活动路线信息。我在“RouteDetails”嵌入式文档中成功获得了它。现在我的问题是在线
array(
'$lookup' => array(
'from' => 'TblRoute',
'localField' => 'ActiveStopId',
'foreignField' => 'StopDetails.StopId',
'as' => 'StopDetails'
)
),
此查找未获取任何内容。请帮忙!!!
是否可以一起查找路线和站点信息。我的意思是在路由表中还有许多其他嵌入式文档,如果可以获取所需的嵌入式文档,例如
RouteDetails: [
"Name": "New york City",
"StopDetails": [
{
"StopId": "abc123",
"Name": "Block no 1"
}
]
解决方案
您可以尝试以下聚合
TblStudent.aggregate([
[ "$addFields"=> [
"TransportDetails"=> [
"$cond"=> [
"if"=> [
"$ne"=> [ [ "$type"=> "$TransportDetails" ], "array" ]
],
"then"=> [],
"else"=> "$TransportDetails"
]
]
]],
[ "$addFields"=> [
"ActiveRouteId"=> [
"$filter"=> [
"input"=> "$TransportDetails",
"as"=> "item",
"cond"=> [ "$eq"=> ["$$item.Status", "Active"] ]
]
]
]],
[ "$lookup"=> [
"from"=> "TblRoute",
"let"=> [ "activeRouteId"=> "$ActiveRouteId.RouteId" ],
"pipeline"=> [
[ "$match"=> [ "$expr"=> [ "$in"=> ["$_id", "$$activeRouteId"] ]]]
],
"as"=> "RouteDetails"
]],
[ "$lookup"=> [
"from"=> "TblRoute",
"let"=> [ "activeStopId"=> "$ActiveRouteId.StopId" ],
"pipeline"=> [
[ "$unwind"=> "$StopDetails" ],
[ "$match"=> [ "$expr"=> [ "$in"=> ["$StopDetails.StopId", "$$activeStopId"] ]]],
],
"as"=> "StopDetails"
]]
])
类似于 javascript
TblStudent.aggregate([
{ "$addFields": {
"TransportDetails": {
"$cond": {
"if": {
"$ne": [ { "$type": "$TransportDetails" }, "array" ]
},
"then": [],
"else": "$TransportDetails"
}
}
}},
{ "$addFields": {
"ActiveRouteId": {
"$filter": {
"input": "$TransportDetails",
"as": "item",
"cond": { "$eq": ["$$item.Status", "Active"] }
}
}
}},
{ "$lookup": {
"from": "TblRoute",
"let": { "activeRouteId": "$ActiveRouteId.RouteId" },
"pipeline": [
{ "$match": { "$expr": { "$in": ["$_id", "$$activeRouteId"] }}}
],
"as": "RouteDetails"
}},
{ "$lookup": {
"from": "TblRoute",
"let": { "activeStopId": "$ActiveRouteId.StopId" },
"pipeline": [
{ "$unwind": "$StopDetails" },
{ "$match": { "$expr": { "$in": ["$StopDetails.StopId", "$$activeStopId"] }}},
],
"as": "StopDetails"
}}
])
给我以下输出
/* 1 */
{
"_id" : ObjectId("5baa85f61d7859401000002a"),
"Name" : "Bella Dave",
"RollNo" : 12,
"Class" : "Ist",
"TransportDetails" : [
{
"RouteId" : ObjectId("5baa93a21d7859401000002b"),
"StopId" : "abc123",
"Status" : "Inactive"
},
{
"RouteId" : ObjectId("5baa818d1d78594010000029"),
"StopId" : "abc456",
"Status" : "Active"
}
],
"ActiveRouteId" : [
{
"RouteId" : ObjectId("5baa818d1d78594010000029"),
"StopId" : "abc456",
"Status" : "Active"
}
],
"routeDetails" : [
{
"_id" : ObjectId("5baa818d1d78594010000029"),
"Name" : "New york City",
"StopDetails" : [
{
"StopId" : "abc123",
"Name" : "Block no 1"
},
{
"StopId" : "abc567",
"Name" : "Block no 2"
}
]
}
],
"StopDetails" : [
{
"_id" : ObjectId("5baa93a21d7859401000002b"),
"Name" : "New york City",
"StopDetails" : {
"StopId" : "abc456",
"Name" : "Block no 2"
}
}
]
}