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

Archives Yii 1.0.x Discussions
1

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->group='person.genre';

$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’.

Ideas?

Thanks is advance

2

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




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

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

AR tries also to bring back the fields Person.id and Log.id. This breaks my GROUP BY expression

4

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

Person::model()->with(’personCount’,’log’)->findAll($criteriaForPersonOnly);

Try and see

5

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.

6

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