ehab ehab - 21 days ago 5
PHP Question

how to make mongoDB query with relation between 3 collections

I have these 3 mongoDB collections :

client_results

[
'resultId':1,
'clientId':'client00',
],
[
'resultId':2,
'clientId':'client00',
],


results

[
'id':1,
'siteID':5
],
[
'id':2,
'siteID':6
]


sites

[
'id':5,
'language':'ar'
],
[
'id':6,
'language':'en'
]


how I can get client_results where results.site.language = 'en' ?
in MYSQL query will like this :

select cr.id, cr.clientId from client_results cr
left join results r
on r.id = cr.resultId
left join site s
on s.id = r.siteID
where s.language = 'en';


but in mongoDB how I can make this query ?

thanks

Answer

In mongodb it is little hectic to do joins,as mongodb was not meant to do joins,the main idea behind mongodb was to put all you need in one document,but there are some limitation,we cant put whole other document in another,anyways here is your answer,$look is like left outer join,But problem in this solution is there i have unwind the first table to join another,in this case if there is no first join there would be no second join as well, You can use a $project stage and put a _id variable in it,play with it you will get the desired result

db.client_results.aggregate([
                            {"$lookup" : 
                             {
                              from : results,
                              localField : resultId,
                              foreignField: id,
                              as : "resultTable"
                            }
                          },
                      {"$unwind" : "$resultTable"},
                      {"$lookup" : 
                         {
                          from : sites,
                          localField : "resultTable.siteID",
                          foreignField: id,
                          as : "siteTable"
                         }
                       },
                      {"$unwind" : "$siteTable"},
                      {$match : {"siteTable.language" : "en"}} // <-- only en language
                        ])