Pagination not working in Laravel

Question

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?

Answer 1

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;