Now… this is the query that let me get the data I need to have:
SELECT t1.*, COUNT(DISTINCT t2.activity_id) as c1, COUNT(DISTINCT t3.activity_id) as c2,
COUNT(DISTINCT t4.activity_id) as c3, COUNT(DISTINCT t5.activity_id) as c4 FROM users AS t1
LEFT JOIN activities AS t2 ON (t1.user_id = t2.user_id AND t2.a_type='meeting')
LEFT JOIN activities AS t3 ON (t1.user_id = t3.user_id AND t3.a_type='todo')
LEFT JOIN activities AS t4 ON (t1.user_id = t4.user_id AND t4.is_active='meeting' AND t4.is_active = 1)
LEFT JOIN activities AS t5 ON (t1.user_id = t5.user_id AND t5.h_type='todo' AND t5.is_active = 1)
WHERE 1 GROUP BY (t1.user_id);
As I said I’d like to be able to sort them also by meeting / todo (“c1” / “c2”) and so in this situation should I go with active record or use “Yii::app()->db” with plain sql? Cause just using sql would lead me to loose the data manipulation I do in the model (for example in the afterFind method) and so on…
But I’ve also read that it is not possible to do this with active record using the “self::STAT” relation in the model cause in this case you can’t sort your results (http://code.google.com/p/yii/issues/detail?id=536)…
so… how would you do this? I’ve spent a lot of time trying to figure out what is the best way but wasn’t able to find a satisfying solution yet…
Only sharing an idea, how i sometimes do this. This is not perfect.
Create public vars for all different activity counts in your model
Create a parameterized named scope that adds your above JOINs to the critieria. Parameter could be the sort by column
// Only available for withActivities():
public $MeetingCount;
// Named scope that populates $..Count vars
public function withActivities($orderby=null)
{
$criteria=$this->getDbCriteria();
$criteria->select=array('*','COUNT(DISTINCT a1.activityID) AS MeetingCount', ...);
$criteria->join="LEFT JOIN activities AS a1 ON (t.user_id = a1.user_id AND a1.a_type='meeting') ....";
if ($orderby!==null)
$criteria->order=$orderby;
return $this;
}
Like that is possible to sort and to do the paging easily. The only problem I had is that, if later I add a having clause, like "having c3 >2" for select only people with at least 2 activity, the count instruction is mistaken.
thanks for your answer… I was asking this also cause from the documentation it states:
But I guess the one you suggested is the best compromise so you can get advantage of data validation and manipulation from your model… Cause else you’d have to manually manipulate your data each time you query your db for reading or validate your data (but for this you can use the bindParam method) each time you execute a query.
I’d like to hear from Qiang or other members of the Yii Dev. team about this
…also… I don’t know if it is possible (or even if it would be actually useful / makes sense) but if I got it right, it would be nice to be able to “map” fields that you use in your query using Yii DAO to the corresponding model(s) to perform data manipulation.
For example the $command->read() method could then call the afterFind method of the model
Let me know what you think.
@zaccaria: no I didn’t had that problem yet but never found myself in that situation using yii.
@Mike or @Zaccaria, Does it have to be a parameterized named scope or can you simply define the select and join properties of a criteria object wherever you use it?
Does using it as a named scope merge it in a special way? what if $criteria->mergeWith($originalCriteria) was use?
The reason I ask is cuz I’m obviously having a hard time producing a listView that is sortable by a related model’s field.
Short answer: No named scope required, that’s just for convenience. And no, nothing special with the merge of criterias in a named scope, you can do that “manually”, too.
