counting within a relational hasMany query

Yii 2.0 General Discussions
1

i have a Tag table along with an ItemTag junction table. the item model contains the relation to get the tags for an item as follows:




$this->hasMany( Tag::className(), ['id' => 'tag_id'] )

            ->viaTable( ItemTag::tableName(), ['item_id' => 'id'] )

            ->orderBy( ['tag.id' => SORT_ASC] );

however what i really want is to order the tags by popularity which requires a count of the total number of items with a tag:




SELECT count(*) from ItemTag where tag_id=x

how can i add this sub query into my relational query so that the relation always returns tags in order of popularity? is it possible to use sub queries in a hasMany query or must i add the popularity subquery to the query in the parent context where i am finding items? bear in mind that i need to use each tag_id from the hasMany list in order to query that tag’s popularity. perhaps doing some kind of fancy join within the hasMany query? i’m not sure but i’ve tried a number of things and none of them have worked.

2 



$popularityQuery = (new \yii\db\Query())

            ->select( ['COUNT(*) AS popularity', 'tag_id'] )

            ->from( 'item_tag' )

            ->groupBy( 'tag_id' );


        return $this->hasMany( Tag::className(), ['id' => 'tag_id'] )

            ->viaTable( ItemTag::tableName(), ['item_id' => 'id'] )

            ->leftJoin( ['totals' => $popularityQuery], 'totals.tag_id = tag.id' )

            ->orderBy( 'popularity DESC' );