Avoiding Duplicate Records When Using Yii's With/Together Combo

Came across a situation in which a query with relations returns duplicate records. First of all, for those of you just getting started, you can retrieve a model's relations with the 'with' method. For example, say you have a model, Person, and Person has a relation, CellPhone. If you want to get Person's CellPhone, you can use with() to retrieve the related record(s) when you query for Person. For example, assuming you've created a relation for the Person model called 'cellPhone':

$model = Person::model()->with('cellPhone')->findAll()

This allows you to do things like this:

echo $model->first_name; // prints the first name
echo $model->cellPhone->os_type // prints information about the cell phone, like 'Android'

The issue, here, however, is that Yii will actually do two queries, one to get the Person and one to get the CellPhone. To get everything in one query (more efficient), use "together" like so:

$model=Person::model()->with(array('cellPhone'=>array('together'=>true)))->findAll()

The syntax is a little different, since I'm including a parameter, here, for the query, namely together. (You could do this with a CDbCriteria, but we'll save that for another time.) The main point is that this will execute the select query once to get the Person and any related CellPhone records.

Now that we've covered that, the gotcha I wanted to mention, here, is that, if your Person has many CellPhones, you're likely to end up with duplicate records when you perform the together version of the query. The solution I found was to use a 'group by' clause in my query, grouping by the primary key for Person. You could do something like this;

$model=Person::model()->with(array('cellPhone'=>array('together'=>true)))->findAll(array('group'=>'t.id'))

where the 't' in 't.id' refers to the main table being queried (e.g., the table for Person). For all queries, the main table will use the alias 't' and you can use 't' in your queries for disambiguation.

I hope this gives you some idea of the solution. There is probably a better one, but this one works for me, and it makes sense. You'll especially want to watch this if you're going to be paging through your records, because the duplicates will throw off the total count returned from the query, but Yii will only show the distinct rows! So, you end up with something like "Showing 2 through 12 of 38," which is weird enough, but even worse, you don't actually get to page through 38 records, because many of them are duplicates!

By the way, I don't actually use the syntax above to pass in the query criteria. I'll cover CDbCriteria in a later post.