Laravel 5.6 selection by active records

Question

User table

depart_id | user_role

5 | 1

4,2 | 2

5,2 | 2

1,5 | 1

I want to fetch all user with user_role=1 and depart_id=5 and depart_id=1

How to achieve using active record(or usual query)?

My code is

$user_depart = array(5,1);
$user_role   = 1;

$data = DB::table('tbl_users')
            ->where('user_role', $user_role)
            ->whereIn('department_id',$user_depart)
            ->get();

Possible duplicate of [Laravel 4 eloquent WHERE with OR AND OR?](https://stackoverflow.com/questions/16995102/laravel-4-eloquent-where-with-or-and-or) — kabanus, Jul 26 '18 at 06:52
Why is there a `,` in the `depart_id` column? Is this a floating point number or multiple values? — Jerodev, Jul 26 '18 at 06:54
@Jerodev My table keeps different depart_id separate with commas — , Jul 26 '18 at 06:56
@OfficialONE You mean both objects with 5 and 1, or in another wording, all objects that have either 1 or 5? — kabanus, Jul 26 '18 at 06:56
@OfficialONE That is a very bad way to make a database and might bring forward problems in the future. — Jerodev, Jul 26 '18 at 06:57
@Jerodev yes ofcourse, its a running project and am not permission to edit the schema. I know its ulgly db design... — , Jul 26 '18 at 07:00

Евгений Одинец · Answer 1 · 2018-07-26T07:01:17.477

0

You have created an bad database structure. Best way - create table users_departs (user_id, depart_id)

And then you can do like this:

$userDeparts = [5,1];
$userRole = 1;
$users = User::with('users.departs')
    ->where('users.role_id', $userRole)
    ->whereIn('departs.id', $userDeparts)
    ->get();

edited Jul 26 '18 at 07:01

answered Jul 26 '18 at 06:55

Евгений Одинец

819
6
15

score 0 · Answer 2 · answered Jul 26 '18 at 07:34

I dont think a simple whereIn will work here. Given the schema of your table it is not quite a good practice assigning multiple keys in your column (IMO)
but you could give a try this. I tried using LIKE statement.

$user_depart = array(5,1);
$user_role   = 1;

$data = DB::table('tbl_users')
        ->where('user_role', $user_role)
        ->where(function($sql) use ($user_depart){
             for($i = 0; $i < count($user_depart); $i++){
                  $sql->orWhere('department_id','LIKE','%' . $user_depart[$i] . '%');
             }
        })
        ->get();

Laravel 5.6 selection by active records

2 Answers2