Strange query results with `when` and `where`

Hello, I have a strange issue a query I'm using to show a list of "members" in my application. It's a sports association site, with clubs and members belonging to clubs (via club_id field).

Each club has a page where I list its members. I'm using Livewire component for that, as I have search box to filter by member's name or their membership number.

The issue I have is when I start using the search query the results show all members, not just folks assigned to the club. If search box is not used, the list shows correct - just members with club_id = currently viewed club.

Here's my query:

$members = Member::where('club_id', $this->club_id)
            ->when($this->searchQuery != '', function ($query) {
            $query->where('first_name', 'like', '%' . $this->searchQuery . '%')
                ->orWhere('last_name', 'like', '%' . $this->searchQuery . '%')
                ->orWhere('number',  $this->searchQuery);
        })
            ->orderBy('active', 'desc')
            ->paginate(25);

I though by having the where first I'm limiting the rest of the collection but it looks like the search query check across all members, not just those related to the club.

LaryAI

2 years ago

Level 58

The issue with your query is that the when method is not properly scoped. The when method applies the given callback only if the first argument is true. In your case, the first argument is $this->searchQuery != '', which means the callback will be executed regardless of the club_id.

To fix this issue, you need to include the club_id condition inside the callback function of the when method. Here's the updated query:

$members = Member::when($this->searchQuery != '', function ($query) {
        $query->where('club_id', $this->club_id)
            ->where(function ($query) {
                $query->where('first_name', 'like', '%' . $this->searchQuery . '%')
                    ->orWhere('last_name', 'like', '%' . $this->searchQuery . '%')
                    ->orWhere('number', $this->searchQuery);
            });
    })
    ->orderBy('active', 'desc')
    ->paginate(25);

By moving the where('club_id', $this->club_id) inside the callback function, the search conditions will only be applied if the search query is not empty. This ensures that the search is limited to members belonging to the currently viewed club.

Let me know if you have any further questions!

redlik

2 years ago

Level 2

Almost perfect Lary :-)

I had to keep the where condition outside of the search check as well, otherwise I wouldn't get the list of members when search wasn't used.

tangtang

2 years ago

Level 6

@redlik

may you try this

$members = Member::where('club_id', $this->club_id)
    ->when($this->searchQuery != '', function ($query) {
        $query->where(function ($subQuery) {
            $subQuery->where('first_name', 'like', '%' . $this->searchQuery . '%')
                ->orWhere('last_name', 'like', '%' . $this->searchQuery . '%')
                ->orWhere('number', $this->searchQuery);
        });
    })
    ->orderBy('active', 'desc')
    ->paginate(25);

redlik

2 years ago

Level 2

This is what worked for me in the end:

$members = Member::where('club_id', $this->club_id)
        ->when($this->searchQuery != '', function ($query) {
            $query->where('club_id', $this->club_id)
                ->where(function ($query) {
                    $query->where('first_name', 'like', '%' . $this->searchQuery . '%')
                        ->orWhere('last_name', 'like', '%' . $this->searchQuery . '%')
                        ->orWhere('number', $this->searchQuery);
                });
        })
            ->orderBy('active', 'desc')
            ->paginate(25);

When search is empty, just show members where club_id, if search not empty - use searchQuery but still keep the scope within just club members

Please or to participate in this conversation.