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