Why `having` only worked on fields that are `group

问题:

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?

回答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

回答2:

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.