Laravel / Mongodb - How to build query where count array of objects equal to total numher of competitor

I have this array (matching_competitors) of objects in the mongodb server:

"matching_competitors": [
    {
      "id_competitor": "21",
      "domain": "",
      "id_product_competitor": 224731,
      "url_product_competior": "https://www.librarie.net/p/224731/nia=1693"
    },
    {
      "id_competitor": "21",
      "domain": "",
      "id_product_competitor": 282868,
      "url_product_competior": "https://www.librarie.net/p/282868/nia=1693"
    }
  ]

and I have 3 competitors in competitors table (mysql)

Now, I need to build a query where -> size of matchig_competitors (mongodb) should equal to total competitor (mysql)

I can not get any idea how to build this query.

My current query:

$get_product = DB::connection('mongodb')->collection( 'products_' . $get_project_id->id_project )->Where( 'matching_competitors', $total_competitor )->paginate(6);

Niush

3 years ago

Level 50

You might need some complex mongo aggregate functions. Like $project, $size etc. with DB::raw.

If it is possible, I would probably simply add matching_competitors_count to each mongodb document, during adding/updating. It will make querying much much easier and less complex.

Shibbir

3 years ago

Level 3

@Niush do you mean this?

	        $cursor = DB::connection('mongodb')->collection('products_' . $id_project)->raw(function ($collection) {
        return $collection->aggregate([
            // ['$match' => ['id' => $user_product_id]],
            [
                '$project' => [
                    'count' => [
                        '$size' => '$matching_competitors',
                    ],
                ],
            ],
        ]);
    });
    $result = $cursor->toArray();
    $count = $result[0]['count'];

Its sometimes work and sometime not. Its showing me :

"Executor error during getMore :: caused by :: The argument to $size must be an array, but was of type: missing",

Niush

3 years ago

Level 50

@Shibbir That error occurs when there is no matching_competitors key in a document. That means, it is not an empty array, but is completely missing.

Something like this can be done. I don't know if it is the best way or not.

$found_ids = collect(
  DB::connection('mongodb')
    ->collection('products_' . $id_project)
    ->raw(function ($collection) {
      return $collection->aggregate([
        [
          '$project' => [
            'count' => [
              '$size' => [
                '$ifNull' => ['$matching_competitors', []], // If null, set as []
              ],
            ],
          ],
        ],
        [
          '$match' => [
            'count' => [
              '$eq' => 3, // <- your number
            ],
          ],
        ],
      ]);
    })
    ->toArray()
)->map(fn($d) => (string) $d['_id']);

$records = DB::connection('mongodb')
    ->collection('products_' . $id_project)
    ->whereIn('_id', $found_ids)
    ->get();

With this way, 2 queries are needed. And, you need to manually handle paginations with skip/limit etc.

Shibbir

3 years ago

Level 3

@Niush Thanks for your reply. Can you tell why we need this '$eq' => 3, // <- your number, why we need 3 ?

Niush

3 years ago

Level 50

@Shibbir That's in your question.

size of matching_competitors (mongodb) should equal to total competitor (mysql)

The 3 is example of total competitors. That is, the $total_competitor variable from your code. The aggregate function is matching if the count of $matching_competitors equals 3 in my example.

Please or to participate in this conversation.