CODEWITHSUNDEEP
phpyii
ajaybc
ajaybc

Reputation: 4059

Yii AR join not working when using findAll

I have two tables menus and lang_menus. My Menus model is as follows :

public function relations()
{
    // NOTE: you may need to adjust the relation name and the related
    // class name for the relations automatically generated below.
    return array(
                'menulanguages'=>array(self::HAS_MANY, 'MenuLangs', 'menuId'),
    );
}
...
public function getMenus(){
        $criteria = new CDbCriteria();
        $criteria->condition = "t.clientId = ".Yii::app()->user->clientId." AND menulanguages.languageId = ".Yii::app()->user->userlanguage;


        $count = Menus::model()->with('menulanguages')->count($criteria);

        $pages=new CPagination($count);
        //Results per page
        $pages->pageSize=10;
        $pages->applyLimit($criteria);

        $menus = Menus::model()->with('menulanguages')->findAll($criteria);
        return array('menus' => $menus, 'paging' => $pages);
    }

This is throwing the error Unknown column 'menulanguages.languageId'. The error is in the line $menus = Menus::model()->with('menulanguages')->findAll($criteria);.

Surprisingly I am getting the value of the variable $count correctly.

On looking at the log I can see that the SQL query that is running for the findAll query is :

SELECT `t`.`id` AS `t0_c0`, `t`.`clientId` AS `t0_c1`, `t`.`restaurantId` AS `t0_c2` FROM `posif_menus` `t` WHERE (t.clientId = 1 AND menulanguages.languageId = 2) LIMIT 10

which means the join has not taken place. Whereas proper join query is running for the count value. Am I doing something wrong ? Please help.

Upvotes: 2

Views: 5294

Answers (2)

Owais Iqbal
Owais Iqbal

Reputation: 549

Try this.. apply joining table in cdbcriteria..

public function getMenus(){
        $criteria = new CDbCriteria();
        $criteria->condition = "t.clientId = ".Yii::app()->user->clientId." AND                                               menulanguages.languageId = ".Yii::app()->user->userlanguage;
        $criteria->with = array('menulanguages');

        $count = Menus::model()->count($criteria);

        $pages=new CPagination($count);
        //Results per page
        $pages->pageSize=10;
        $pages->applyLimit($criteria);

        $menus = Menus::model()->findAll($criteria);
        return array('menus' => $menus, 'paging' => $pages);
    }

Upvotes: 2

bool.dev
bool.dev

Reputation: 17478

Just use together() of CActiveRecord or together property of CDbCriteria:

$menus = Menus::model()->with('menulanguages')->together()->findAll($criteria);

or:

$criteria = new CDbCriteria();
$criteria->condition = "t.clientId = ".Yii::app()->user->clientId." AND menulanguages.languageId = ".Yii::app()->user->userlanguage;
$criteria->together=true;

Upvotes: 5

Related Questions