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?

Answer 1

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