problem with many-to-many relation in gridview

Yii 2.0 General Discussions
1

Hi, sorry if this isn’t the right place to post this.

I’m developing a web application with Yii2 framework, and i’m facing a problem now. I want to display and search data from a many-to-many relation in a gridview, but i can’t get the right values to show. I have 3 tables: actividad, plan_actividad and circulo_icare (plan_actividad being the junction table), actividad is related to plan_actividad and circulo_icare as well. So i have defined the following relations in Actividad model:




public function getPlanActividad()

{

    return $this->hasMany(PlanActividad::classname(), ['act_id' => 'act_id']);

}

and




public function getCirculo()

{

    return $this->hasMany(CirculoIcare::classname(), ['cirica_id' => 'act_id'])->via('planActividad');

}

and then accesing the data in my view index.php in a gridview as follows:




<?= GridView::widget([

        'dataProvider' => $dataProvider,

        // 'filterModel' => $searchModel,

        'columns' => [

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


            // 'act_id',

            ['attribute' => 'Codigo Evento', 'value' => 'act_numorden'],

            ['attribute' => 'Nombre Evento', 'value' => 'act_nombre'],

            ['attribute' => 'Fecha Evento', 'value' => 'act_fecha'],

            ['attribute' => 'Locacion', 'value' => 'locacion.loc_nombre'],

            [

            'attribute' => 'Circulo', 

            'value' => 'circulo.cirica_nombre',             

            ],

            ['attribute' => 'Circulo id', 'value' => 'planActividad.cirica_id'],

            // 'act_horaini',

            // 'act_horafin',

            // 'act_idencuesta',

            // 'act_vigencia:boolean',

            // 'loc_id',


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

        ],

    ]); ?>

The problem is, all the values show “not defined” in the gridview like there’s no existing relations but there are. I’ve read the documentation about working with relations in Yii2, some forum and stackoverflow post as well, can get the right values when using hasOne() in a 1-to-1 relations but can’t get n-to-n relations to work with hasMany(), always get (not defined) using hasMany().

Any help would be appreciated, let me know if any more info is needed and sorry for my bad english.

2

So i changed:




class Actividad extends \yii\db\ActiveRecord 

{

 ...


 public function getPlanActividad()

    {

        return $this->hasMany(PlanActividad::classname(), ['act_id' => 'act_id']);

    }

 

 ...

}

to




class Actividad extends \yii\db\ActiveRecord 

{

 ...


 public function getPlanActividad()

    {

        return $this->hasOne(PlanActividad::classname(), ['act_id' => 'act_id']);

    }

 

 ...

}

And can get some values to show in the gridview using the circulo relation. However the values are not correct and it just shows 2 of 11 it should (based on the cirica_id that plan_actividad has, i can see them displayed correctly via plan_actividad relation after the above modification.).

I still don’t understand why this is happening. For some reason, every time i try to use hasMany it shows wrong values or just plain (not set). Maybe i should use a different method to do what i want but i’m not sure, as i want to be able to filter the gridview by those fields later on.

Any help would be greatly appreciated, thanks in advance.

3

Updating my own question again, sorry if this is not the right way to do it.

Got it to work by doing this in the gridview:




<?= GridView::widget([

'dataProvider' => $dataProvider,

'columns' => [

...


['attribute' => 'planActividad.cirica_id',

             'value' => function($data)

                    {

                        $circulo_id = $data->getPlanActividad()->select('cirica_id')->one();

                        $circulo_nombre = app\models\CirculoIcare::find()->select('cirica_nombre')->where(['cirica_id' => $circulo_id])->one();

                        return $circulo_nombre['cirica_nombre'];

                        

                    },

            ],


...

],

]) ?>

And then filtering in my ActividadSearch model with planActividad relation by cirica_id, this works as expected but it causes the page to load really slow and i know it’s not the right way to do it. I still can’t understand what am i doing wrong with the relations via a junction table, can’t get the right values to show in my gridview.

I can get values from a simple many-to-many relation in a gridview with a callback function now but it doesn’t work when there’s a junction table involved and i use via or viaTable.

Any help or hint would be greatly appreciated.

4

Hi Gonzalo, welcome to the forum.

First of all, you should be aware that a has many relation gives you an array of models.

You have been trying to display it as a single model.

Try this instead:




class Actividad extends \yii\db\ActiveRecord 

{

 ...


 // public function getPlanActividad()

 public function getPlanActividads()

    {

        return $this->hasMany(PlanActividad::classname(), ['act_id' => 'act_id']);

    }

  ...

}


...


// public function getCirculo()

public function getCirculos()

{

    return $this->hasMany(CirculoIcare::classname(), ['cirica_id' => 'act_id'])->via('planActividads');

}

You know, it’s a good practice to use plural names for has many relations. :)

And in the view:




<?= GridView::widget([

  'dataProvider' => $dataProvider,

  'columns' => [

    ...

    [

        'label' => 'Circulo',

        'value' => function($model)

            {

                $items = [];

                foreach($model->circulos as $circulo) {

                   $items[] = $circulo->name;

                }

                return implode(', ', $items);

             },

     ],

     ...

]) ?>
5

Hi softark, thanks for your answer!

I understand a little bit more about working with relations now, especially many-to-many relations (as you said, I was trying to display them as single model instead of an array). Seems like a callback function is the right way to do it in my case.

I’ve updated my code with your suggestions, and got some values to show in the gridview but these are not correct. In the gridview it display only 2 values (of 11 it should, based on a raw sql query i made), and both are wrong. What could be causing this? maybe i defined my relations wrong or something else is missing.

Cirica ID | Nombre Circulo

(not set) |

(not set) |

Marketing | Personas

Personas | Innovacion

(not set) |

(not set) |

Personas |

Innovacion |

This is how part of the gridview looks right now, the values on the column Cirica ID are the correct ones, and the values on the Nombre Circulo column are wrong, these are the ones displayed by the relation:




public function getCirculos()

{

    return $this->hasMany(CirculoIcare::classname(), ['cirica_id' => 'act_id'])->via('planActividads');

}

Thanks again for your answer, was really helpful.

6

Could you show us the following?

  1. The schema of the tables involved in this issue.

  2. The definitions of the relations among the involved models.

  3. The code that you used to show the related values.

  4. The raw sql that gave you the expected results.

7

The scheme of the tables involved is the following:

actividad


act_id | loc_id | tiac_id | act_numorden | act_nombre | act_fecha | act_horaini | act_horafin | act_idencuesta | act_vigencia

  1       2         4         60            dinner      18-06-2017     09:10         12:30           80             1

plan_actividad


planact_id | cirica_id | act_id | planact_observacion 

1             2            1      sample observation

circulo_icare


cirica_id | cirica_nombre | cirica_detalle | cirica_fechaini | cirica_fechafin | cirica_vigencia

2            marketing      sample detail     08-05-2016         15-11-2017             1

The defined relations among the models are as follows:

Actividad:


class Actividad extends \yii\db\ActiveRecord 

{

 ...


public function getPlanActividad()

{

    return $this->hasMany(PlanActividad::classname(), ['act_id' => 'act_id']);

}


public function getCirculos()

{

    return $this->hasMany(CirculoIcare::classname(), ['cirica_id' => 'act_id'])->via('planActividad');

}


...


}

Circulo




class CirculoIcare extends \yii\db\ActiveRecord

{


...


    public function getPlanActividad()

    {

        return $this->hasMany(PlanActividad::classname(), ['cirica_id' => 'cirica_id']);

    }


    public function getActividad()

    {

        return $this->hasMany(Actividad::classname(), ['act_id' => 'cirica_id'])->via('planActividad');

    }


...


}

PlanActividad




class PlanActividad extends \yii\db\ActiveRecord

{


...


    public function getActividad()

    {

        return $this->hasOne(Actividad::classname(), ['act_id' => 'act_id']);

    }


    public function getCirculo()

    {

        return $this->hasOne(CirculoIcare::classname(), ['cirica_id' => 'cirica_id']);

    }


...


}

The code that i’m using to display the values in the gridview is located in views/actividad/index.php, and looks as follows:




<?= GridView::widget([

        'dataProvider' => $dataProvider,

        // 'filterModel' => $searchModel,

        'columns' => [

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


            ...

            ['attribute' => 'planActividad.cirica_id',

             'value' => function($data)

                    {

                        $circulo_id = $data->getPlanActividad()->select('cirica_id')->one();

                        $circulo_nombre = app\models\CirculoIcare::find()->select('cirica_nombre')->where(['cirica_id' => $circulo_id])->one();

                        return $circulo_nombre['cirica_nombre'];

                        

                    },

            ],

            ['attribute' => 'Tipo Actividad',

            'value' => 'tipoActividad.tiac_nombre'],

            ['attribute' => 'Nombre Circulo',

             'value' => function($model){

                $items = [];

                foreach($model->circulos as $circulo){

                    $items[] = $circulo->cirica_nombre;

                }

                return implode(', ', $items);

             }],

            

            ...

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

        ],

    ]); ?>

I didn’t really used raw sql to get the expected values, but rather used yii ActiveRecord to do the query for me. Said query looks as follows:




...


['attribute' => 'planActividad.cirica_id',

             'value' => function($data)

                    {

                        $circulo_id = $data->getPlanActividad()->select('cirica_id')->one();

                        $circulo_nombre = app\models\CirculoIcare::find()->select('cirica_nombre')->where(['cirica_id' => $circulo_id])->one();

                        return $circulo_nombre['cirica_nombre'];

                        

                    },

            ],


...

This query is in my gridview as well. I know this is not the right way to do it, and that’s why it takes a long time to load the related values.

Let me know if any more info is needed, thanks a lot for you help.

8

I think that getCirculos() method should be like the following:




public function getCirculos()

{

    return $this->hasMany(CirculoIcare::classname(), ['cirica_id' => 'cirica_id'])->via('planActividad');

}

In the same way, getActividad() must be:




    public function getActividad()

    {

        return $this->hasMany(Actividad::classname(), ['act_id' => 'act_id'])->via('planActividad');

    }
9

‘attribute’ property of DataColumn is rather for a simple scenario where you don’t need to specify ‘value’ nor ‘label’ manually. DataColumn will look up ‘attributeLabels’ of the model to get the label for the specified attribute when the label is not specified manually. ‘attribute’ is not supposed to be used to specify the label directly as you did for for ‘Nombre Circulo’ and others.

http://www.yiiframework.com/doc-2.0/yii-grid-datacolumn.html#$attribute-detail

10

The relation works es expected now, it shows the correct values in the gridview!

Can you explain to me why the relation has to be defined this way?




public function getCirculos()

{

    return $this->hasMany(CirculoIcare::classname(), ['cirica_id' => 'cirica_id'])->via('planActividad');

}

I thought it was supposed to be:


$this->hasMany(ClassModel::classname(), ['current_table_id' => 'referenced_table_id'])

Like shown in the documentation here, most probably I interpreted it the wrong way.

About the ‘attribute’ property, I know it’s not supposed to be used like that, but I was using it that way just to see the values the gridview displayed, will change it now that it works as expected. I can also apply filter correctly in the gridview with the relation fields.

I have another issue I would like to ask about, but it’s not related to this scenario at all (it’s related to nested pjax and reloading a gridview after insert). Should I create a new topic for that?

Thanks for all your help softark! learned a lot more now, appreciate it.

11

In the API reference of hasMany(), you’ll see the following:

So the array should be




[$attr_in_related_model => $attr_in_this_model]

// or, in terms of underlying table

[$column_in_related_table => $column_in_this_table]

And in the guide for relational data:

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#junction-table

The array should be interpreted as




[$attr_in_related_model => $attr_in_junction_model]

// or, in terms of underlying table

[$column_in_related_table => $column_in_junction_table]

Well, I have to say that the description should be improved a little in the documentation.

Yes, you should start a new topic for it.

12

I see now, so the array in the relations using via/viaTable refers to the attributes in the class model/corresponding table and the attributes in the junction table. I interpreted it wrong, my mistake.

Thanks again for all your help and support softark, problem is fully solved now, relations are working exactly as expected right now. I’ll open a new thread to post the pjax related issue then.