CODEWITHSUNDEEP
phplaravel
Wouter
Wouter

Reputation: 807

How to use pagination() in laravel by a direct query

I'm using a direct query in Laravel. But I will also use pagination. I have tried many ways to use the paginate function but always get an error.

When I read the laravel docs I am almost sure I have to use it like:

$blog = Db::select("SELECT * 
            FROM `wouter_blog_posts` 
            WHERE `published` IS NOT NULL 
            AND `published` = '1'
            AND `published_at` IS NOT NULL 
            AND `published_at` < NOW()
            AND (

            SELECT count( * ) 
            FROM `wouter_blog_tags` 
            INNER JOIN `wouter_blog_posts_tags` ON `wouter_blog_tags`.`id` = `wouter_blog_posts_tags`.`tags_id` 
            WHERE `wouter_blog_posts_tags`.`post_id` = `wouter_blog_posts`.`id` 
            AND `id` 
            IN (
             '".$tagid[0]->id."'
            )) >=1
            ORDER BY `published_at` DESC 
            LIMIT 10 
            OFFSET 0")->paginate(5);

But I get this error:

Call to a member function paginate() on array

What do I understand wrong form the laravel docs ?

Upvotes: 3

Views: 3579

Answers (1)

PaePae
PaePae

Reputation: 1074

Select by raw query will return results as array and lost access to paginate(). There are two solutions to utilize paginate().

(1) Use query builder

You need to build entire query using query builder to have an access to paginate() function. Go study how to convert your SQL to query builder style. It's worth as the query will be independent from database engines.

(2) Create your own paginate()

Here is the source of paginate() from class Illuminate/Database/Query/Builder (Laravel 5.0)

/**
 * Paginate the given query into a simple paginator.
 *
 * @param  int  $perPage
 * @param  array  $columns
 * @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
 */
public function paginate($perPage = 15, $columns = ['*'])
{
    $page = Paginator::resolveCurrentPage();

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

    $results = $this->forPage($page, $perPage)->get($columns);

    return new LengthAwarePaginator($results, $total, $perPage, $page, [
        'path' => Paginator::resolveCurrentPath(),
    ]);
}

So you may create your own paginate() like this.

use Illuminate\Pagination\Paginator;
use Illuminate\Pagination\LengthAwarePaginator;

class YourClass {

    public function paginateArray($data, $perPage = 15)
    {
        $page = Paginator::resolveCurrentPage();
        $total = count($data);
        $results = array_slice($data, ($page - 1) * $perPage, $perPage);

        return new LengthAwarePaginator($results, $total, $perPage, $page, [
            'path' => Paginator::resolveCurrentPath(),
        ]);
    }
}

And using is easy.

$blog = $this->paginateArray(
    DB::select( ... )
);

Upvotes: 3

Related Questions