Level 75
Have you tried moving ->paginate(5); around to a different location in the code or tested with one of the name of email only?
Jan 14, 2017
6
Level 1
Laravel 5.3 Pagination With Query Builder Problem [Solved]
the following code:
$data = User::orWhere(function($query) use ($request){
if ($name = $request->input("name")) {
$query->orWhere("name", 'LIKE', '%'.$name.'%');
}
if ($email = $request->input("email")) {
$query->orWhere("email", 'LIKE', '%'.$email.'%');
}
if($roles = !empty($request->input('roles'))) {
$query->orWhereHas('roles', function ($query) use ($request) {
$roles = implode(",",$request->input('roles'));
$query->whereIn('role_id', [$roles]);
});
}
})
->paginate(5);
is producing the result:
Array
(
[0] => Array
(
[query] => select count(*) as aggregate from `users` where (`name` LIKE ? or exists (select * from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `role_user`.`user_id` = `users`.`id` and `role_id` in (?)))
[bindings] => Array
(
[0] => %string%
[1] => 1,2
)
[time] => 0.95
)
[1] => Array
(
[query] => select * from `users` where (`name` LIKE ? or exists (select * from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `role_user`.`user_id` = `users`.`id` and `role_id` in (?))) limit 5 offset 0
[bindings] => Array
(
[0] => %string%
[1] => 1,2
)
[time] => 0.46
)
)
and the result given to blade isnt right. what im doing wrong? how to fix it?
Level 1
sorry, i think that i dont showed my problem properly:
I have 3 tables:
-users -role_user (pivot table) -roles
i have a form for search that send the columns 'name' and 'email' that exists in table 'users' and the column 'id' that references to roles. i just want to do submit a search for 2 tables using a pivot table, how to do this?
Level 75
Just do a length aware paginator.
Level 50
@Hikaro maybe something like this would be better... haven't tested it though..
$data = User::when(request()->has('name'), function($query) {
$query->orWhere('name', 'LIKE', '%' . request('name') . '%');
})->when(request()->has('email'), function($query) {
$query->orWhere('email', 'LIKE', '%' . request('email') . '%');
})->when(request()->has('roles'), function($query) {
$query->orWhereHas('roles', function($query2) {
$query2->whereIn('role_id', request('roles'));
});
})->paginate(5);
2 likes
Level 1
tried this:
$data = User::when($request->input("name"), function($query) use ($request) {
return $query->orWhere('name', 'LIKE', '%' . $request->input("name") . '%');
})->when($request->input("email"), function($query) use ($request) {
return $query->orWhere('email', 'LIKE', '%' . $request->input("email") . '%');
})->when($request->input('roles'), function($query) use ($request) {
return $query->orWhereHas('roles', function($query2) use ($request) {
$roles = implode(",",$request->input('roles'));
return $query2->whereIn('id', [$roles]);
});
})->paginate(5);
the code executed, but without espected result, i think i missing something in sql, i have an user and a have roles, want to search for user name OR user email OR role_x OR role_y (...) OR role_z
the 'name' is ok, the 'email' is ok, but the roles isnt giving me the expected result if i send more than one for search 'where in (1,2,3)'...
Level 1
ok, i think this works, thanks for your support!
$data = User::when($request->input("name"), function($query) use ($request) {
return $query->orWhere('name', 'LIKE', '%' . $request->input("name") . '%');
})->when($request->input("email"), function($query) use ($request) {
return $query->orWhere('email', 'LIKE', '%' . $request->input("email") . '%');
})->when($request->input('roles'), function($query) use ($request) {
return $query->orWhereHas('roles', function($query2) use ($request) {
return $query2->whereIn('id', $request->input('roles'));
});
})->paginate(5);
1 like
Please or to participate in this conversation.