Using where in jenssegers/laravel-mongodb

Question

I am using jenssegers/laravel-mongodb in laravel :

The structure of my data in the mongodb is as follows. Suppose there are 100 rows like this

click show image

I need a query to show me all the data whose finance.selling_type status is 1

My attempt to write code

finances::where('finance.selling_types.$$status', 1)->get();

or

finances::where('finance.selling_types.*.status', 1)->get();

You can find the documents without the use of * or $$ inside where condition DB::collection('finances')->where('finance.sellingTypes.status',1)->get() — hhharsha36
– hhharsha36, Commented Jul 14, 2020 at 6:20
This answer is wrong. Note that each head of object "selling_types" is an object in itself — Hossein
– Hossein, Commented Jul 14, 2020 at 6:23

Oluwafemi Sule · Accepted Answer · 2020-07-24 04:59:21Z

Use an aggregation pipeline.

Convert the objects embedded in selling_types to an array

 $convertSellingTypesToArray = [
    '$project' => [
      'finance.selling_types' => [
        '$objectToArray' => '$finance.selling_types'
      ]
    ]
  ];

Then unwind the array to individual documents

$sellingTypesAsDocuments =   [
    '$unwind' => '$finance.selling_types'
];

Filter where status is 1.

$selectOnlyActiveStatus = [
    '$match' => [
      'finance.selling_types.v.status' => [
        '$eq' => 1
      ]
    ]
  ];

Convert selling_types back as an object.

$convertSellingTypesToObject = [
    '$project' => [
      'finance.selling_types' => [
        '$arrayToObject' => [
          [
            '$finance.selling_types'
          ],
        ]
      ]
    ]
  ];

finances::raw()->aggregate([
  $convertSellingTypesToArray,
  $sellingTypesAsDocuments,
  $selectOnlyActiveStatus,
  $convertSellingTypesToObject,
])

Note that you'll need to support pagination on the query

Hossein · Accepted Answer · 2020-08-10 08:59:11Z

thanks to Oluwafemi Sule, I found the solution :

\DB::connection('mongodb')
            ->collection('finances')
            ->raw(function($collection) {
                return $collection->aggregate(
                    [
                        [
                            '$project' => [
                                'finance'=> [
                                    'selling_types'=> [
                                        '$objectToArray'=>  '$finance.selling_types'
                                    ]
                                ],
                              
                            ]
                        ],
                        [
                            '$unwind' => '$finance.selling_types'
                        ],
                        [
                            '$match'=> [
                                '$and' => [
                                    [
                                        'finance.selling_types.v.status' => [
                                            '$eq' => 1
                                        ],
                                    ],
                                ]
                            ]
                        ],

                    ]
                );
            });

Collectives™ on Stack Overflow

Using where in jenssegers/laravel-mongodb

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related