Yii Framework: Using CDbCriteria to find all records ordered and filtered by the values of aggregate functions

Horatiu Cristea
Aug 28, 2017 · 2 min read

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:

  1. invoice table that has children invoice_item and children payment.
  2. invoice_item table keeps the line items of the invoice and each line item has a price (amount).
  3. payment table 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+)$/im

The 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.

)

Horatiu Cristea

Written by

Entrepreneur and Software Engineer

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade