laravel paginate when use select case and parameter binding

Question

Laravel Version: 5.5

PHP Version:7

Hi, I want to execute this query:

select (case 
   when(title like 'my-keyword') then 1
   when(description like 'my-keyword') then 2
   ) as ordering from products where id > 10;

When I do this by query builder:

$products = DB::table('products')->select(DB::raw('(case 
   when(title like "?") then 1
   when(description like "?") then 2
   ) as ordering'))->where('id', '>', 10)->setBindings(['my-keyword', 'my-keyword'])->paginage(10);

this will going to get count and as we know this will remove all select part and replace it with count(*) as aggregate so if I use setBindings on this query builder and pass ['my-keyword', 'my-keyword'] to this query for aggregate will change to this:

select count(*) as aggregate from products where id > my-keyword;

So this will cause the problem of using pagination on this query and other alternatives like this query!

To solve the problem I changed some codes in /..../Query/Builder.php this:

$total = $this->getCountForPagination($columns);

to this:

$all = $this->get();
$total = $all->count();

for this situation I know it's wrong but for now it works!

what should I do to resolve this problem in a correct way?!

Answer 1

You can try this:

Write you raw query like this :

DB::select('RAW_QUERY');

It would return an array. You can use LengthAwarePaginator to paginate the array like so:

use Illuminate\Pagination\LengthAwarePaginator;


$this->paginateArray($array, $perPage); 

public function paginateArray($items, $perPage)
{
    $pageStart = \Request::get('page', 1);
    $offSet = ($pageStart * $perPage) - $perPage;
    $itemsForCurrentPage = array_slice($items, $offSet, $perPage, true);

    return new LengthAwarePaginator($itemsForCurrentPage, count($items), $perPage, Paginator::resolveCurrentPage(), array('path' => Paginator::resolveCurrentPath()));
}

Answer 2

Bindings are categorized in eloquent and setBindings overrides all existing bindings of the where part by default (just have a look at the signature of the method)

As such, you need to set the second parameter of setBindings to select

->setBindings(['my-keyword','my-keyword'], 'select')

Alternatively, this is the same for addBinding

->addBinding('my-keyword', 'select')