Count by a 'table1' field conditioned by a 'table2' field

I have 2 tables:

Person (person data)

Log (stores logs changes on Person table)

I want to count persons by genre, registered on some periode

Here is my code:

$criteria=new CDbCriteria;

$criteria->select='person.genre, count(*) as qty';


$criteria->condition = 'log.time <= now()'; //example

$result = person::model()->with('log') ->together()->findAll($criteria)

Error: not a GROUP BY expression

Problem is:

My condition involves one ‘Log’ field, I have to join it. But Yii tries to bring back fields from ‘Log’, which causes the SQL COUNT to fail.

I can use ‘join’ property from CDbCriteria, but I’m avoiding it because my relation with ‘LOG’ is a bit complex and I want to reuse the relation I defined on ‘Person’.


Thanks is advance

You can set the select for the related table, maybe it helps:

$result = person::model()->with(array('log'=>array('select'=>'id'))) ->together()->findAll()

Thanks for your suggestion, but it either don’t work.

AR tries also to bring back the fields and This breaks my GROUP BY expression

Why dont you create a relation of type self::STAT called personCount and call:


Try and see

Nops. Field not found!

Yii actually is not joining the related table defined by the STAT relation :confused:

Note: I’m using 1.0.12 version.

After thinking a bit, I reached a conclusion: What I’m trying to do does not make so much sense.

When I use ‘with’, I am trying to retrieve a related table with its results, which is not the case. I just want to JOIN to filter my results based on a field from a second table.

So, I actually resolved it with ‘JOIN’ attribute from CDbCriteria.

This was not best solution, as I had to write AGAIN the JOIN condition.

The best solution would be if JOIN property could REUSE a predefined AR Relation.

I see… it probably was looking for the field that personCount is related to, or maybe for Log…