How to count the total of records in a query with limit and offset?

1
In a query with paginate I would use total() in view to return total records.

Using limit and offset can not do this, is there an alternative that does not involve just making a new query just to count? / p>
$comentarios = $this->comentario::with('user')
    ->where('serie_id', $request->get('serie_id'))
    ->orderBy('created_at', $filtro)
    ->limit(2)
    ->offset($offset)
    ->get();
    
asked by anonymous 15.12.2017 / 15:16

2 answers

2

I think you can do what you want by separating the queries and reusing:

$query = $this->comentario()
              ->with('user')
              ->where('serie_id', $request->get('serie_id'));

$quantidade_comentarios = $query->count();

$comentarios $query->orderBy('created_at', $filtro)
       ->limit(2)
       ->offset($offset)
       ->get();

Note : In the above case, two queries will be executed, but reusing portions of Laravel's Query Builder.

    
15.12.2017 / 16:29
2

When using limit and offset it is mandatory to use another SQL to count the number of records. For me, in this case, you can use paginate() that will have the same effect and in addition brings the total of record, an example of this is JSON generated, note:

{
   "total": 50,
   "per_page": 15,
   "current_page": 1,
   "last_page": 4,
   "first_page_url": "http://laravel.app?page=1",
   "last_page_url": "http://laravel.app?page=4",
   "next_page_url": "http://laravel.app?page=2",
   "prev_page_url": null,
   "path": "http://laravel.app",
   "from": 1,
   "to": 15,
   "data":[
        {
            // Result Object
        },
        {
            // Result Object
        }
   ]
}

In this example you have a key with the total name, which is the amount of record contained in your database, it is worth remembering that any placed filter also reflects on that key then bringing the data contained by the filter, but also internally are executed two statements SQL .

So, just to ratify if you want to use limit and offset and want the record amount of this table, you should do another SQL to get such information and this will not lead to poor performance, other factors together.

In your code:

$comentarios = $this->comentario::with('user')
    ->where('serie_id', $request->get('serie_id'))
    ->orderBy('created_at', $filtro)
    ->paginate(2);

In the code if you want to pass the parameters just follow what is in the framework /src/Illuminate/Pagination/Paginator.php

paginate(int $perPage = null, 
         array $columns = ['*'], 
         string $pageName = 'page', 
         int|null $page = null)

This will have the same effect (with two SQL it has nowhere to escape).

Reference: Converting Results To JSON

    
15.12.2017 / 16:06