Yii2 GridView 实现对外部表相关表的值进行过滤和排序 [英] Yii2 GridView implement Filter and Sort for Values for related Table of foreign Table

问题描述

我有 3 个表:

CREATE TABLE tabCve(intCveID INTEGER NOT NULL AUTO_INCREMENT,strNumber VARCHAR(20) 非空，fltScore FLOAT(0),strDescription TEXT,datImported DATETIME NOT NULL DEFAULT NOW(),intCvePhaseID 整数，intCveTypeID 整数，主键(intCveID)，密钥(intCvePhaseID)，KEY (intCveTypeID));CREATE TABLE tabProgress(intProgressID INTEGER NOT NULL AUTO_INCREMENT,intProgressCveID 整数非空，intProgressUserID 整数非空，intProgressStateID 整数非空，intProgressCategoryID 整数，datCreated DATETIME NOT NULL,主键(intProgressID)，KEY (intProgressCategoryID),密钥(intProgressCveID)，密钥(intProgressStateID)，KEY (intProgressUserID));创建表选项卡类别(intCategoryID INTEGER NOT NULL AUTO_INCREMENT,strCategory VARCHAR(50) 非空，主键(intCategoryID));

我用 Gii 为 tabCve 创建了一个 CRUD.我成功地为intCvePhaseID

等引用表中的字段实现了过滤和排序功能

现在我想通过 tabProgress 为 tabCategory 实现这个tabCve 和 tabProgress 之间的关系是 1 比 1.

我必须如何在我的 SearchModel 中实现它?

到目前为止我做了什么:

在模型中:

公共函数getProgress(){return $this->hasOne(TabProgress::className(),['intProgressCveID' => 'intCveID'])->with(['category']);}公共函数 getCategory(){返回 $this->hasOne(TabCategory::className(),['intCategoryID' =>'intProgressCategoryID']);}

在搜索模型中:

公共函数搜索($params){$query = TabCve::find();

$dataProvider = new ActiveDataProvider(['查询' =>$查询，'排序'=>['defaultOrder' =>['strNumber' =>'降级']]，]);$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);如果 (!$this->validate()) {//如果您不想在验证失败时返回任何记录，请取消注释以下行//$query->where('0=1');返回 $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]);返回 $dataProvider;}

我必须如何实现这些行中的字段:

$query->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(['not like', 'strDescription', '** RESERVED **%', false]);$query->andWhere(['not like', 'strDescription', '** REJECT **%', false]);

以本文档中建议的另一种方式

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/

为了

<块引用>

->andFilterWhere(['like','tabProgress.tabCategory.strCategory',$this->intCveTypeID])

上面链接中提供的示例建议使用 getter 来检索您需要的列，并且在 andFilterWhere 中也使用此 getter

这样:

在你的模型中你已经有一个

公共函数getCategory(){返回 $this->hasOne(TabCategory::className(),['intCategoryID' =>'intProgressCategoryID']);}

然后你可以为 strCategory 构建一个 getter

公共函数 getStr_category() {返回 $this->category->strCategory;}

此时您可以使用

检索modelSearch中的数据

->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屋！