Using a joinwith to two different relations on the same table causes alias error

[Sammaye]

I have an order object with two relations like so:

    public function getUser()
    {
        return $this->hasOne('common\models\User', ['id' => 'user_id']);
    }

    public function getPartner()
    {
        return $this->hasOne('common\models\User', ['ns_partner_id' => 'partner_id']);
    }

when I do:

<?php foreach($query->joinWith(['partner', 'user'])->each() as $order){ ?>

I get:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'tbl_user'

Is this expected?

[Sammaye]

This seems related: https://github.com/yiisoft/yii2/issues/1791

[cebe]

@Sammaye please show the SQL that produces the error and make sure you are using the latest code of yii (not the beta, see https://github.com/yiisoft/yii2/issues/5060#issuecomment-55920352).

[Sammaye]

Just to let you know so that you don't think I have ran away, I am still working on getting your comment to work.

It does seem to work now after adding the asset paths into my composer.json for the bower deps (though I do get an annoying formatting bug in the console but that is isolated to composer, specifically the npm plugin for believe), but I will need to do more testing before I can respond.

[Sammaye]

Yes, confirmed as still broken with very latest of Yii2.

[Sammaye]

Sorry, forgot the error, this is the full raw error with the SQL:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'tbl_user'
The SQL being executed was: 
SELECT `tbl_order`.* FROM `tbl_order` LEFT JOIN `tbl_ad` ON `tbl_order`.`ad_id` = `tbl_ad`.`id` 
LEFT JOIN `tbl_user` ON `tbl_order`.`user_id` = `tbl_user`.`id` 
LEFT JOIN `tbl_user` ON `tbl_order`.`partner_id` = `tbl_user`.`ns_partner_id` 
LEFT JOIN `tbl_country` ON `tbl_order`.`ship_country` = `tbl_country`.`enumeration` 
WHERE (`tbl_order`.payment_type IN ('invoice', 'credit_card', 'proforma', 'quotation')) AND (((`tbl_order`.create_time >= '2014-09-14 00:00:00') AND (`tbl_order`.create_time <= '2014-09-24 23:59:59')) OR ((`tbl_order`.website_date_created >= '2014-09-14 00:00:00') AND (`tbl_order`.website_date_created <= '2014-09-24 23:59:59'))) ORDER BY `tbl_order`.id DESC

I noticed the double JOIN there

[cebe]

when you join the same table with different condition you need to add an alias manually.

[Sammaye]

That issue I linked did say something about that but it kept going backwards and forwards and I couldnt tell if it had actually been solved.

[cebe]

here is an example on how to specify the alias for the joinWith() query: https://github.com/yiisoft/yii2/commit/795a09c27afc779bd8315654a8614b1433994178#diff-a92d24117b719946a9dfbc1a0b698773R281

[CedricYii]

With the new alias possibilities introduced in yii2.0.7, you could use the following simple syntax:

<?php 
foreach( $query->joinWith(['partner p', 'user u'])->each() as $order ) {
...
}
?>

This should work well with the simple relations described in your first post.

(But maybe you are expected Yii to detect this situation, and handle it automatically for you? Which may be done by assigning the relation's name as default alias instead of keeping tableName.)

[cebe]

related to #7263

[leandrogehlen]

@CedricYii, really this works, but i think that query must works without to force explict alias. It's necessary,by default, join always use relation name as alias

[bizley]

Closing due to inactivity. If this should be reopen please write here.