Make use of eloquent cache for count

[liepumartins]

Summary of problem or feature request

If one adds some sort of eloquent model caching mechanism, such as laravel-model-caching
data queries are nicely cached and retrieved from cache upon subsequent requests, however count queries (for getting total and filtered count) are not. And every page change triggers those. Since I work with a large dataset, that has some joined tables, both count queries take about 350ms each (data query for page takes about 100ms). Which is why I would want to cache them.
Count does not change that often.

I suspect this happens because counting is done with raw query, instead of using eloquent and count().

laravel-datatables/src/QueryDataTable.php

Line 190 in d1bc415

$table = $this->connection->raw('(' . $builder->toSql() . ') count_row_table');

System details

• Operating System Linux
• PHP Version PHP 7.3.6-1+ubuntu18.04.1
• Laravel Version 5.8
• Laravel-Datatables Version 9.0

[yajra]

Not yet supported but I guess we could try to override the count method of query builder and write the eloquent version inside https://github.com/yajra/laravel-datatables/blob/d1bc415a72d252a009d65d9aca2eb2b70035deaf/src/EloquentDataTable.php?

Just an idea where to start for a possible PR :) thanks!

[liepumartins]

I simplified QueryDataTable class count() and prepareCountQuery() methods, as follows:

    public function count()
    {
        $builder = $this->prepareCountQuery();
        return $builder->count();
    }
    protected function prepareCountQuery()
    {
        $builder = clone $this->query;
        return $builder;
    }

Caching now works for count().
What did I oversee, what was the purpose of all the raw query stuff happening there?

[KnightAR]

For consistently my count() duplicates the original query using Eloquent builder and produces near identical query

    public function count()
    {
        $builder = clone $this->query;
        $builder->setQuery($this->query->getQuery()->newQuery());
        return $builder->fromSub($this->prepareCountQuery(), 'count_row_table')->withoutGlobalScopes()->count();
    }

place in the EloquentDataTable class, or extend it in your app, and change datatables.php to use the new custom class.

I am using this to allow LadaCache to cache the query properly.

[Arne1303]

We implemented this in our internal version (with a simple override), It works and saves a bit of time, but you need to make sure that the cache is always cleared when new entries are added or old ones are removed I didn't expect it, but a lot of users seem to take issue if the number does not change immediately.
We also cache the search panes, that one does not raise any issues and also saves a lot more time since the queries are more complex.