Search code examples
laravel-query-builderlaravel-mongodb

Use whereNot with elemMatch in Laravel with mongo


I have a query I use to filter results according to user profile. I have a list of countries applied to model, and I show only those where user.country matches with this array. This part works fine to me:

... ->where(function ($q) {
       $q->whereNull('country')
         ->orWhere(
               'country',
               'elemMatch',
               ['country' => Auth::user()->country]
           );
          }
        )

Now I want to add 'black-listing' functionality and do not show some content to users from the given countries list, but it doesn't works.

... ->where(function ($q) {
       $q->whereNull('country_exclude')
         ->orWhereNot(
               'country_exclude',
               'elemMatch',
               ['country' => Auth::user()->country]
           );
          }
        )

How can I use elemMatch with not operator in Laravel/Mongo query builder? I have one record I expect to be in result with country_exclude:null but it's exluded from result once I add orWhereNot.

This is demography object I'm queuring over:

{
    "_id" : ObjectId("65e5a667792571b4d316fb75"),
    "country" : [ 
        {
            "country" : "A"
        }, 
        {
            "country" : "B"
        }
    ],
    "country_exclude" :  [ 
        {
            "country" : "C"
        }, 
        {
            "country" : "D"
        }
    ],
    ...
}

Solution

  • Solved that with raw query instead:

    ...
    ->where(
       function ($q) {
           $q->whereNull('country_exclude')
               ->orWhereRaw(
                   [
                       'country_exclude' => [
                          '$not' => [
                              '$elemMatch' => [
                                  'country' => Auth::user()->country
                               ]
                           ]
                        ]
                    ]
                );
       }
    )...