Yii Framework Forum: Problem with table aliases when using multiple eager joins to same relation - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

Problem with table aliases when using multiple eager joins to same relation SQL error "Not unique table/alias" when using relation multipl Rate Topic: -----

#1 User is offline   zilchman 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 18-November 10

Posted 17 July 2012 - 09:31 AM

Hello,

For performance reasons I am trying to get my data query to be preformed with a single select on my database.

I have tables of the following structure:

media
------
Id (PK)
Title

artist
--------
Id (PK)
LastName

artist_artisttype
------------------
ID (PK)
Type

media_artist
------------
Media_ID (PK)
Artist_ID (PK)
ArtistType_ID (PK)

with the following relations in their corresponding Yii model classes:

Media.php
----------
public function relations()
{
return array(
'actors3'=>array(
self::MANY_MANY,
'Artist',
'media_artist(Media_Id, Artist_Id)',
'with'=>'types',
'condition'=>"types.Type = '".ARTISTTYPE_ACTOR."'",
'index'=>'Id',
),
'directors3'=>array(
self::MANY_MANY,
'Artist',
'media_artist(Media_Id, Artist_Id)',
'with'=>'types',
'condition'=>"types.Type = '".ARTISTTYPE_DIRECTOR."'",
'index'=>'Id',
),
);
}

Artist.php
----------
public function relations()
{
return array(
'types'=>array(
self::MANY_MANY,
'ArtistType',
'media_artist(Artist_Id,Media_Id,ArtistType_Id)',
),
);
}

When I execute either of the following code:
$media = Media::model()->with('actors3')->findAllByPK(200);
or
$media = Media::model()->with('directors3')->findAllByPK(200);
and then
echo sizeof($media[0]->actors3).'<br />';
echo sizeof($media[0]->directors3).'<br />';
I get the expected results but the data is retrieved from the database in two select statements because one of the relations is not being eagerly loaded.

However, if I try to eagerly load both relations, actors3 and directors3, as in the following:

$media = Media::model()->with('actors3', 'directors3')->findAllByPK(200);

the following SQL error occurs:

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

Looking at the Yii generated SQL select code (below) I see that for both Artists.types relation two LEFT OUTER JOIN clauses use the same alias 'types_types' when joining the media_artist table, first for 'actors3' and then 'directors3', and also two LEFT OUTER JOIN clauses use the same alias 'types' when joining the artisttype table first for 'actors3' and then 'directors3'.

SELECT *
FROM `media` `t`
LEFT OUTER JOIN `media_artist` `actors3_actors3` ON (`t`.`Id`=`actors3_actors3`.`Media_Id`)
LEFT OUTER JOIN `artist` `actors3` ON (`actors3`.`Id`=`actors3_actors3`.`Artist_Id`)
LEFT OUTER JOIN `media_artist` `types_types` ON (`actors3`.`Id`=`types_types`.`Artist_Id`)
LEFT OUTER JOIN `artisttype` `types` ON (`types`.`Id`=`types_types`.`ArtistType_Id`)
LEFT OUTER JOIN `media_artist` `directors3_directors3` ON (`t`.`Id`=`directors3_directors3`.`Media_Id`)
LEFT OUTER JOIN `artist` `directors3` ON (`directors3`.`Id`=`directors3_directors3`.`Artist_Id`)
LEFT OUTER JOIN `media_artist` `types_types` ON (`directors3`.`Id`=`types_types`.`Artist_Id`)
LEFT OUTER JOIN `artisttype` `types` ON (`types`.`Id`=`types_types`.`ArtistType_Id`)
WHERE (`t`.`Id`=200) AND (types.Type = 'Actor') AND (types.Type = 'Director');

Question: Is there a way to modify the relations such that the aliases used in the select are unique?

If I modify the Yii generated select above to the following I get the desired result:

select *
FROM `media` `t`
LEFT OUTER JOIN `media_artist` `actors3_actors3` ON (`t`.`Id`=`actors3_actors3`.`Media_Id`)
LEFT OUTER JOIN `artist` `actors3` ON (`actors3`.`Id`=`actors3_actors3`.`Artist_Id`)
LEFT OUTER JOIN `media_artist` `actors3_types_types` ON (`actors3`.`Id`=`actors3_types_types`.`Artist_Id`)
LEFT OUTER JOIN `artisttype` `actors3_types` ON (`actors3_types`.`Id`=`actors3_types_types`.`ArtistType_Id`)
LEFT OUTER JOIN `media_artist` `directors3_directors3` ON (`t`.`Id`=`directors3_directors3`.`Media_Id`)
LEFT OUTER JOIN `artist` `directors3` ON (`directors3`.`Id`=`directors3_directors3`.`Artist_Id`)
LEFT OUTER JOIN `media_artist` `directors3_types_types` ON (`directors3`.`Id`=`directors3_types_types`.`Artist_Id`)
LEFT OUTER JOIN `artisttype` `directors3_types` ON (`directors3_types`.`Id`=`directors3_types_types`.`ArtistType_Id`)
WHERE (`t`.`Id`=200) AND (actors3_types.Type = 'Actor') AND (directors3_types.Type = 'Director');

Thanks,
-Tom
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users