php - Yii2: sort a relational count column in GridView -

[edited 2]

i'm having hard time sort 'topiccount' defined relational getter on model 'tag'. topic can have lots of tag, , wish sort tags how many topics containing tag.

in models/tag.php:

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

and in views/tag/index.php:

<?= gridview::widget([     'dataprovider' => $dataprovider,     'columns' => [         'id',         'name',         [              'attribute'=>'topiccount',              'value' => 'topiccount',         ],         'created_at',          ['class' => 'yii\grid\actioncolumn','template' => '{view}',],     ], ]); ?> 

and in 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 models/tagsearch.php:

<?php  namespace common\models;  use yii;  /**  * model class 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();     $query->joinwith(['topiccount']);      $dataprovider = new activedataprovider([         'query' => $query,     ]);      $dataprovider->sort->attributes['topiccount'] = [         'asc' => ['topiccount' => sort_asc],         'desc' => ['topiccount' => sort_desc],     ];      if (!($this->load($params) && $this->validate())) {         return $dataprovider;     }      $query->andfilterwhere([         //... other searched attributes here     ])     ->andfilterwhere(['=', 'topiccount', $this->topiccount]);      return $dataprovider; }   } 

and in index view can see correct topiccount:

but on clicking topiccount column error:

exception 'pdoexception' message 'sqlstate[42703]: undefined column: 7 error: column "topiccount" not exist line 1: select * "tags" order "topiccount" limit 100

thanks guidance..!

---

[edit]

following lucas' advice, i've set dataprovider query in $dataprovider this:

'query' => $query->select(['tags.*','(select count(topic_tags.id) topic_tags topic_tags.tag_id=tags.id) topiccount'])->groupby('tags.id'), 

and got error:

exception 'pdoexception' message 'sqlstate[42p01]: undefined table: 7 error: missing from-clause entry table "tags"

so reformulated this:

        'query' => $query->from('tags')->leftjoin('topic_tags','topic_tags.tag_id = tags.id')->select(['tags.*','(select count(topic_tags.id) topic_tags topic_tags.tag_id=tags.id) topiccount'])->groupby('tags.id'), 

and result:

apparently topiccount column not set, when try sort it, returns error:

exception 'pdoexception' message 'sqlstate[42703]: undefined column: 7 error: column "topiccount" not exist

but when try sql directly on db, works fine:

so suppose problem in way yii handles alias 'topiccount'?

---

2nd edit

still same result without topiccount set in grid view. show tagsearch model, tagcontroller , views/tag/index view file below:

tagsearch

<?php  namespace common\models;  use yii; use yii\base\model; use yii\data\activedataprovider; use common\models\tag;  /**  * tagsearch represents model behind search form `common\models\tag`.  */ class tagsearch extends tag {      public $topiccount;      /**      * @inheritdoc      */     public function rules()     {         return [             [['id', 'topiccount'], 'integer'],             [['name', 'created_at', 'updated_at', 'topiccount'], 'safe'],         ];     }      /**      * @inheritdoc      */     public function scenarios()     {         // bypass scenarios() implementation in parent class         return model::scenarios();     }      /**      * creates data provider instance search query applied      *      * @param array $params      *      * @return activedataprovider      */     public function search($params)     {         $query = tag::find();          $dataprovider = new activedataprovider([             'query' => $query->from("tags")->select(["tags.*","(select count(topic_tags.id) topic_tags topic_tags.tag_id=tags.id) topiccount"])->groupby("tags.id"),         ]);          $this->load($params);          if (!$this->validate()) {             // uncomment following line if not want return records when validation fails             $query->where('0=1');             return $dataprovider;         }          $query->andfilterwhere([             'id' => $this->id,             'topiccount' => $this->topiccount,             'created_at' => $this->created_at,             'updated_at' => $this->updated_at,         ]);          $query->andfilterwhere(['like', 'name', $this->name]);          return $dataprovider;     } } 

tag model

<?php  namespace common\models;  use yii;  /**  * model class table "tags".  *  * @property integer $id  * @property integer $topiccount  * @property string $name  * @property string $created_at  * @property string $updated_at  */ class tag extends \yii\db\activerecord {      public $topiccount;      /**      * @inheritdoc      */     public static function tablename()     {         return 'tags';     }      /**      * @inheritdoc      */     public function rules()     {         return [             [['topiccount'], 'integer'],             [['name'], 'string'],             [['created_at', 'updated_at'], 'required'],             [['created_at', 'updated_at'], 'safe']         ];     }      /**      * @inheritdoc      */     public function attributelabels()     {         return [             'id' => 'id',             'name' => 'name',             'topiccount' => 'tc',             'created_at' => 'created at',             'updated_at' => 'updated at',         ];     }  } 

tagcontroller

public function actionindex() {      $searchmodel = new tagsearch();     $mymodels = $searchmodel->search([]);      return $this->render('index', [         'dataprovider' => $mymodels,     ]); } 

tags/index

<?= gridview::widget([     'dataprovider' => $dataprovider,     'columns' => [         'id',         'name',         'topiccount',         'created_at',         'updated_at',         ['class' => 'yii\grid\actioncolumn','template' => '{view}',],     ], ]); ?> 

what missing?

so resolved following this wiki:

since in case don't use sum('amount'), changed following , works perfectly:

tag model:

public function gettopiccount()  {     return $this->hasmany(topictag::classname(), ["tag_id" => "id"])->count();  } 

tagsearch model:

    $query = tag::find();     $subquery = topictag::find()->select('tag_id, count(tag_id) topic_count')->groupby('tag_id');             $query->leftjoin(["topicsum" => $subquery], '"topicsum".tag_id = id'); 

just encountered problem generated sql:

exception 'pdoexception' message 'sqlstate[42p01]: undefined table: 7 error:  missing from-clause entry table "topicsum" 

this might postgres-specific issue, had arrange code generated sql becomes this:

select count(*) "tags"  left join (select "tag_id", count(*) topic_count "topic_tags" group "tag_id") "topicsum"  on "topicsum".tag_id = id 

note double-quotation in "topicsum".tag_id part.

hope might of using postgres on yii2.