Why `having` only worked on fields that are `group by` on laravel eloquent?

Question

My script laravel eloquent like this :

$query = $this->item->select('a.*','b.attribute_code')
    ->from('items as a') 
    ->join('attr_maps as b','b.number','=','a.number')
    ->groupBy('a.number');

foreach($param as $key => $value) {
    $query = $query->having($key, '=', $value);
}

$query = $query->paginate(10);

My $param is dynamic. It can change

If $param is array('number'=>'1234'), it works. No error

If $param is array('description'=>'test'), there exist error : Unknown column 'description' in 'having clause'

I tried all fields in the table items. Only the number field works. Apparently because the number field is group by

How can I make all field in the items table works if using having?

Why not using just "where"? remember from sql having is used only for agreggation. I believe you're trying to filter not aggregate. — Roger Rodriguez Texido, Sep 16 '18 at 18:17
@RogerRodriguezTexido My case is quite complicated. My case is actually like this: https://stackoverflow.com/questions/52241536/how-can-i-convert-conditional-aggregation-mysql-to-laravel-query. See `$param`. It's dynamic. So I need to using having to filter it — moses toh, Sep 16 '18 at 23:00

score 1 · Answer 1 · answered Sep 17 '18 at 04:22

The HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates. The HAVING clause executed after SELECT, so if you apply HAVING on columns which is not in group by or not in aggregate function then it will work as where, which is no use because select clause is already executed. And i think just because of that eloquent may throw exception, not sure though.

What you can do, check your param key if it is in group by fields then apply having if not then add it as where condition like this.

$query = $this->item->select('a.*','b.attribute_code')
    ->from('items as a') 
    ->join('attr_maps as b','b.number','=','a.number')
    ->groupBy('a.number');

foreach($param as $key => $value) {
    if($key== 'number'){
        $query = $query->having($key, '=', $value);
    }else{
        $query = $query->where($key, '=', $value);
    }
}

you can check here WHERE vs HAVING

Seems `where` doesn't work after `groupBy`. `where` should it be placed before `groupBy` — moses toh, Sep 19 '18 at 08:32
The order is irrelevant, Laravel takes care of that. Did you try it? — Jonas Staudenmeir, Sep 24 '18 at 21:10

score 0 · Answer 2 · answered Sep 16 '18 at 22:59

0

It's because Laravel's Eloquent ORM is active-record inspired and very database centric.

In the end, it's mainly because having is an sql concept which is used to filter rows in aggregate results.

In short, you're dealing with patches to problems in the SQL language which the Eloquent ORM has not abstracted away.

answered Sep 16 '18 at 22:59

Stratadox

1,291
8
21

My case is quite complicated. My case is actually like this: https://stackoverflow.com/questions/52241536/how-can-i-convert-conditional-aggregation-mysql-to-laravel-query. See `$param`. It's dynamic. So I need to using having to filter it – moses toh Sep 16 '18 at 23:02
Oh, apologies, I seem to have answered the question you were asking rather than the problem you're facing. Anyway, having is made for aggregate functions. Number works because it's used as group by, for the rest you should use `where`. – Stratadox Sep 16 '18 at 23:08
No problem. Whereas if I try run in mysql query in database gui, it works. This just not works in laravel eloquent – moses toh Sep 16 '18 at 23:10
Maybe you should try and look at the actual query (or queries) Eloquent is running. As far as I know you can't just select all columns from a table in an aggregate query without grouping them all, so there's possibly some magic going on. – Stratadox Sep 16 '18 at 23:15

Why `having` only worked on fields that are `group by` on laravel eloquent?

2 Answers2

Linked