Activerecord And Two Tables

Yii 1.1 General Discussion
1

Here is a problem, this SQL query i need to transform into AR, but i don’t know how:

SELECT movies_categories., (SELECT COUNT() FROM movies WHERE FIND_IN_SET(movies_categories.id, movies.category_id)) AS count FROM movies_categories ORDER BY movies_categories.title

I tried to do this, but it doesn’t works:

	$criteria = new CDbCriteria();


	$criteria->select = '*, (SELECT COUNT(*) FROM movies WHERE FIND_IN_SET(movies_categories.id, movies.category_id)) AS count';


	$criteria->order = 'id DESC'; 


	$result = Movies::model()->findAll($criteria);


	$dataProvider = new CArrayDataProvider($result);
2

Assuming that the SQL above works fine, then I think it should be translated into this:




	$criteria = new CDbCriteria();

	$criteria->select = '*, (SELECT COUNT(*) FROM movies WHERE FIND_IN_SET(`t`.id, movies.category_id)) AS count';  /* #1 */

	$criteria->order = 'title';  /* #2 */

	$result = MovieCategories::model()->findAll($criteria); /* #3 */

	$dataProvider = new CArrayDataProvider($result);

#1 … Yii uses t for the alias of the main table.

#2 … The order should be t.title

#3 … MovieCategories instead of Movies … this should be a critical point. :)

3

Thanks, but i have this: Property "MoviesCategories.count" is not defined.

4

Add a public $count attribute to your MoviesCategories class to hold the value.




class MoviesCategories extends CActiveRecord

{

    public $count; // Here


    ...

}