Yii2:对 GridView 中的关系计数列进行排序

Yii2: sort a relational count column in GridView(Yii2:对 GridView 中的关系计数列进行排序)

问题描述

我很难按定义为模型标签"上的关系 getter 的topicCount"进行排序.一个主题可以有很多标签，并且希望按照包含该标签的主题的数量对标签进行排序.

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.

在我的模型/Tag.php 中:

In my models/Tag.php:

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

在我的 views/tag/index.php 中:

And in my views/tag/index.php:

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

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

在我的控制器/TagController.php 中:

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,
    ]);
}

在我的模型/TagSearch.php 中:

And in my models/TagSearch.php:

<?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();
    $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;
}


}

在索引视图中，我可以看到正确的 topicCount:

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

但是在单击 topicCount 列时出现错误:

but on clicking the topicCount column I get the error:

带有消息SQLSTATE[42703]"的异常PDOException":未定义的列:7 错误:topicCount"列不存在第 1 行:SELECT * FROM "tags" ORDER BY "topicCount" LIMIT 100

感谢您的指导...！

按照 Lucas 的建议，我在 $dataProvider 中设置了 dataProvider 查询，如下所示:

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'),

我得到了错误:

带有消息SQLSTATE[42P01]"的异常PDOException":未定义的表:7 错误:缺少表标签"的 FROM 子句条目

所以我改写成这样:

        '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'),

现在我得到了结果:

显然没有设置 topicCount 列，所以当我尝试按它排序时，它返回错误:

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

带有消息SQLSTATE[42703]"的异常PDOException":未定义的列:7 错误:topicCount"列不存在

但是当我直接在数据库上尝试 SQL 时，它工作正常:

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

所以我想问题在于 Yii 处理别名topicCount"的方式?

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

在网格视图中没有设置 topicCount 的情况下仍然是相同的结果.我在下面展示了我的 TagSearch 模型、TagController 和 views/tag/index 视图文件:

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:

标签搜索

<?php

namespace commonmodels;

use Yii;
use yiiaseModel;
use yiidataActiveDataProvider;
use commonmodelsTag;

/**
 * TagSearch represents the model behind the search form about `commonmodelsTag`.
 */
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;
    }
}

标签模型

<?php

namespace commonmodels;

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 yiidbActiveRecord
{

    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',
        ];
    }

}

标签控制器

public function actionIndex()
{

    $searchModel = new TagSearch();
    $myModels = $searchModel->search([]);

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

标签/索引

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'columns' => [
        'id',
        'name',
        'topicCount',
        'created_at',
        'updated_at',
        ['class' => 'yiigridActionColumn','template' => '{view}',],
    ],
]); ?>

我错过了什么?

推荐答案

于是解决了这个维基:

由于在我的情况下我不使用 SUM('amount')，我更改为以下内容并且工作正常:

Since in my case I don't use SUM('amount'), I changed to the following and works perfectly:

标签模型:

public function getTopicCount() 
{
    return $this->hasMany(TopicTag::className(), ["tag_id" => "id"])->count();

}

标签搜索模型:

    $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');

刚生成的SQL遇到问题:

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"

这可能是 Postgres 特有的问题，不得不安排代码，使生成的 SQL 变成这样:

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

注意"topicSum".tag_id 部分的双引号.

希望这对在 Yii2 上使用 Postgres 的人有所帮助.

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

这篇关于Yii2:对 GridView 中的关系计数列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！