Scope not applied in whereHas | Laravel | Page 1

hidden epoch Mar 21, 2023, 1:02 PM

#

I'm trying to hide users with a certain scope, but that scope is not applied it seems, because the query still returns the users I've wanted to hide. Any idea what I'm doing wrong? The rest of the query works as expected.

// User model
public function scopeNotResigned(Builder $query)
    {
        return $query->where(fn($query) => $query->whereNull('date_of_resignation')->orWhere('date_of_resignation', '>', now()));
    }

// PhoneNumber model
public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

protected function getPhoneNumbers()
    {
        return PhoneNumber::query()
            ->when(
                filled($this->search),
                function (Builder $query): Builder {
                    return $query->where(function (Builder $query): Builder {
                        return $query->where('name', 'like', "%{$this->search}%")
                            ->orWhere('phone', 'like', "%{$this->search}%")
                            ->orWhereHas('user', function (Builder $query): Builder {
                                return $query->notResigned() // HERE
                                    ->where('abbreviation', 'like', "%{$this->search}%")
                                    ->orWhereHas('categories', function (Builder $query): Builder {
                                        return $query->where('name', 'like', "%{$this->search}%");
                                    });
                            });
                    });
                },
            )
            ->when(blank($this->search), fn (Builder $query): Builder => $query->where('id', '<', 0)) // Show 0 results by default.
            ->with(['user', 'user.categories'])
            ->paginate($this->tableRecordsPerPage);
    }

sweet iris Mar 21, 2023, 1:08 PM

#

You're not applying the scope to the eager load, only to the whereHas(). You need to apply it to the with as well, or use withWhereHas

hidden epoch Mar 21, 2023, 1:12 PM

#

I just realized that the PhoneNumber model contains a one-to-one relation with user. Will that correctly hide the PhoneNumber model if the related user 'has resigned'?

sweet iris Mar 21, 2023, 1:13 PM

#

It should, yeah

hidden epoch Mar 21, 2023, 1:14 PM

#

Hmm I can't seem to get it working.

return PhoneNumber::query()
            ->with(['user' => fn ($query) => $query->notResigned(), 'user.categories'])
            ->when(
                filled($this->search),
                function (Builder $query): Builder {
                    return $query->where(function (Builder $query): Builder {
                        return $query->where('name', 'like', "%{$this->search}%")
                            ->orWhere('phone', 'like', "%{$this->search}%")
                            ->orWhereHas('user', function (Builder $query): Builder {
                                return $query
                                    ->notResigned()
                                    ->where('abbreviation', 'like', "%{$this->search}%")
                                    ->orWhereHas('categories', function (Builder $query): Builder {
                                        return $query->where('name', 'like', "%{$this->search}%");
                                    });
                            });
                    });
                },
            )
            ->when(blank($this->search), fn (Builder $query): Builder => $query->where('id', '<', 0)) // Show 0 results by default.
            ->paginate($this->tableRecordsPerPage);

#

I've tried to add ->notResigned() after return $this->belongsTo(User::class); as well before I tried your suggestion, but that didn't work as well

sweet iris Mar 21, 2023, 1:15 PM

#

call ->dump() before ->paginate() on that to see the query being generated

hidden epoch Mar 21, 2023, 1:18 PM

#

select * from `phone_numbers` where (`name` like ? or `phone` like ? or exists (select * from `users` where `phone_numbers`.`user_id` = `users`.`id` and ((`date_of_resignation` is null or `date_of_resignation` > ?) and `abbreviation` like ? or exists (select * from `user_categories` inner join `user_user_category` on `user_categories`.`id` = `user_user_category`.`user_category_id` where `users`.`id` = `user_user_category`.`user_id` and `name` like ? and `user_categories`.`deleted_at` is null)) and `users`.`deleted_at` is null and (`date_of_resignation` is null or `date_of_resignation` > ?)))

sweet iris Mar 21, 2023, 1:20 PM

#

also by "can't seem to get it working" do you mean you're seeing users that you're not expecting to see?

hidden epoch Mar 21, 2023, 1:22 PM

#

Yeah for example, I have a PhoneNumber entry with name: Arjen, phone: 1234, user: 10. This user ID is a user where date_of_resignation = 2023-03-20. This user should not be visible when I retrieve a list of PhoneNumber

sweet iris Mar 21, 2023, 1:23 PM

#

What's your search input for that?

hidden epoch Mar 21, 2023, 1:23 PM

#

"Arj" for example

#

PhoneNumber model can also contain phone numbers not related to users, that's why it's set up like this

sweet iris Mar 21, 2023, 1:24 PM

#

Right so it's matching on the ->where('name', 'like', '%{$this->search}%'). The other stuff is in orWhere so it doesn't matter if it's found there after that

#

I think what you want there is to have a resigned() scope and use whereDoesntHave()

hidden epoch Mar 21, 2023, 1:25 PM

#

Ohh let me try

#

This is harder then I thought 😅

#

I've tried the following but it returns all numbers:

dd(PhoneNumber::query()->whereDoesntHave('user', fn (Builder $query) => $query->resigned())->get());

sweet iris Mar 21, 2023, 1:39 PM

#

dump the query again

hidden epoch Mar 21, 2023, 1:40 PM

#

select * from `phone_numbers` where not exists (select * from `users` where `phone_numbers`.`user_id` = `users`.`id` and `date_of_resignation` <= ? and `users`.`deleted_at` is null and (`date_of_resignation` is null or `date_of_resignation` > ?))

#

Oh

#

I applied a notResigned global scope before going on with this so that was probable screwing with my query 🫠

#

Let me check again

#

return PhoneNumber::query()
            ->with(['user' => fn ($query) => $query->notResigned(), 'user.categories'])
            ->when(
                filled($this->search),
                function (Builder $query): Builder {
                    return $query->where(function (Builder $query): Builder {
                        return $query->where('name', 'like', "%{$this->search}%")
                            ->orWhere('phone', 'like', "%{$this->search}%")
                            ->orWhere(function (Builder $query): Builder {
                                return $query
                                    ->whereDoesntHave('user', fn (Builder $query) => $query->resigned())
                                    ->whereHas('user', function (Builder $query): Builder {
                                        return $query
                                            ->where('abbreviation', 'like', "%{$this->search}%")
                                            ->orWhereHas('categories', function (Builder $query): Builder {
                                                return $query->where('name', 'like', "%{$this->search}%");
                                            });
                                    });
                            });
                    });
                },
            )
            ->when(blank($this->search), fn (Builder $query): Builder => $query->where('id', '<', 0)) // Show 0 results by default.
            ->paginate($this->tableRecordsPerPage);

#

Still not working and I don't know where to fix it 😅

sweet iris Mar 21, 2023, 1:48 PM

#

Your whereDoesntHave is still inside an orWhere so if you match the name field you're still gonna get that

hidden epoch Mar 21, 2023, 1:51 PM

#

Is what I want even possible? Name could be the name of the linked person, but also of a device.

sweet iris Mar 21, 2023, 1:52 PM

#

Well, in the example you gave, assuming Arjen is the name of the user and also of the device, do you want to show it?

hidden epoch Mar 21, 2023, 1:55 PM

#

Lets assume the following three PhoneNumber:

Arjen, 1234, 10 (name, number, user_id [=> user.name = Arjen] as well) -> this user has resigned
Device #1, 5432, null
Mike, 9876, 19 -> this user has NOT resigned

#

If I search for Device, I want result 2 to show. If I search for Mike, I want result 3 to show because that user has not resigned. If I seach for Arjen, I do NOT want to show any results, because that user has resigned

sweet iris Mar 21, 2023, 1:58 PM

#

Alright, so just put the whereDoenstHave outside any orWhere groupings and make sure everything that is an orWhere is grouped alongside it

#

filled($this->search),
                function (Builder $query): Builder {
                    return $query->whereDoesntHave('user', fn($query) => $query->resigned())->
                        $query->where(function (Builder $query): Builder {
                        return $query->where('name', 'like', "%{$this->search}%")...```

#Scope not applied in whereHas