[Question] How to order by record count in MANY_MANY relations?

Archives Yii 1.0.x Discussions
1

Hello everybody.

I am trying to get a record listing using Table1::model()->findAll() ordered by the record count of a MANY_MANY related table. Let me explain further.

My schema has three tables with TableMM being the table that holds the many to may relationship between Table1 and Table2:

Table1 {id,field1,field2 etc…}

Table2 {id,field1,field2 etc…}

TableMM {table1_id, table2_id}

I want to get all records of Table1 ordered by the number of records of Table2 each one references. Please note that the reference is held only in the table "TableMM".

Is there any way to get there using the Relational Active Record? I have already defined a Statistical Query in each one of the models and it works fine, but I can’t find a way to sort the records using it. Something like Table1::model()->findAll($criteria,array(‘order’=>‘table2Count DESC’)); would be great! Any suggestions would be appreciated.

Thanks in advance.

2

Unfortunately, you will need to write plain SQLs for this kind of queries. AR is not very suitable to deal with aggregational queries.

3

Thanks a lot for your answer qiang. It's good to know that doing it manually is the right way.