Yii2在网格中连接6个表格+下拉过滤器 [英] Yii2 join 6 tables + dropdown filter in grid
问题描述
$ b(b:B 1:N)将表格D连接到BCD将是没有问题的。不过,我想过滤从A,B,C和D的关键属性下拉列表中找到结果(因为最后我需要BCDid)。在BCD旁边的BCD和我可以存储当然援助,投标和CID,这似乎是一个相当简单的解决方法,但是我知道这是完全反对数据库规范化。有没有另一种更好的方法(当然是急于加载)?
我现在在BCD中:
preublic $ getB(){
return $ this- > hasOne(\app\models\B className(),['id'=>'Bid'])
- > via('BC');
}
似乎可以工作,但并不是急切的加载。
我该如何去模型A?我可以在BCD中这样定义它:
public function getA(){
return $ this-> ('BC')
- >通过(B);
}
现在还不行。
通过这种方式,它可以工作(BCSearch):
$ b $ pre $ code public function search($ params){
$ query = BC :: find() - > joinWith('A',true) - > joinWith('C',true);
BC中的关系A用via定义。下拉式过滤器也可以。
但是我仍然不知道如何在db结构中实现更深层次的功能。
似乎工作完全:
模型/ BCDSearch.php
public函数搜索($ params){
$ query = BCD :: find()
- > select([
'BCD.id',
'BCD.amount',
'A.id AS A_Id',
'A.name AS A_name',
'B.name AS B_name',
'B.name2 AS B_name2',
'C.name AS C_name',
'D.name AS D_name',
])
- > leftJoin('BC','BC.id = BC_Id')
- > leftJoin('B','B.id = B_Id')
- > leftJoin('A','A.id = A_Id')
- > leftJoin 'C','C.id = C_Id')
- > leftJoin('D','D.id = D_Id');
$ b $ query-> andFilterWhere([
...
'A.id'=> $ this-> A_Id,
'B.name'=> $ this-> B_name,
'B.name2'=> $ this-> B_name2,
'C.name'=> $ this-> ; C_name,
'D.name'=> $ this-> D_name,
]);
public function rules(){
return [
...
[[...'A_Id','B_name','B_name2','C_name ','D_name'],'safe'],
];
models / base / BCD.php:
类BCD扩展无论{
public $ A_Id;
public $ A_name;
public $ B_name;
public $ B_name2;
public $ C_name;
public $ D_name;
views / BCD / index.php:
<
'layout'=>'{summary} {pager} {items} {pager}',
'dataProvider'= $ {code> > $ dataProvider,
'pager'=> [
'class'=> yii\widgets\LinkPager :: className(),
'firstPageLabel'=> Yii :: t('app','First'),
'lastPageLabel'=> Yii :: t('app','Last')],
'filterModel'=> $ searchModel ,
'columns'=> [
[
'attribute'=>'A_Id',
'value'=>'A_name',
' filter'=> yii \helpers\ArrayHelper :: map(app\models\A :: find() - > all(),'id','name')
],
'b''=>'B_name',
'value'=>'B_name',
'filter'=> yii\helpers\ArrayHelper: :map([['id'=>'name1', name'=>'name1'],['id'=>'name2','name'=>'name2']],'id','name')
],
[
'attribute'=>'B_name2',
'value'=> 'B_name2',
'filter'=> yii\helpers\ArrayHelper :: map([['id'=>'name2_1','name'=>'name2_1'],['id'=>'name2_2','name'=> ;'name2_2']],'id','name')
],
[
'attribute'=> 'C_name',
'value'=> 'C_name',
'filter'=> yii \helpers\ArrayHelper :: map(app\models\C :: find() - > all(),'C_name','C_name')
],
[
'属性'=> 'D_name',
'value'=> 'D_name',
'filter'=> yii \helpers\ArrayHelper :: map(app\models\D :: find() - > all(),'D_name','D_name')
],
'amount ',
...
希望这有助于他人。 (至少对我来说)这个基本上简单的解决方案并不容易。我不知道为什么,但到目前为止,我无法在网上找到任何相关信息。
I have the following tables/models: A, B, C, BC, D, BCD
(A:B 1:N) Connecting table D to BCD would be no problem. However I would like to filter key attributes as dropdown from A, B, C and D to find results in BCD (because in the end I need BCDid). In BCD next to BCid and Did I can store of course Aid, Bid and Cid, and it would seem to me quite an easy workaround, however I know it's totally against db normalisation. Is there another, better way (with eager loading of course)? I've now this in BCD:
public function getB() {
return $this->hasOne(\app\models\B::className(), ['id' => 'Bid'])
->via('BC');
}
and it seems to work, but it's not eager loading. And how do I get to model A? can I define it like this in BCD?:
public function getA() {
return $this->hasOne(\app\models\A::className(), ['id' => 'Aid'])
->via('BC')
->via(B);
}
It doesn't really work yet.
This way it works (BCSearch):
public function search($params) {
$query = BC::find()->joinWith('A', true)->joinWith('C', true);
Relation A in BC defined with "via". Dropdown filter also works. But I still don't know how to achieve one more level deep into db structure.
This way it seems to work fully:
models/BCDSearch.php
public function search($params) {
$query = BCD::find()
->select([
'BCD.id',
'BCD.amount',
'A.id AS A_Id',
'A.name AS A_name',
'B.name AS B_name',
'B.name2 AS B_name2',
'C.name AS C_name',
'D.name AS D_name',
])
->leftJoin('BC', 'BC.id = BC_Id')
->leftJoin('B', 'B.id = B_Id')
->leftJoin('A', 'A.id = A_Id')
->leftJoin('C', 'C.id = C_Id')
->leftJoin('D', 'D.id = D_Id');
$query->andFilterWhere([
...
'A.id' => $this->A_Id,
'B.name' => $this->B_name,
'B.name2' => $this->B_name2,
'C.name' => $this->C_name,
'D.name' => $this->D_name,
]);
public function rules() {
return [
...
[[... 'A_Id', 'B_name', 'B_name2', 'C_name', 'D_name'], 'safe'],
];
}
models/base/BCD.php:
class BCD extends Whatever {
public $A_Id;
public $A_name;
public $B_name;
public $B_name2;
public $C_name;
public $D_name;
views/BCD/index.php:
GridView::widget([
'layout' => '{summary}{pager}{items}{pager}',
'dataProvider' => $dataProvider,
'pager' => [
'class' => yii\widgets\LinkPager::className(),
'firstPageLabel' => Yii::t('app', 'First'),
'lastPageLabel' => Yii::t('app', 'Last')],
'filterModel' => $searchModel,
'columns' => [
[
'attribute' => 'A_Id',
'value' => 'A_name',
'filter' => yii\helpers\ArrayHelper::map(app\models\A::find()->all(), 'id', 'name')
],
[
'attribute' => 'B_name',
'value' => 'B_name',
'filter' => yii\helpers\ArrayHelper::map([['id' => 'name1', 'name' => 'name1'], ['id' => 'name2', 'name' => 'name2']], 'id', 'name')
],
[
'attribute' => 'B_name2',
'value' => 'B_name2',
'filter' => yii\helpers\ArrayHelper::map([['id' => 'name2_1', 'name' => 'name2_1'], ['id' => 'name2_2', 'name' => 'name2_2']], 'id', 'name')
],
[
'attribute' => 'C_name',
'value' => 'C_name',
'filter' => yii\helpers\ArrayHelper::map(app\models\C::find()->all(), 'C_name', 'C_name')
],
[
'attribute' => 'D_name',
'value' => 'D_name',
'filter' => yii\helpers\ArrayHelper::map(app\models\D::find()->all(), 'D_name', 'D_name')
],
'amount',
...
hope this helps others. It was not easy to figure out (at least for me) this basically easy solution. I don't know why but so far I couldn't find any relevant info on the web like this.
这篇关于Yii2在网格中连接6个表格+下拉过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!