get nested relations data $lookup

Question

i have collection page and page has many widgets:

page collection :

_id
name
slug,
widget_codes

widget collection :

_id
name
code
type
product_ids
category_ids

product collection:

_id
name
type

category collection:

_id
name
type

for retrieve data i use like this:

public function getData($page) {
    return $page->raw(function ($collection) use ($page) {
        return $collection->aggregate([
            [
                '$lookup' => [
                    'from'         => 'widgets',
                    'localField'   => 'widget_codes',
                    'foreignField' => 'code',
                    'as'           => 'widgets',
                ]
            ],
            [
                '$match' => ['slug' => $page->slug]
            ]
        ]);
    });
}

it works perfect and get widget data now i want to get product and category data with widget using that widget has product_ids and category_ids what query can i use.

Does this answer your question? [Multiple join conditions using the $lookup operator](https://stackoverflow.com/questions/37086387/multiple-join-conditions-using-the-lookup-operator) — ngShravil.py, May 23 '20 at 13:57

score 0 · Answer 1 · answered May 23 '20 at 14:01

You can use pipeline in $lookup for subquery the multiple tables

 public function getData($page) {
    return $page->raw(function ($collection) use ($page) {
      return $collection->aggregate([
          [
            '$match' => ['slug' => $page->slug]
          ],
          [
              '$lookup' => [
                  'from'         => 'widgets',
                  "let"=> [ "widget_codes"=> "$widget_codes" ],
                  "pipeline"=> [
                    [ "$match"=> [ "$expr"=> [ "$in"=> ["$_id", "$$widget_codes"] ]]],
                    [
                        '$lookup' => [
                            'from'         => 'category',
                            'localField'   => 'category_ids',
                            'foreignField' => '_id',
                            'as'           => 'categories',
                        ]
                    ],
                    [
                        '$lookup' => [
                            'from'         => 'product',
                            'localField'   => 'product_ids',
                            'foreignField' => '_id',
                            'as'           => 'products',
                        ]
                    ]
                  ],
                  'as'           => 'widgets',
              ]
          ]
      ]);
    });
}

Thanks for response,but it's not work it return product and category blank. — Denisha Asodariya, May 24 '20 at 15:35
It should work, I checked the query on my system with mongo shell on sample data, Please once check the spelling matches with you collection names and fields and the PHP syntax is correct. — Puneet Singh, May 26 '20 at 07:46

get nested relations data $lookup

1 Answers1