Yii Framework: Using CDbCriteria to find all records ordered and filtered by the values of aggregate functions
Yii framework is a wonderful PHP framework. It has its own build in Active Record (AR) that can help you a lot with the CRUD (create, read, update and delete) operations. CDbCriteria is used to apply conditions, ordering, and limits on the find and findAll methods or the AR.
I presume you are familiar with Yii and its Active Record implementation and want to use CDbCriteria to fetch a set of records together with results of aggregate functions, like SUM(), on child tables.
Consider the following database structure:
invoicetable that has childreninvoice_itemand childrenpayment.invoice_itemtable keeps the line items of the invoice and each line item has a price (amount).paymenttable keeps all the payments made for the invoice. Each payment has an amount.
This is a simple structure that illustrates our scenario and the use of aggregate functions on child rows.

Let’s say you want the following:
Get all the invoices with payments and for each invoice, you want the total amount and the total payments amount.
The code will look like this:
Line 7: $criteria->select initialized as Array, not as String
The select property can be either string or array. In this case, it won’t work as a string because, internally, the string will be split by comma (,) character trying to delimit individual columns. You have commas in the SQL that doesn’t delimit columns (coalesce(sum(ii.amount), 0)). This will generate errors.
Line 9 & 10: The ‘AS’ keyword
AS keyword, to define an alias is optional in many DBMS. While the generated SQL would work fine, you need to use it here. If you don’t use the AS keyword to define the alias, Yii won’t be able to parse this code. Internally, it uses a regular expression that expects the AS keyword to be used.
Line 9 & 10: Don’t use SQL keywords as aliases because you cannot escape them
The same as above, we are limited by the regular expression used by Yii internally. The regular expression is:
/^(.*?)\s+AS\s+(\w+)$/imThe part after AS is \s+(\w+). This means 1 or more spaces followed by 1 or more word characters (alphanumeric & underscore). Escape characters, like back ticks (`), do not match \w expression and Yii won’t be able to detect the alias.
Line 16: The HAVING clause
This is SQL specific. If you want to use a condition using the alias that you used in the select, then you need to place it in the HAVING clause and not in the WHERE clause.
