使用聚合（分组）函数进行CGridView过滤 - 例如MAX（）

Question

I have a CGridView that uses a MAX() mysql column to provide data for one of the columns. I have that working with sorting, but I can't figure out filtering. I assumed that I could just use CDbCriteria::compare() call to set it, but it's not working. Ideas?

My search function:

    $criteria = new CDbCriteria;
    $criteria->condition = 't.is_deleted = 0 and is_admin = 0';
    // get last note date
    $criteria->select = array('t.*', 'MAX(n.visit_date) AS last_note');
    $criteria->join = 'LEFT JOIN notes n ON t.id = n.distributor_id';
    $criteria->group = 't.id';
    $criteria->order = 't.status';

    $criteria->compare('t.type', $this->type);
    $criteria->compare('t.name', $this->name, true);
    $criteria->compare('t.city', $this->city, true);
    $criteria->compare('t.state', $this->state);
    $criteria->compare('last_note', $this->last_note);



    return new CActiveDataProvider('Distributor', array(
            'criteria' => $criteria,
            'pagination' => array(
                'pageSize' => 20
            ),
            'sort' => array(
                'defaultOrder' => 'name',
                'attributes' => array(
                    'last_note' => array(
                        'asc' => 'last_note',
                        'desc' => 'last_note DESC'
                    ),
                )
            ),
        ));

In my view, I just have the name and value values set.

The error I get is CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'last_note' in 'where clause'

Edit: The only way I found to do this is (because you can't have aggregate functions in where clauses) is to check the $_GET array for the last_note value, and add a having clause. Note, this isn't parameterized or anything yet, I just wanted to rough it out to see if it would work:

if(isset($_GET['Distributor']['last_note']) && $_GET['Distributor']['last_note'] != '') {
        $criteria->having = 'MAX(n.visit_date)=\'' . $this->last_note . "'";
}

I hate using the request variables in the model like this, but there isn't much else I could do.

Answer 1

You're on the right track. You filter aggregate functions like MAX() and SUM() using having. In a query like this, you should probably add a group, otherwise you'll end up with a lot of duplicated data in your result when a Distributor has multiple notes on the same day (assuming t.id is a primary key). Second, you should use named params instead of putting variables directly into SQL. So, instead of compare('last_note'... you'd end up with something like this:

$criteria->group = 't.id';
$criteria->having = 'MAX(n.visit_date) = :last_note';
$criteria->params = array(':last_note' => $this->last_note);