Laravel 4.2 Pagination with complex DB query

0
votes

I am new to Laravel 4.2.

I need to do some pagination in my view search result page.

What I am doing is writing this code in the controller -

public function getSpecials()
{
    $title = "Specials";

    
    $info = DB::table(DB::raw('`car` , `available_car`, `users`'))
                        ->select('car.car_maker', 'car.car_model', 'available_car.car_price', 'car.car_production_year', 'available_car.car_id', 'available_car.id', 'available_car.current_position')
                        ->where('available_car.car_id', '`car`.`car_id`')
                        ->where('available_car.is_sold', 'no')
                        ->whereRaw('`available_car`.`created_at` BETWEEN NOW() - INTERVAL 30 DAY AND NOW()')
                        ->orderByRaw('WEEK(`available_car`.`created_at`) DESC')
                        ->orderBy('available_car.car_price', 'desc')
                        ->orderBy('users.last_paid_date', 'desc')
                        ->orderBy('available_car.created_at', 'desc')
                        ->distinct()
                        ->get();

    $pagination = Paginator::make($info, count($info), 5);


    //var_dump($pagination );
    
    
    return View::make('specials',compact('pagination'))->with('info',$info)->with('title',$title);
}

I want to show 5 items per page. So I am doing paginate like this -

$pagination = Paginator::make($info, count($info), 5);

But the problem here is I am getting paginate number in the page perfectly. But, All page is showing the whole result, not showing only 5 items.

It is the output. (paginate is for 5 items per page and total no of entry is 7 in my case)

First Page

Page 1

Second Page

Page 2

Can anyone help me please? Thanks in advance for helping.

2
phpmysqllaravelpdopagination
What web server are you using? Can you show us how you use url rewrite? – Jerodev
instead of get(), use paginate(5). you have a direct query builder object. you can chain it. if you want to go custom way, you have to again add skip and take and calculate them from the page parameter. – itachi
I am using WAMP server. If I use paginate() nothing is shown, do I need to change any settings in WAMP server? We are not using any URL rewrite.... We are using only default laravel pagination – Abrar Jahin
I am using Paginating in view like {{ $pagination->links() }} – Abrar Jahin
URI is like localhost/car/public/specials?page=2 – Abrar Jahin

2 Answers

3
votes

just make a small change in the code and laravel will take care of the rest. 

public function getSpecials()
{
    $title = "Specials";


    $info = DB::table(DB::raw('`car` , `available_car`, `users`'))
                        ->select('car.car_maker', 'car.car_model', 'available_car.car_price', 'car.car_production_year', 'available_car.car_id', 'available_car.id', 'available_car.current_position')
                        ->where('available_car.car_id', '`car`.`car_id`')
                        ->where('available_car.is_sold', 'no')
                        ->whereRaw('`available_car`.`created_at` BETWEEN NOW() - INTERVAL 30 DAY AND NOW()')
                        ->orderByRaw('WEEK(`available_car`.`created_at`) DESC')
                        ->orderBy('available_car.car_price', 'desc')
                        ->orderBy('users.last_paid_date', 'desc')
                        ->orderBy('available_car.created_at', 'desc')
                        ->distinct()
                        ->paginate(5);

return View::make('specials',['info' => $info, 'title' => $title]);
}

in view:

Iterate like this:

@if(!$info->isEmpty())

    @foreach($info as $i)
    do whatever you want
    @endforeach

@else
   no data to show....

@endif

{{$info->links()}}
3
votes

You need to create your paginator manually (Check this question for more details):

$pageNumber = Input::get('page', 1);
$perPage = 5;

$info = DB::table(DB::raw('`car` , `available_car`, `users`'))
                    ->select('car.car_maker', 'car.car_model', 'available_car.car_price', 'car.car_production_year', 'available_car.car_id', 'available_car.id', 'available_car.current_position')
                    ->where('available_car.car_id', '`car`.`car_id`')
                    ->where('available_car.is_sold', 'no')
                    ->whereRaw('`available_car`.`created_at` BETWEEN NOW() - INTERVAL 30 DAY AND NOW()')
                    ->orderByRaw('WEEK(`available_car`.`created_at`) DESC')
                    ->orderBy('available_car.car_price', 'desc')
                    ->orderBy('users.last_paid_date', 'desc')
                    ->orderBy('available_car.created_at', 'desc')
                    ->distinct()
                    ->get();

$slice = array_slice($info, $perpage * ($pageNumber - 1), $perpage);
$info = Paginator::make($slice, count($info), $perPage);
return View::make('specials',['info' => $info, 'title' => $title]);

Then print results in a foreach loop like suggested by @itachi.