dcc dcc - 7 months ago 7
PHP Question

Yii2: sort a relational count column in GridView

[EDITED 2]

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' => 'yii\grid\ActionColumn','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:

<?php

namespace common\models;

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();
$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 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..!




[EDIT]



Following Lucas' advice, I've set my dataProvider query in my $dataProvider like this:

'query' => $query->select(['tags.*','(select count(topic_tags.id) from topic_tags where topic_tags.tag_id=tags.id) topicCount'])->groupBy('tags.id'),


and I got error:

exception 'PDOException' with message 'SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "tags"


so I reformulated like this:

'query' => $query->from('tags')->leftJoin('topic_tags','topic_tags.tag_id = tags.id')->select(['tags.*','(select count(topic_tags.id) from topic_tags where topic_tags.tag_id=tags.id) topicCount'])->groupBy('tags.id'),


and now I get the result:

enter image description here

apparently the topicCount column is not set, so when I try to sort by it, it returns the error:

exception 'PDOException' with message 'SQLSTATE[42703]: Undefined column: 7 ERROR: column "topicCount" does not exist


but when I try the SQL directly on the DB, it works fine:

enter image description here

so I suppose the problem is in the way Yii handles the alias 'topicCount'?




2nd EDIT



Still the same result without the topicCount set in the Grid view.
I show my TagSearch model, TagController and 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 the model behind the search form about `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 the parent class
return Model::scenarios();
}

/**
* Creates data provider instance with 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) from topic_tags where topic_tags.tag_id=tags.id) topicCount"])->groupBy("tags.id"),
]);

$this->load($params);

if (!$this->validate()) {
// uncomment the following line if you do not want to return any 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;

/**
* This is the model class for 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 am I missing?

dcc dcc
Answer

So resolved following this wiki:

Since in my case I don't use SUM('amount'), I changed to the following and 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) as topic_count')->groupBy('tag_id');        
    $query->leftJoin(["topicSum" => $subQuery], '"topicSum".tag_id = id');

Just encountered a problem with the generated SQL:

exception 'PDOException' with message 'SQLSTATE[42P01]: Undefined table: 7 ERROR:  missing FROM-clause entry for table "topicsum"

This might be a Postgres-specific issue, had to arrange the code so that the generated SQL becomes like this:

SELECT COUNT(*) FROM "tags" 
LEFT JOIN (SELECT "tag_id", COUNT(*) as topic_count FROM "topic_tags" GROUP BY "tag_id") "topicSum" 
ON "topicSum".tag_id = id

note the double-quotation in "topicSum".tag_id part.

Hope this might be of help for someone using Postgres on Yii2.

Comments