I have a legacy Laravel application. In one of the methods, they've coded a query using raw SQL like this:
$sql = "select * from (select `o`.`order_id`, `o`.`order_status_id`, `o`.`business_type`,
`o`.`university_name`, `o`.`first_name`, `o`.`last_name`, `o`.`email`, `o`.`telephone`, `o`.`enrollment_no`,
`o`.`application_no`, `o`.`date_added`, `o`.`term_id`, `os`.`name` as `order_status`, `u`.`id` as `user_id`,`cso`.`last_call_time`,`cso`.`next_call_time`,
`oss`.`name` as `order_sub_status`, `o`.`total`, `o`.`product_id`, `bm`.`batch_name`, `o`.`university_enrollment_no`,
`ptb`.`current_term_id` as `current_batch_term`, `tm`.`type` as `term_type`, `tm`.`value`,`u`.`name` as `username`
from `order` as `o`
inner join `order_status` as `os` on `os`.`order_status_id` = `o`.`order_status_id`
left join `call_schedule_order` as `cso` on `cso`.`order_id`= `o`.`order_id`
left join `order_sub_status` as `oss` on `oss`.`order_sub_status_id` = `o`.`order_sub_status_id` left join `batch_master` as `bm` on `bm`.`batch_id` = `o`.`batch_id`
left join `term_master` as `tm` on `tm`.`term_id` = `o`.`term_id` left join `order_dropout_status` as `ods` on `ods`.`reason_id` = `o`.`reason_id` left
join `product_term_batch` as `ptb` on `ptb`.`batch_id` = `o`.`batch_id` and `ptb`.`product_id` = `o`.`product_id`
left join collection_user as cu on cu.order_id = o.order_id
left join users as u on u.id = cu.user_id
where `o`.`order_status_id` in (".implode(',',$order_status).")";
//$sql .= "and cu.payment_done != 'yes'";
if($is_manager == 'no')
{
$sql .= "and u.id = '".$user_id."'";
}
if(isset($filter['order_id']) and $filter['order_id']!='' ){
$sql .= " and o.order_id = '".$filter['order_id']."'";
}
if(isset($filter['application_no']) and $filter['application_no']!='' ){
$sql .= " and o.application_no = '".$filter['application_no']."'";
}
if(isset($filter['enrollment_no']) and $filter['enrollment_no']!='' ){
$sql .= " and o.enrollment_no = '".$filter['enrollment_no']."'";
}
if(isset($filter['university_enrollment_no']) and $filter['university_enrollment_no']!='' ){
//$sql->where('o.university_enrollment_no','LIKE','%'.$filter['university_enrollment_no'].'%');
$sql .= " and o.university_enrollment_no = '".$filter['university_enrollment_no']."'";
}
if(isset($filter['university_id']) and $filter['university_id']!='' ){
//$sql->where('o.university_id','=',$filter['university_id']);
$sql .= " and o.university_id = '".$filter['university_id']."'";
}
if(isset($filter['name']) and $filter['name']!='' ){
$sql .= " and (o.first_name LIKE '%".$filter['name']."%'
or o.last_name LIKE '%".$filter['name']."%')";
}
if(isset($filter['phone']) and $filter['phone']!='' ){
$sql .= " and o.telephone LIKE '%".$filter['phone']."%'";
}
if(isset($filter['email']) and $filter['email']!='' ){
$sql .= " and o.email LIKE '%".$filter['email']."%'";
}
if(isset($filter['order_status_id']) and $filter['order_status_id']!='' ){
$sql .= " and o.order_status_id = '".$filter['order_status_id']."'";
}
if(isset($filter['batch'])){
$sql .= " and bm.batch_id in (".implode(',',$filter['batch']).")";
}
if(isset($filter['order_status'])){
$sql .= " and o.order_status_id in (".implode(',',$filter['order_status']).")";
}
if(isset($filter['order_sub_status'])){
$sql .= " and o.order_sub_status_id in (".implode(',',$filter['order_sub_status']).")";
}
if(isset($filter['user'])){
$sql .= " and u.id in (".implode(',',$filter['user']).")";
}
if(isset($filter['due_calls'])){
$time = date('Y-m-d H:i:s');
$sql .= " and cso.user_id = '".$user_id."'";
$sql .= " and cso.next_call_time < '".$time."'";
}
if( isset($filter['scheduled_call']) and $filter['scheduled_call']!='' and $filter['scheduled_call'] == 'show' ){
$sql .= " and cso.user_id = '".$user_id."'";
$sql .= " and cso.next_call_time IS NOT NULL";
$sql .= " order by `cso`.`updatedAt` asc";
}
if( isset($filter['call_status_id']) and $filter['call_status_id']!='' ){
$sql .= " and cso.user_id = '".$user_id."'";
$sql .= " and cso.call_status_id NOT IN (1,2,5,6,9,10,12)";
$sql .= " order by `cso`.`updatedAt` asc";
}
if( isset($filter['UNSCH_call_status_id']) and $filter['UNSCH_call_status_id']!='' ){
$sql .= " and cso.user_id = '".$user_id."'";
$sql .= " and cso.call_status_id IN (1,2,5,6,9,10,12)";
$sql .= " order by `cso`.`updatedAt` asc";
}
if(!isset($filter['UNSCH_call_status_id']) && !isset($filter['call_status_id']) && !isset($filter['scheduled_call']))
$sql .= " order by `o`.`order_id` asc";
$sql .= ") as x
group by x.order_id LIMIT ".$page.",".$limit;
$applicants = DB::Select($sql);
$applicants = Paginator::make($applicants, count($applicants), $limit);
return $applicants;
However, when I try to echo $applicants->links(), nothing gets printed out.
If I change the limit size (as shown below), it displays links, albeit incorrect links.
$applicants = Paginator::make($applicants, count($applicants), $limit - 10);
How can I fix this?
According to the documentation, the parameters for Paginator::make are
Paginator::make($items, $totalItems, $perPage);
That makes sense why the first version didn't show any pages; since your query has a limit applied by the raw query, the count of applicants (second parameter) will match the number of items in the first parameter, so it'll assume all the items fit on one page.
Instead of creating the Paginator manually, I would suggest calling paginate on the query directly.
$applicants = DB::table('Oder')
->where('order_id', $order_id)
->paginate(10); // replacing 10 with your items-per-page
return $applicants;