Closed
Description
Summary of problem
I have a query looking like this:
$companies = Company::select([
'companies.*',
DB::raw('SELECT COUNT(*) FROM orders WHERE orders.company_id = companies.id'),
])
...
This generates 2 queries:
- first query to count the number of rows (
count_row_table
) - 2nd query to select the current page results
This works well, but the count_row_table
query is taking a while because we have a lot of companies, and the query includes the aggregation function to count orders, which is useless in the count_row_table
query:
SELECT
COUNT(*) AS aggregate
FROM
(SELECT companies.*, (SELECT COUNT(*) FROM orders WHERE orders.company_id = companies.id) FROM companies) count_row_table
If I remove it from the select, the query becomes very fast again:
SELECT
COUNT(*) AS aggregate
FROM
(SELECT companies.* FROM companies) count_row_table
Is there any way to remove it from the count_row_table query?
Metadata
Metadata
Assignees
Labels
No labels