php - Pagination not working in Laravel


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 = cu.user_id
            where `o`.`order_status_id` in (".implode(',',$order_status).")";

            //$sql .= "and cu.payment_done != 'yes'";

    if($is_manager == 'no')
        $sql .= "and = '".$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 .= " and o.university_enrollment_no = '".$filter['university_enrollment_no']."'";

    if(isset($filter['university_id']) and $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 LIKE '%".$filter['email']."%'";

    if(isset($filter['order_status_id']) and $filter['order_status_id']!=''  ){
        $sql .= " and o.order_status_id = '".$filter['order_status_id']."'";

        $sql .= " and bm.batch_id in (".implode(',',$filter['batch']).")";

        $sql .= " and o.order_status_id in (".implode(',',$filter['order_status']).")";

        $sql .= " and o.order_sub_status_id in (".implode(',',$filter['order_sub_status']).")";

        $sql .= " and in (".implode(',',$filter['user']).")";

        $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 forPaginator::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 callingpaginate on the query directly.

$applicants = DB::table('Oder')
    ->where('order_id', $order_id)
    ->paginate(10); // replacing 10 with your items-per-page

return $applicants;

People are also looking for solutions to the problem: php - Use a callback function on form validation rules from an external library - Codeigniter


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.