Yii2: sort relational counter column in GridView
[EDITED 2]
I'm having a hard time sorting by "topicCount" which is defined as a relational getter on the "Tag" model. A topic can have many tags and wants to sort the tags by the number of topics that contain 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 see the correct topicCount:
but clicking on topicCount column shows 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 recommendations ..!
[EDIT]
Following Lucas's advice, I have set my dataProvider request 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 the 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:
obviously the topicCount column is not set, so when I try to sort it it returns an error:
exception 'PDOException' with message 'SQLSTATE[42703]: Undefined column: 7 ERROR: column "topicCount" does not exist
but when I try to execute SQL directly in DB it works fine:
so my guess is that the problem is how Yii handles the "topicCount" alias?
2nd EDIT
Still the same result without the topicCount parameter set in the Grid view. I'll show my TagSearch model, TagController and views / tags / index 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,
]);
}
labels / index
<?= GridView::widget([
'dataProvider' => $dataProvider,
'columns' => [
'id',
'name',
'topicCount',
'created_at',
'updated_at',
['class' => 'yii\grid\ActionColumn','template' => '{view}',],
],
]); ?>
What am I missing?
source to share
So the following is allowed on this wiki :
Since in my case I am not using SUM ('amount'), I changed to the following and it works fine:
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 got a problem with the generated SQL:
exception 'PDOException' with message 'SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "topicsum"
It might be a Postgres related issue, I had to streamline the code so that the generated SQL looks 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
notice the double quote in part "topicSum".tag_id
.
Hope this can help someone using Postgres on Yii2.
source to share
You should change your group query and select a counter instead of working with relationships.
$query->groupBy('tags.id')->select(['tags.*','(select count(topic_tag.id) from topic_tag where topic_tag.tag.id=tags.id) topicCount']);
This will add topicCount
as a result an object to your query that will make it behave like a regular column.
Also as a side note, for a relation action method in Yii2, it must return an object ActiveQuery
. Yours getTopicCount()
returns the counter as an int instead of a query, so Yii2 won't treat it as a relation.
source to share
Based on this Wiki and @arogachev's answer. I put a property select
to get count tags
public function search($params)
{
$query = SomeModels::find()
->select('subQueryName.field_count, someModels.*');
// ....
so it will give SQL as SELECT subQuery.field_count, someModels.*
...
in view (grid),
[
'attribute'=> 'field_count',
],
Thanks @arogachev you saved me :)
source to share
light solution is just a reate view
in PostgreSQL
and generate a model through a generator gii
using both model and order and find work.
For updates and deletions, use the model table
to find and use the index view
.
for example
for actions update
and delete
use Tag
model
for actions index
and view
use the model TagView
.
source to share