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

Question

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

Using the limit and offset cannot do this, there is some alternative that does not involve making a new query just to tell?

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

Answer 1

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

{
   "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 has a key with the total name, which is the amount of record contained in your database, it is worth remembering that any filter placed also reflects in this key bringing then the data contained by the filter, but also internally are executed two instructions SQL.

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

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)

that will have the same effect (with two SQL this has nowhere to escape).

_{Reference: Converting Results To JSON}

Answer 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 excerpts from Laravel’s Query Builder.