filter eloquent query with many to many relationship table

Hi i have a datatable with filters so with each filter i want to adjust the query

for the users table i have a table called roles that is a many 2 many with roles and users

i use this code

   //filters
    $active = false;
    $non_active = false;
    $role = false;

    //check active status
    if ($request->get('active') == 'true') {
        $active = true;
    }
    if ($request->get('non_active') == 'true') {
        $non_active = true;
    }
    if ($request->get('non_active') == 'true' && $request->get('active') == 'true') {
        $active = false;
        $non_active = false;
    }

    //check role filter
    if ($request->get('role')) {
        $role = true;
    }


    $users = User::with('roles')
        ->when($active, function ($q) use ($active) {
            return $q->where('active', 1);
        })
        ->when($non_active, function ($q) use ($non_active) {
            return $q->where('active', 0);
        })
        ->when($role, function ($q) use ($role, $request) { //THIS DOES NOT WORK
           return $q->where('roles.id', $request->get('role')); //THIS DOES NOT WORK
      }) //THIS DOES NOT WORK
        ->get();

    return DataTables::of($users)
        ->addColumn('role', function (User $user) {
            return $user->roles->map(function($role) {
                return $role->name;
            })->implode('<br>');
        })
        ->make(true);

the role filter does not work... it says: "message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'roles.id' in 'where clause' (SQL: select * from users where roles.id = 1)", "exception": "Illuminate\Database\QueryException",

how can i achieve this? to add a where statement but for the roles table

i have added belongsto etc etc.. so the tables are set up correct but i dont know how to query this

Cinek

8 years ago

Level 6

Can you show your roles relation in User model?

r2get

8 years ago

Level 1

in my user model

/*
 * many-to-many relationship between User and Role.
 */
public function roles()
{
    return $this->belongsToMany(Role::class);
}

and in my roles role model

public function users()
{
    return $this->belongsToMany(User::class);
}

r2get

8 years ago

Best Answer

Level 1

after some trial and error i got

    $users = User::with('roles')
        ->when($active, function ($q) use ($active) {
            return $q->where('active', 1);
        })
        ->when($non_active, function ($q) use ($non_active) {
            return $q->where('active', 0);
        })
        ->when($role, function ($q) use ($role, $request) {
            return $q->whereHas('roles', function ($q) use ($request) {
                $q->where('role_id', $request->get('role'));
            });
        })->get();

...this outputs the correct data,, dont know if this is "the" way... but it works!

Cronix

8 years ago

Level 67

I think you can get rid of use ($active) and use ($non_active) and change use ($role, $request) to just use ($request).

You're not using those variables in any of the closures so you don't need to pass them in.

$users = User::with('roles')
        ->when($active, function ($q) {
            return $q->where('active', 1);
        })
        ->when($non_active, function ($q) {
            return $q->where('active', 0);
        })
        ->when($role, function ($q) use ($request) {
            return $q->whereHas('roles', function ($q) use ($request) {
                $q->where('role_id', $request->get('role'));
            });
        })->get();

Should give you the same result. I just got rid of the variables that weren't being used in the closures.

Please or to participate in this conversation.