Yii2: sort a relational count column in GridView

I’m having hard time to sort by the ‘topicCount’ which is defined as a relational getter on a model ‘Tag’.
A Topic can have a lots of Tag, and wish to sort the Tags by how many Topics containing that Tag.

In my models/Tag.php:

public function getTopicCount()
{
    return TopicTag::find()->where(['tag_id' => $this->id])->count();
}

And in my views/tag/index.php:

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'columns' => [
        'id',
        'name',
        [
             'attribute'=>'topicCount',
             'value' => 'topicCount',
        ],
        'created_at',

        ['class' => 'yiigridActionColumn','template' => '{view}',],
    ],
]); ?>

And in my controllers/TagController.php:

public function actionIndex()
{
    $dataProvider = new ActiveDataProvider([
        'query' => Tag::find(),
        'sort'=> [
            'defaultOrder' => ['id'=>SORT_DESC],
            'attributes' => ['id','topicCount'],
        ],
        'pagination' => [
            'pageSize' => 100,
        ],
    ]);

    return $this->render('index', [
        'dataProvider' => $dataProvider,
    ]);
}

And in my models/TagSearch.php:

namespace commonmodels;

use Yii;

/**
* This is the model class for table “tags”.
*
* @property integer $id
* @property string $name
* @property string $created_at
* @property string $updated_at
*/
class TagSearch extends Tag
{

public $topicCount;

/**
 * @inheritdoc
 */
public function rules()
{
    return [
        [['topicCount'], 'safe']
    ];
}

public function search($params)
{
    // create ActiveQuery
    $query = Tag::find();
    // Important: lets join the query with our previously mentioned relations
    // I do not make any other configuration like aliases or whatever, feel free
    // to investigate that your self
    $query->joinWith(['topicCount']);

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    // Important: here is how we set up the sorting
    // The key is the attribute name on our "TourSearch" instance
    $dataProvider->sort->attributes['topicCount'] = [
        // The tables are the ones our relation are configured to
        // in my case they are prefixed with "tbl_"
        'asc' => ['topicCount' => SORT_ASC],
        'desc' => ['topicCount' => SORT_DESC],
    ];

    // No search? Then return data Provider
    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }
    // We have to do some search... Lets do some magic
    $query->andFilterWhere([
        //... other searched attributes here
    ])
    // Here we search the attributes of our relations using our previously configured
    // ones in "TourSearch"
    ->andFilterWhere(['=', 'topicCount', $this->topicCount]);

    return $dataProvider;
}

}

And in the index view I can see the correct topicCount:

enter image description here

but on clicking the topicCount column I get the error:

exception 'PDOException' with message 'SQLSTATE[42703]: Undefined column: 7 ERROR: column "topicCount" does not exist
LINE 1: SELECT * FROM "tags" ORDER BY "topicCount" LIMIT 100

Thanks for any guidance..!


Source: php

1 Comment

  1. Anatoliy_Greenice

    Hi!
    models/TagSearch.php:

    Add:

    public function attributes()
    {
    // add related fields to searchable attributes
    return array_merge(parent::attributes(), [‘topicCount’]);
    }

    Add in search():
    $query =Tag::find()->joinWith([‘topic_tags’])->select(‘`tags`.*, COUNT(topic_tags.id) as topicCount’)->groupBy(‘topic_tags.id’);

    Reply

Leave a Reply