CODEWITHSUNDEEP
sqlyii
Debashis
Debashis

Reputation: 596

Yii left join query

I want to execute the below sql query using Yii framework and need help on this.

SQL query

SELECT t.*, LP.name AS lp_name FROM `user` AS `t` LEFT JOIN `level_profiles` AS `LP` ON t.prof_i = LP.id WHERE t.bld_i IN (17)

So, i tried the below steps.

$usql = 't.bld_i IN (17)';

$criteria1 = new CDbCriteria;
$criteria1->select = 't.*, LP.*';
$criteria1->join = ' LEFT JOIN `level_profiles` AS `LP` ON t.prof_i = LP.id';
$criteria1->addCondition($usql);
$criteria1->order = 't.prof_i';

$result    =    User::model()->findAll($criteria1);

The above step is not allowing me to access the value from 'level_profiles' table.

Then, i tried to execute:

$usql = 't.bld_i IN (17)';

$result = User::model()->with('level_profiles', array(
                'level_profiles'=>array(
                    'select'=>'name',
                    'joinType'=>'LEFT JOIN',
                    'condition'=>'level_profiles.id="prof_i"',
                ),
            ))->findAll($usql);

This is returning an error 'Relation "level_profiles" is not defined in active record class "User". '

I know this could be executed using the below method. 

Yii::app()->db->createCommand('SELECT query')->queryAll();

But i dont want to use the above.

I am a beginner with Yii and tried to look into the forums. But, i am getting confused how to execute the query using "User::model()" approach .

Upvotes: 0

Views: 1099

Answers (1)

Degger
Degger

Reputation: 4313

class User extends CActiveRecord
{
    ......

    public function relations()
    {
        return array(
            'level_porfile_relation'=>array(self::BELONGS_TO, 'Level_Profiles_Modelname', 'prof_i'),

        );
    }

and your query will be:

$result = User::model()->with('level_porfile_relation')->findAll($usql);

Upvotes: 1

Related Questions