Yii2 GridView对外表的相关表进行过滤和排序 [英] Yii2 GridView implement Filter and Sort for Values for related Table of foreign Table
问题描述
CREATE TABLE tabCve
(
intCveID INTEGER NOT NULL AUTO_INCREMENT,
strNumber VARCHAR(20)NOT NULL,
fltScore FLOAT(0),
strDescription TEXT,
datImported DATETIME NOT NULL DEFAULT NOW(),
intCvePhaseID INTEGER,
intCveTypeID INTEGER,
PRIMARY KEY(intCveID),
KEY(intCvePhaseID),
KEY(intCveTypeID)
);
CREATE TABLE tabProgress
(
intProgressID INTEGER NOT NULL AUTO_INCREMENT,
intProgressCveID INTEGER NOT NULL,
intProgressUserID INTEGER NOT NULL,
intProgressStateID INTEGER NOT NULL,
intProgressCategoryID INTEGER,
datCreated DATETIME NOT NULL,
PRIMARY KEY(intProgressID),$ b $ KEY(intProgressCategoryID),$ b $ KEY(intProgressCveID) ,
KEY(intProgressStateID),
KEY(intProgressUserID)
);
CREATE TABLE tabCategory
(
intCategoryID INTEGER NOT NULL AUTO_INCREMENT,
strCategory VARCHAR(50)NOT NULL,
PRIMARY KEY(intCategoryID)
);
我创建了一个带有Gii的CRUD,用于 tabCve
。
我在成功实现过滤和排序函数中引用表中的字段,比如 intCvePhaseID
现在我想通过 tabProgress
实现 tabCategory
tabCve
和 tabProgress
是1到1。
如何在我的SearchModel?
我到目前为止做了什么:
/ p>
public function getProgress()
{
return $ this-> hasOne(TabProgress :: className( ),['intProgressCveID'=&';'intCveID']) - > with(['category']);
$ b $ public function getCategory()
{
return $ this-> hasOne(TabCategory :: className(),['intCategoryID'=>' intProgressCategoryID']);
}
在SearchModel中:
公共职能搜索($ params)
{
$ query = TabCve :: find();
'query'=> $ query,
'sort'=> ['defaultOrder'=&';''strNumber'=&''DESC' ]],
]);
$ query-> select([intCveID,strNumber,fltScore,strDescription,datImported,intCvePhaseID,intCveTypeID,progress.intProgressCategoryID] );
$ query-> joinWith(phase);
$ query-> joinWith(type);
$ query-> joinWith(progress);
$ query-> Where(['不喜欢','strDescription','** RESERVED **%',false]);
$ query-> andWhere(['不喜欢','strDescription','** REJECT **%',false]);
// $ query-> andWhere([intProgressID=> null]);
$ this->载入($ params);
if(!$ this-> validate()){
//如果在验证失败时不想返回任何记录,请取消注释以下行
// $查询 - 化合物其中( '0 = 1');
返回$ dataProvider;
$ b $ query $> andFilterWhere([
'intCveID'=> $ this-> intCveID,
'fltScore'=> $ this - > fltScore,
'datImported'=> $ this-> datImported,
]);
$ query-> andFilterWhere(['like','strNumber',$ this-> strNumber])
- > andFilterWhere(['like','strDescription', $ this - > strDescription])
- > andFilterWhere(['like','tabPhase.strPhase',$ this-> intCvePhaseID])
- > andFilterWhere(['like', (['like','tabType.strType',$ this-> intCveTypeID])
- > andFilterWhere(['dataImported',$ this-> datImported])
- > andFilterWhere 'like','tabProgress.tabCategory.strCategory',$ this-> intCveTypeID])
;
返回$ dataProvider;
}
如何在这些行中实现字段:
$ b $ $ $ $ $ $ $ $ $ $查询 - > select([intCveID,strNumber,fltScore,strDescription,datImported,intCvePhaseID , intCveTypeID, 'progress.intProgressCategoryID']);
和:
- > andFilterWhere(['like','tabProgress.tabCategory.strCategory',$ this-> intCveTypeID])
在您的seachModel中,您需要public var for filter ..
这是由find提供的,并且可以更好地重新排列下面的代码。
$ query-> select([intCveID, strNumber, fltScore, strDescription, datImported, intCvePhaseID, intCveTypeID, 'progress.intProgressCategoryID']);
$ query-> joinWith(phase);
$ query-> joinWith(type);
$ query-> joinWith(progress);
$ query-> Where(['不喜欢','strDescription','** RESERVED **%',false]);
$ query-> andWhere(['不喜欢','strDescription','** REJECT **%',false]);
以另一种方式像本文档中建议的那样
http://www.yiiframework.com/wiki/621/filter-sort-by-calculated-related-fields-in-gridview-yii-2-0/
For
- > andFilterWhere(['like','tabProgress.tabCategory.strCategory',
$ this-> intCveTypeID])
在上面的链接中提供的样本建议使用getter来检索您需要的列,并且还可以在andFilterWhere中使用此getter
这种方式:
在你的模型中,你已经有了一个
public function getCategory()
{
return $ this-> ; hasOne(TabCategory :: className(),
['intCategoryID'=> intProgressCategoryID]);
}
然后你可以为strCategory创建一个getter。 b
public function getStr_category(){
return $ this-> category-> strCategory;
}
此时您可以检索modelSearch中的数据
$ b $
- > andFilterWhere(['like','str_category',$ this-> intCveTypeID])
I have 3 Tables:
CREATE TABLE tabCve
(
intCveID INTEGER NOT NULL AUTO_INCREMENT,
strNumber VARCHAR(20) NOT NULL,
fltScore FLOAT(0),
strDescription TEXT,
datImported DATETIME NOT NULL DEFAULT NOW(),
intCvePhaseID INTEGER,
intCveTypeID INTEGER,
PRIMARY KEY (intCveID),
KEY (intCvePhaseID),
KEY (intCveTypeID)
) ;
CREATE TABLE tabProgress
(
intProgressID INTEGER NOT NULL AUTO_INCREMENT,
intProgressCveID INTEGER NOT NULL,
intProgressUserID INTEGER NOT NULL,
intProgressStateID INTEGER NOT NULL,
intProgressCategoryID INTEGER,
datCreated DATETIME NOT NULL,
PRIMARY KEY (intProgressID),
KEY (intProgressCategoryID),
KEY (intProgressCveID),
KEY (intProgressStateID),
KEY (intProgressUserID)
) ;
CREATE TABLE tabCategory
(
intCategoryID INTEGER NOT NULL AUTO_INCREMENT,
strCategory VARCHAR(50) NOT NULL,
PRIMARY KEY (intCategoryID)
) ;
I have created a CRUD with Gii for tabCve
.
I was successfull in implementing filter and sort functions for fields in referenced tables like intCvePhaseID
Now I would like to implement this for tabCategory
via tabProgress
the relation between tabCve
and tabProgress
is 1 to 1.
How do I have to implement it in my SearchModel?
What I did so far:
In the Model:
public function getProgress()
{
return $this->hasOne(TabProgress::className(),['intProgressCveID' => 'intCveID'])->with(['category']);
}
public function getCategory()
{
return $this->hasOne(TabCategory::className(),['intCategoryID' => 'intProgressCategoryID']);
}
In the SearchModel:
public function search($params) { $query = TabCve::find();
$dataProvider = new ActiveDataProvider([
'query' => $query,
'sort'=> ['defaultOrder' => ['strNumber' => 'DESC']],
]);
$query->select(["intCveID","strNumber","fltScore","strDescription","datImported","intCvePhaseID","intCveTypeID",'progress.intProgressCategoryID']);
$query->joinWith("phase");
$query->joinWith("type");
$query->joinWith("progress");
$query->Where(['not like', 'strDescription', '** RESERVED **%', false]);
$query->andWhere(['not like', 'strDescription', '** REJECT **%', false]);
//$query->andWhere(["intProgressID" => null]);
$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([
'intCveID' => $this->intCveID,
'fltScore' => $this->fltScore,
'datImported' => $this->datImported,
]);
$query->andFilterWhere(['like', 'strNumber', $this->strNumber])
->andFilterWhere(['like', 'strDescription', $this->strDescription])
->andFilterWhere(['like','tabPhase.strPhase', $this->intCvePhaseID])
->andFilterWhere(['like','datImported',$this->datImported])
->andFilterWhere(['like','tabType.strType', $this->intCveTypeID])
->andFilterWhere(['like','tabProgress.tabCategory.strCategory', $this->intCveTypeID])
;
return $dataProvider;
}
How do I have to implement the fields in these lines:
$query->select(["intCveID","strNumber","fltScore","strDescription","datImported","intCvePhaseID","intCveTypeID",'progress.intProgressCategoryID']);
and:
->andFilterWhere(['like','tabProgress.tabCategory.strCategory', $this->intCveTypeID])
In your seachModel you need public var for filter ..
not need select because this are provide by find.. and is better rearrange the code below
$query->select(["intCveID","strNumber","fltScore","strDescription","datImported","intCvePhaseID","intCveTypeID",'progress.intProgressCategoryID']);
$query->joinWith("phase");
$query->joinWith("type");
$query->joinWith("progress");
$query->Where(['not like', 'strDescription', '** RESERVED **%', false]);
$query->andWhere(['not like', 'strDescription', '** REJECT **%', false]);
in another way like suggested in this doc
http://www.yiiframework.com/wiki/621/filter-sort-by-calculated-related-fields-in-gridview-yii-2-0/
http://www.yiiframework.com/wiki/653/displaying-sorting-and-filtering-model-relations-on-a-gridview/
For
->andFilterWhere(['like','tabProgress.tabCategory.strCategory', $this->intCveTypeID])
the samples provide in the link above suggest the use of getter for retrieve the column you need and the use of this getter also in andFilterWhere
this way :
in your Model you already have a
public function getCategory()
{
return $this->hasOne(TabCategory::className(),
['intCategoryID' => 'intProgressCategoryID']);
}
then you can buil a getter for for strCategory
public function getStr_category() {
return $this->category->strCategory;
}
at this point you can retrieve the data in you modelSearch with
->andFilterWhere(['like','str_category', $this->intCveTypeID])
这篇关于Yii2 GridView对外表的相关表进行过滤和排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!