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->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
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 Person.id and Log.id. 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
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…