Use different query to count filtered record

[abudawud]

Summary of problem or feature request

On very complex query where i need to join many table to get some aggregation value and complex calculation for column:

• Total Order,
• Total Sent,
• Total Procesed,
• warehouse stock,
• material stock,
• etc.

I'm not need to filter all column above because it's just a numeric value so i set the searchable flag to false. The duration to complete this query is about 500 ms, so the total duration to complete datatable request is 1500 ms consist of 500 ms to count total record, 500 ms to cound filtered record and 500 ms to get the first 25 record for paging. I have ommit the 500 ms to count total record by set the total record manually using Order::count(). now i want to ommit the count of filtered record but i don't now how to apply the filter value from datatable to the model. is there any suggestion ?

System details

• Operating System: Manjaro Linux
• 8
• Laravel Version 9
• Laravel-Datatables 10.1

[Abdelraman]

facing the same issue, trying skip paging, crash the whole table

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[abudawud]

Hello I need help.

On Sun, Jan 14, 2024, 7:16 AM github-actions[bot] @.***> wrote:

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

— Reply to this email directly, view it on GitHub https://github.com/yajra/laravel-datatables/issues/3103#issuecomment-1890803196, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADYQQ5U6X5JPZRV5V6GRGQDYOMPVHAVCNFSM6AAAAABAKLRKOGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOJQHAYDGMJZGY . You are receiving this because you authored the thread.Message ID: @.***>

[romanstingler]

what I did in my project WORKS ONLY ON POSTGRES, for MYSQL, MARIADB YOU HAVE TO CHANGE THE ESTIMATED METHOD

is at the end of the datatable method in the controller

        $estimatedEntries = $this->estimateEntries($query);

        return $dt->setTotalRecords($estimatedEntries)->setFilteredRecords($estimatedEntries)->toJson();

and my estimatedEntries method looks like this

    protected function estimateEntries(Builder $query): int
    {
        $explainResult     = $query->explain();
        $estimatedRowCount = 0;

        foreach ($explainResult as $row) {
            // Check if the row contains the estimated row count information
            if (preg_match('/\brows=([0-9]+)\b/', json_encode($row), $matches)) {
                $estimatedRowCount = (int) $matches[1];
                break;
            }
        }

        return $estimatedRowCount;
    }

Basically just calls the same query with EXPLAIN, which uses the DB statistics to estimate the count of rows depending on how often you write/delete to the table(s) the number is more or less accurate enough for my use case.

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.