Laravel whereHas() / orWherehas() with where('id', '!=', $id) closure doesn&#039-CodePudding

I want to query all the entries of an one-to-many relation not already associated with the given resource.

In this case I have the Basket model containing a Country relation

    public function country()
    {
        return $this->belongsTo(Country::class);
    }

And vice-versa

    public function baskets()
    {
        return $this->hasMany(Basket::class);
    }

The table baskets has the required columm country_id

To query all Countries for a Basket with id 1, except for the one Country already attached to it, I would expect to use the following query:

    $countries = Country::whereDoesntHave('baskets')
        ->orWherehas('baskets', function($query) {
            $query->where('id', '!=', 1);
        })->get()

But this is giving me all Countries in the table as results including the Country that was attached to the Basket, which it shouldn't.

The problem seems to be that the '!=' in the closure seems to be not working.

If I would just query with only whereHas() I can get the result if I want the Country with a relation with Basket with id 1, but not all Countries with relations with Baskets BUT id 1, as it wouldn't exclude it and basically ignore the where() in the closure;

 $countries = Country::wherehas('baskets', function($query) {
            $query->where('id', 1); // This works, but not the opposite by adding the `!=` operator
        })->get()

What am doing wrong? I've got this code working perfectly fine for many-to-many relations (using something like basket_id instead for the where() statement).

CodePudding user response：

What you really want is "get all Countries where there doesn't exist any Basket with both my country_id and id=1".

So, the solution is:

$countries = Country::whereDoesntHave('baskets', function($query) {
    $query->where('baskets.id', '=', 1);
})->get();

which, if you test with the ->toSql() (instead of ->get()) gives you

select * from `countries`
where not exists
  (select * from `baskets` where `countries`.`id` = `baskets`.`country_id` and `id` = 1)

Why didn't your original query work?

$countries = Country::whereDoesntHave('baskets')
        ->orWherehas('baskets', function($query) {
            $query->where('id', '!=', 1);
        })->get();

would return something like

select * from `countries`
where not exists
  (select * from `baskets`
   where `countries`.`id` = `baskets`.`country_id`)
or exists
  (select * from `baskets`
   where `countries`.`id` = `baskets`.`country_id` and `baskets`.`id` != 1)

So the orWhereHas gets translated to "or where there exists at least one Basket that has my country_id but whose id is not 1". This is not what you wanted.

For example: if a Country has, say, two Baskets, one with id=1 and the other with id=100, the latter Basket satisfies that second "exists" clause of your query, and that Country would therefore be included in your results. In other words, there does exist a Basket belonging to that country that doesn't have id=1.

Bonus solution: (