StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCGridView filtering with aggregate (grouping) functions - eg MAX()
text
Body
copied!<p>I have a CGridView that uses a <code>MAX()</code> 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 <code>CDbCriteria::compare()</code> call to set it, but it's not working. Ideas?</p> <p>My search function:</p> <pre><code> $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' ), ) ), )); </code></pre> <p>In my view, I just have the name and value values set.</p> <p>The error I get is <code>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'last_note' in 'where clause'</code></p> <p>Edit: The only way I found to do this is (because you can't have aggregate functions in where clauses) is to check the <code>$_GET</code> 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:</p> <pre><code>if(isset($_GET['Distributor']['last_note']) && $_GET['Distributor']['last_note'] != '') { $criteria->having = 'MAX(n.visit_date)=\'' . $this->last_note . "'"; } </code></pre> <p>I hate using the request variables in the model like this, but there isn't much else I could do.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload