Use whereNot with elemMatch in Laravel with mongo

31 Views Asked by At

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"
        }
    ],
    ...
}
1

There are 1 best solutions below

0
Denys Siebov On BEST ANSWER

Solved that with raw query instead:

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