How to search clients of the authenticated user?-CodePudding

I have a categorization in DB that a user has some clients, they are connected by user_id. At me, tables are shown in the dashboard that comes only clients that are registered by the currently logged-in user, but when I search by name or surname or mail at the table I see clients of other users. This is the code?

public function render()
    {
        $user = Auth::user()->id;
        if($this->searchTerm == ''){
            return view('livewire.dashboard.user-management', [
                'clients' => Client::where('user_id', '=', $user)->orderBy($this->sortField, $this->sortAsc)->paginate(8)
            ]);
        }
        else{
            return view('livewire.dashboard.user-management', [
                'clients' => Client::where('user_id', '=', $user)
                ->where('name', 'LIKE', '%'.$this->searchTerm.'%')
                ->orWhere('surname', 'LIKE', '%'.$this->searchTerm.'%')
                ->orWhere('email', 'LIKE', '%'.$this->searchTerm.'%')
                ->orderBy($this->sortField, $this->sortAsc)->paginate(8)
            ]);
        }
    }

How can I solve it?

CodePudding user response：

You're mixing your and/or clauses. You want to make sure that user_id is always respected, but the rest are maybes. To fix that, pass the or clauses into a closure:

return view('livewire.dashboard.user-management', [
    'clients' => Client::where('user_id', '=', $user)
        ->where(function($query) {
            $query->where('name', 'LIKE', '%'.$this->searchTerm.'%')
                ->orWhere('surname', 'LIKE', '%'.$this->searchTerm.'%')
                ->orWhere('email', 'LIKE', '%'.$this->searchTerm.'%');
        })
        ->orderBy($this->sortField, $this->sortAsc)->paginate(8)
        
]);

CodePudding user response：

You should use the Eloquent relationships to avoid having to manually search for foreign keys in tables. You can also create a conditional query using when() that only searches when needed, although it's not really necessary – a search for '%%' will return all rows anyway.

public function render()
{
    $clients = Auth::user()
        ->clients()
        ->when($this->searchTerm !== '', function ($q) {
            $q->where('name', 'LIKE', '%' . $this->searchTerm . '%')
                ->orWhere('surname', 'LIKE', '%' . $this->searchTerm . '%')
                ->orWhere('email', 'LIKE', '%' . $this->searchTerm . '%')
        })
        ->orderBy($this->sortField, $this->sortAsc)
        ->paginate(8)

    return view('livewire.dashboard.user-management', compact('clients'));
}

This assumes a one-to-many relationship between User and Client models:

class User extends Authenticatable {
    public function clients() {
        return $this->hasMany(Client::class);
    }
}
class Client extends Model {
    public function user() {
        return $this->belongsTo(User::class);
    }
}

Oh, and NEVER use the loose equality operator ==; always use ===. It's safer and more consistent.