Laravel using eloquent Count with Having

Question

I have two variables $customers (that holds all the rows) and $total that holds the total rows of the query.

I usually do the following query:

$customers = Customers::select
(
    'customer.id', 
    'customer.name', 
    'customer.min_tolerance',
    DB::raw('(SELECT MAX(tolerance) FROM customers_tolerances WHERE customer_id = customer.id) AS tolerance')
)
->from('customers AS customer')
->whereIn('customer.id', $request->customers);

$total = $customers->count();

$customers = $customers->limit($request->limit)
    ->offset($request->offset)
    ->get();

This works great. I get all the rows limited (usually 20 per page) plus the total rows.

My problem is that I added a having clause to my query, so it looks like this now:

$customers = Customers::select
(
    'customer.id', 
    'customer.name', 
    'customer.min_tolerance',
    DB::raw('(SELECT MAX(tolerance) FROM customers_tolerances WHERE customer_id = customer.id) AS tolerance')
)
->from('customers AS customer')
->whereIn('customer.id', $request->customers)
->havingRaw('tolerance >= customer.min_tolerance');

And the $count stopped working as it triggers an error:

Column not found: 1054 Unknown column 'tolerance' in 'having clause' select count(*) as aggregate from customers as customer having tolerance >= customer.min_tolerance)

So how can I use count with having clause?

I don't think you need the `->from('customers as customer')` if you are already selecting from the Customers model. Also, check this answer. https://stackoverflow.com/questions/50081540/laravel-where-count-n — msmahon, May 26 '20 at 15:40
`having` is unnecessary since the aggregate is in a subquery. Just change that to a whereRaw — aynber, May 26 '20 at 16:19
@Hardood you mean `SELECT COUNT(*) FROM (SELECT ...)`. If so, my problem with that, and I have tried, is the slowness of the query. — Linesofcode, May 26 '20 at 16:50

score 0 · Accepted Answer · answered May 26 '20 at 17:34

Solved.

Before creating this post I tried to create a subquery, as follow:

SELECT COUNT(*) FROM (SELECT ...)

But the slowness of the query was too much, so I tried to look for answers here. The slowness was due to the lack of index in tables.

By adding ALTER TABLE customers_tolerances ADD INDEX(customer_id); I'm now able to retrieve fast the total results.

Laravel using eloquent Count with Having

1 Answers1