relations cannot be linked via multiple tables

[jpodpro]

my understanding of a relational database is that there can be many kinds of relations, yet yii only supports single table relations. i find this extremely limiting.

for example, i have tags that can be assigned to an item via an ItemTag table and also via a CustomTag table. in my item, i want to define a single getTags() relation that gets all the tags assigned via both tables and sorts them on a condition. as far as i can tell yii is unable to perform such a task inherently. i would have to get both tag lists separately and sort them manually in php.

$popularityQuery = (new \yii\db\Query())
    ->select( ['COUNT(*) AS popularity', 'tag_id'] )
    ->from( 'item_tag' )
    ->groupBy( 'tag_id' );

$this->hasMany( Tag::className(), ['id' => 'tag_id'] )
    ->viaTable( ItemTag::tableName(), ['item_id' => 'id'] )
    ->leftJoin( ['totals' => $popularityQuery], 'totals.tag_id = tag.id' )
    ->orderBy( 'popularity DESC' );

$this->hasMany( Tag::className(), ['id' => 'tag_id'] )
    ->viaTable( CustomTag::tableName(), ['item_id' => 'id'] )
    ->leftJoin( ['totals' => $popularityQuery], 'totals.tag_id = tag.id' )
    ->orderBy( 'popularity DESC' );

[cebe]

so you basically want a relation against a virtual table you create via join? Could you explain a bit more, how these relations are expected to work?

Please add the complete relation definition including function name and show some code that describes how you want to use these relations, i.e. calling some query methods, which output you expect.

[jpodpro]

it would be great if you could just add multiple viaTable calls:

$this->hasMany( Tag::className(), ['id' => 'tag_id'] )
    ->viaTable( ItemTag::tableName(), ['item_id' => 'id'] )
    ->viaTable( TagSuggestion::tableName(), ['item_id' => 'id'] )
    ->leftJoin( ['totals' => $popularityQuery], 'totals.tag_id = tag.id' )
    ->orderBy( 'popularity DESC' );

here is the SQL query i made that does what i want. note that this query adds tags to and removes tags from the basic ItemTag relation list - it is probably more specific than you want.

SELECT `tag`.`name`, `tag`.`id`, IFNULL( `totals`.`popularity`, 0 ) AS `popularity`
                FROM `tag`
                LEFT JOIN
                (
                    SELECT `tag`.`id` AS `id`
                    FROM `item_tag`
                    LEFT JOIN `tag` ON `tag`.`id` = `item_tag`.`tag_id`
                    WHERE `item_type`=:item_type
                    AND `item_id`=:item_id
                UNION
                    SELECT `tag_id` AS `id`
                    FROM `tag_suggestion`
                    WHERE `item_type`=:item_type
                    AND `item_id`=:item_id
                    AND `user_id`=:user_id
                    AND `action`=:tag_add
                ) `alltags` ON `tag`.`id` = `alltags`.`id`
                LEFT JOIN `tag_suggestion`
                    ON `tag`.`id` = `tag_suggestion`.`tag_id`
                    AND `tag_suggestion`.`item_type`=:item_type
                    AND `tag_suggestion`.`item_id`=:item_id
                    AND `tag_suggestion`.`user_id`=:user_id
                    AND `tag_suggestion`.`action`=:tag_remove
                LEFT JOIN
                (
                    SELECT COUNT(*) AS `popularity`, `tag_id`
                    FROM `item_tag`
                    GROUP BY `tag_id`
                ) `totals` ON `tag`.`id` = `totals`.`tag_id`
                WHERE `alltags`.`id` IS NOT NULL
                AND `tag_suggestion`.`action` IS NULL
                ORDER BY `popularity` DESC

essentially i would like to modify what is returned in the relation based on other information.

[jpodpro]

i don't know if what i am asking for is too rare a case - perhaps it is. but it's very hard to figure out when yii is able to handle my requirements or not.

[cebe]

it would be great if you could just add multiple viaTable calls:

if you need relation via multiple tables you have to use via() instead of viaTable(). viaTable() is for convenience to specify a many to many relation more easily.

[jpodpro]

but i need the relations from BOTH tables together!

[jpodpro]

to be clear - i want ALL the tags via the ItemTag table and ALL the tags via the TagSuggestion table. the two via tables are independent of each other.

[cebe]

this is not possible in Yii active record. it represents only relations to one table, which is the equivalent of a foreign key in the database. What you could do to solve your case is to create an SQL VIEW and define a relation to it.

[jpodpro]

maybe i could propose adding an optional callable function to the hasMany method that would provide the list of records returned by the hasmany query. this list could be modified and returned, allowing the relation results to be customized based on specific needs:

return $this->hasMany( Tag::className(), ['id' => 'tag_id'], function( $queryResponseArray )
{
     if( $queryResponseArray[0]->tag_name == 'cebe' )
           unset( $queryResponseArray[0] );

     $queryResponseArray[] = new Tag( 'jpod' );
} );

i'm struggling with relations not being flexible enough to return the data i need, but really want to use them to eager-load my data.

currently i'm looking at building a custom query to get the related data in an eager-like way (using WHERE id in ( x, y, z ... ) ) and then building the return object as is done inherently in active relation logic. could you tell me if it's possible to get a list of records manually and have Yii populate the relation based on my query?

EDIT: looks like buildBuckets is private, and populateRelation requires a relation name. i would love access to the bucket-building functionality without having to use the relation system that doesn't quite fulfill what i need.

[cebe]

@GlyphDataServices there is a bug in Yii 2 that prevents this right now. Needs to be fixed.

[lowap]

I would also like to use viaTable() multiple times. I had difficulties using via() with giiant, that's why I would prefer viaTable().

[SOHELAHMED7]

@cebe

@GlyphDataServices there is a bug in Yii 2 that prevents this right now. Needs to be fixed.

Can you provide link to this issue? I would like to have more info about it.

[cebe]

Can you provide link to this issue? I would like to have more info about it.

#5341

[lowap]

if you need relation via multiple tables you have to use via() instead of viaTable(). viaTable() is for convenience to specify a many to many relation more easily.

Would be nice to mention in the docs also. Thanks in advance!

[cebe]

Would be nice to mention in the docs also. Thanks in advance!

done. c4b3e102dbc35b0db5164ecc104a675f794b4a5b

[rugabarbo]

As far as I understand, this is a feature request: https://github.com/yiisoft/yii2/issues/10408#issuecomment-165334623 – this isn't a bug. It's a request for flexibility of hasMany() function.

IMHO, it must be transferred to https://github.com/yiisoft/active-record as an enhancement. Branch 2.0.x is closed for enhancements.