CODEWITHSUNDEEP
mysqlsqllaraveleloquent
DatsunBing
DatsunBing

Reputation: 9076

How to query an Eloquent ORM many-to-many relationship

I have Staff who work in States (that is, states of Australia).

Staff are stored in the users table. States are stored in the states table. The many-to-many relationship between the two is stored in staff_state.

On my Staff model I have a method as follows:

public function workStates()
{
    return $this->belongsToMany(Core\State::class);
}

I want to retrieve all users who work in the state of NSW, and have either a first name or last name that includes the letters 'dan'. Here is my Eloquent query:

    $query = Staff::whereHas('workStates', function ($query) {
            $query->where('state_id', '=', 'NSW');})
        ->where('first_name', 'like', '%dan%')
        ->orWhere('last_name', 'like', '%dan%')
        ->orderBy('first_name', 'asc')
        ->orderBy('last_name', 'asc');

My query results all include the letters 'dan', however some of the Staff do not work in NSW. What am I doing wrong?

Here is the SQL query generated by Eloquent:

select * from `users` where (exists 
    (select * from `states` inner join `staff_state` on `states`.`id` = `staff_state`.`state_id` 
        where `users`.`id` = `staff_state`.`staff_id` and `state_id` = 'NSW') 
    and `first_name` like '%dan%' or `last_name` like '%dan%') 
    and `users`.`deleted_at` is null order by `first_name` asc, `last_name` asc

Thanks..!

Upvotes: 1

Views: 46

Answers (2)

Camilo
Camilo

Reputation: 7204

I would try grouping up the first_name and last_name conditions.

I think you can do this by passing a closure to the where() function. Maybe something like this:

Staff::whereHas('workStates', function ($query) {
    $query->where('state_id', '=', 'NSW');
})->where(function($query) {
    $query
        ->where('first_name', 'like', '%dan%')
        ->orWhere('last_name', 'like', '%dan%');
})->orderBy('first_name', 'asc')->orderBy('last_name', 'asc');

Upvotes: 1

Alexey Mezenin
Alexey Mezenin

Reputation: 163948

You shouldn't use state_id, use the real column name from states table. Also, group name related logic within where() closure. For example, if the column name is name:

$state = 'NSW';
$name = 'dan';
Staff::whereHas('workStates', function ($query) use($state) {
        $query->where('name', $state);
    })
    ->where(function($query) use($name) {
        $query->where('first_name', 'like', '%' . $name . '%')
              ->orWhere('last_name', 'like', '%' . $name . '%');
    })
    ->orderBy('first_name', 'asc')
    ->orderBy('last_name', 'asc')
    ->get();

Upvotes: 0

Related Questions