CODEWITHSUNDEEP
phpmysqlyiiyii2
fabio
fabio

Reputation: 235

yii2: index page (with search) for relation one-to-many disaplying all fields

I'm quite new in Yii and I would like to know if this problem can be resolved in yii framework. I have three tables (A,B,C), A has the primary key 'a_id', B and C have a link to A (for example: b_a_id, c_a_id) that is a foreign key to 'a_id', relation A-B is one-to-one, relation A-C is one-to-many.

Is it possible in Yii in a simple way to have:

1) for relation A - B, an index-page with search bar for every field that displays all the field of A and B

2) for relation A - C, an index-page with search bar for every field that displays all the field of A and c

3) the same for A - B - C

??

I'm not interested in CREATE/UPDATE/DELETE operation, only INDEX with SEARCH

I has been able to show A-B relation using B Controller/Model/View displaying the index page with A fields and B fields. I used a code like that below

<?= GridView::widget([
    'dataProvider' => $dataProviderB,
    'filterModel' => $searchModelB,
    'columns' => [
        //['class' => 'yii\grid\SerialColumn'],
        'b_id',
        'b_field1',
        'b_field2', 

        'b_a_id.a_field1',    //field of B table
        'b_a_id.a_field2',
        'b_a_id.d_id.field1', //also field of D table liked to B

        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>

But It's not available search box on 'b_a_id.a_field1', 'b_a_id.a_field2' neither obviously on 'b_a_id.d_id.field1'. I know why. Because using foreign kwy i would like to show more than one field.

Do you have any suggestion for this kind of problem? Should I build a join sql command and display it? And so would the search available? Should I build a view in mysql database and generate model from view?

Please, reply with accuracy because I'm new in Yii

Thanks in advance

Upvotes: 1

Views: 1510

Answers (1)

DrunkWolf
DrunkWolf

Reputation: 1004

You will need to change your searchModel (the one generated by GII when you made the crud).

in your view first change into, 

<?= GridView::widget([
'dataProvider' => $dataProviderB,
'filterModel' => $searchModelB,
'columns' => [
    //['class' => 'yii\grid\SerialColumn'],
    'b_id',
    'b_field1',
    'b_field2', 

    ['attribute'=>'afield1','value'=>'b_a_id.a_field1'], //so you have an easy tag to refer to in the searchmodel
    ['attribute'=>'afield2','value'=>'b_a_id.a_field2'],   
    //left out the one to D, should be analog

    ['class' => 'yii\grid\ActionColumn'],
],

Now in your searchModel you need to do a few steps, first off all, inside the class add

public $afield1 //the tag we used in the grid
public $afield2

Then add them in the rules 

public function rules(){
    return [
        ...,
        [[...,'afield1','afield2'],'safe'],
        ...,
   ];
}

Then inside the search function, join a to b, and tell the dataProvider how the handle the sort

$query = b:find(); 

$query->joinWith('b_a_id');//join the table

$dataProvider = ...;

$this->load($params);

$dataProvider->sort->attributes['afield1'] = [ 
   'asc'=>['b_a_id.a_field1'],
   'desc'=>['b_a_id.a_field1']
] // tell how to sort

and finally, where the query is add an 'andFilterWhere' as such

$query->andFilterWhere([...])
   ...
   ->andFilterWhere(['like','b_a_id.a_field1']);

It's also possible to sort on one-to-many / many-to-many relationships in a similar way, except you need to join more, and you need to define a 'getOneToManyDisplay' method in the model that transform that relationship into a string, but otherwise works in a similar way

Upvotes: 4

Related Questions