[zilchman]

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