Ndtyjky

There are 3 Models with relation between themselves.
Example

class A extends ActiveRecord

{

    public static function tableName(){

        return 'tbl_a';

    }

    public function getB()

    {

        return $this->hasOne(B::className(), ['column' => 'column']);

    }

}



class B extends ActiveRecord

{

    public static function tableName(){

        return 'tbl_b';

    }

    public function getC()

    {

        return $this->hasOne(C::className(), ['column' => 'column']);

    }

}

I have next code:

$result = A::find()->joinWith('b')->where('');

if () {

    A->joinWith('b.c')->where('');

}

$result->createCommand()->rawSql;

And outcome I have next sql:

select * from tbl_a left join tbl_b on ... join tbl_b on ... join tbl_c on ... where ...

As you can see sql query duplicates table relation 'tbl_b'. Do you know why?

UPDATE

OK, I researched my problem in more detail.
Table connection is duplicated if used different JOIN types.
Next original models:

class Myuser extends ActiveRecord

{

    public static function tableName(){

        return 'myuser';

    }



    public function getProfile()

    {

        return $this->hasOne(Profile::className(), ['user_id' => 'id']);

    }

}



class Profile extends yiidbActiveRecord {



    public static function tableName(){

        return 'profile';

    }



    public function getCity()

    {

        return $this->hasOne(City::className(), ['id'=>'city_id']);

    }

}

Executed code:

$get_city = 1;

$u = Myuser::find()->joinWith('profile', 0, 'INNER JOIN');

if ($get_city) {

    $u->joinWith('profile.city');

}

echo $u->createCommand()->rawSql;

Result:

SELECT `myuser`.* FROM `myuser` 

    INNER JOIN `profile` ON `myuser`.`id` = `profile`.`user_id`

    LEFT JOIN `profile` ON `myuser`.`id` = `profile`.`user_id`

    LEFT JOIN `city` ON `profile`.`city_id` = `city`.`id`

How can I avoid repetition, if I need to get a unique table 'profile' and add fields from the table 'city'. If there are no the fields in table 'city', then value should be 'null'.

I guess it's kind a bug (or feature) in the framework. When you use relation profile.city framework doosn't see relation already joined by inner join... I assume that if you use left join for first relation, everything will work fine.

In your case try to use leftJoin() and specify the table name to join:

$get_city = 1;

$u = Myuser::find()->innerJoinWith('profile');

if ($get_city) {

    $u->leftJoin('city', 'profile.city_id = city.id');

}

echo $u->createCommand()->rawSql;

This is because you're using different join type for these two joins. joinWith('profile', false, 'INNER JOIN') and joinWith('profile') will generate different JOIN (joinWith() uses LEFT JOIN as join type by default), so you have 2 joins you query. If you want to avoid duplicates, you may use the same settings for these two joinWith() calls:

$get_city = 1;

$u = Myuser::find()->joinWith('profile', false, 'INNER JOIN');

if ($get_city) {

    $u->joinWith('profile.city', false, 'INNER JOIN');

}

echo $u->createCommand()->rawSql;

Result:

SELECT `myuser`.* FROM `myuser` INNER JOIN `profile` ON `myuser`.`id` = `profile`.`user_id` INNER JOIN `city` ON `profile`.`city_id` = `city`.`id`

If you want to combine INNER JOIN and LEFT JOIN, you may use extended syntax:

$get_city = 1;

if ($get_city) {

    $u = Myuser::find()->joinWith(

        [

            'profile' => function (ActiveQuery $query) {

                $query->joinWith('city');

            },

        ],

        false,

        'INNER JOIN'

    );

} else {

    $u = Myuser::find()->joinWith('profile', false, 'INNER JOIN');

}

echo $u->createCommand()->rawSql;

Result:

SELECT `myuser`.* FROM `myuser` INNER JOIN `profile` ON `myuser`.`id` = `profile`.`user_id` LEFT JOIN `city` ON `profile`.`city_id` = `city`.`id`