Cache paginator aggregate count

[sathia-musso]

Hi, I found that on a large-ish datasets simple queries such as:

select count(*) as aggregate from `blah` where `blah`.`deleted_at` is null
+-----------+
| aggregate |
+-----------+
|   8453595 |
+-----------+
1 row in set (0.725 sec)

can take a lot of time (yes, deleted_at is an index), and it would be probably better to cache them for a little while, how about something like this in the Builder class?

    return Cache::remember("aggregate_cache_".md5($this->toSql()) , 360, function () use($columns)  {

        $without = $this->unions ? ['orders', 'limit', 'offset'] : ['columns', 'orders', 'limit', 'offset'];

        return $this->cloneWithout($without)
            ->cloneWithoutBindings($this->unions ? ['order'] : ['select', 'order'])
            ->setAggregate('count', $this->withoutSelectAliases($columns))
            ->get()->all();

    });

this should prevent any collision and provide a good method to make paginator fast

[Angel5a]

First of all, it is a bad thing to put cache inside a builder.

Standard paginator is known to be slow. Use simple pagination or cursor pagination. If us need a count, you can make a separate method for it. And, you can always create your own paginator with cache and stuff.

P.S.: Do not forget about bindings:

> User::where('is_active', 1)->toSql();
= "select * from `users` where `is_active` = ?"

> User::where('is_active', 0)->toSql();
= "select * from `users` where `is_active` = ?"

[sathia-musso]

you are right, I forgot the bindings part, and I agree about the bad design. I'll see if simple pagination works with livewire.

thanks