php – Yii2:在GridView中对关系计数列进行排序
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了php – Yii2:在GridView中对关系计数列进行排序,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6233字,纯文字阅读大概需要9分钟。
内容图文
![php – Yii2:在GridView中对关系计数列进行排序](/upload/InfoBanner/zyjiaocheng/795/820cb819512040d19a28d4ff69ec51bb.jpg)
[编辑2]
我很难按’topicCount’进行排序,’topicCount’被定义为模型’Tag’上的关系getter.
主题可以包含大量标记,并希望按标记的多少主题对标记进行排序.
在我的models / Tag.php中:
public function getTopicCount()
{
return TopicTag::find()->where(['tag_id' => $this->id])->count();
}
在我的views / tag / index.php中:
<?= GridView::widget([
'dataProvider' => $dataProvider,
'columns' => [
'id',
'name',
[
'attribute'=>'topicCount',
'value' => 'topicCount',
],
'created_at',
['class' => 'yii\grid\ActionColumn','template' => '{view}',],
],
]); ?>
在我的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中:
<?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;
}
}
在索引视图中,我可以看到正确的topicCount:
但是点击topicCount列我得到错误:
异常’PDOException’,消息’SQLSTATE [42703]:未定义列:7错误:列“topicCount”不存在
第1行:SELECT * FROM“tags”ORDER BY“topicCount”LIMIT 100
感谢您的任何指导..!
[编辑]
按照Lucas的建议,我在我的$dataProvider中设置了我的dataProvider查询,如下所示:
'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错误:缺少表“tags”的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列没有设置,所以当我尝试按它排序时,它会返回错误:
异常’PDOException’,消息’SQLSTATE [42703]:未定义列:7错误:列“topicCount”不存在
但是当我直接在数据库上尝试SQL时,它运行正常:
所以我想问题是Yii处理别名’topicCount’的方式?
第二次编辑
如果没有在Grid视图中设置topicCount,结果仍然相同.
我在下面显示我的TagSearch模型,TagController和views / tag / index视图文件:
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;
}
}
标签模型
<?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,
]);
}
标签/索引
<?= GridView::widget([
'dataProvider' => $dataProvider,
'columns' => [
'id',
'name',
'topicCount',
'created_at',
'updated_at',
['class' => 'yii\grid\ActionColumn','template' => '{view}',],
],
]); ?>
我错过了什么?
解决方法:
在this wiki之后解决了:
因为在我的情况下,我不使用SUM(‘金额’),我改为以下并完美地工作:
标签型号:
public function getTopicCount()
{
return $this->hasMany(TopicTag::className(), ["tag_id" => "id"])->count();
}
TagSearch模型:
$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问题:
exception 'PDOException' with message 'SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "topicsum"
这可能是Postgres特定的问题,必须安排代码,以便生成的SQL变为如下所示:
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的人有所帮助.
内容总结
以上是互联网集市为您收集整理的php – Yii2:在GridView中对关系计数列进行排序全部内容,希望文章能够帮你解决php – Yii2:在GridView中对关系计数列进行排序所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。