yii2 - fill table with data from two tables from database

Question

I used Yii and Yii2 a few times... and every time I use it I get lost with this framework. Actually, Yii it's very powerful, it does a lot of things for you but sometimes you get lost because of that, you don't know what's happening and how to do some simple things. Now I'm trying to fill a table with data from 2 related tables. I've read Yii forums, stackoverflow topics, youtube... and nothing works. My configuration is Yii2 advanced.

In postgres, I have two tables:

• table 'tools' with fields 'id(int)' and 'tool_name(varchar)'.
• table 'options' with fields 'id(int)', 'tool_id(int - Foreign Key from tools)', 'option_name(varchar)', 'type(int)'.

The thing is, in my Options/index I want to show the tool name in the table like this (GridView?):

| Tool_name | Option_name | Type |

Another option could be filtering the table through a DropDownList, where I can choose a Tool and updates the table. If no tool is selected, then shows all data. I don't know which option could be simpler, both are valid for me.

Could someone show me some the light please? I'll appreciate it... Thanks!!

Answer 1

The best way to do that is to use the relations implemented in active model.

1) Define relation in your models

If you've used Gii to generate your model classes from your DB then the relations are probably already defined.

It should look like this

class Option extends \yii\db\ActiveRecord
{
    public function getTool()
    {
        return $this->hasOne(Tool::class, ['id' => 'tool_id']);
    }
    
    //... other code of the model
}

Defining relation in model class will allow you to access related model as property of that object. For example the method defined above will allow you to do something like $option->tool->tool_name.

2) Force eager loading for relation

By default relations in Yii uses "lazy loading". That means that the related model is not loaded from DB until it's accessed for the first time. That's fine when you are working with single model because you might or might not use that related model. But if you are going to output multiple models in table and each of them will access the related model the lazy loading will cause extra query for each row of the table. To avoid that we want to say to ActiveQuery that it should load all related model for our tool relation while loading the option models. This is called "eager loading". To do that you have to use with() method when you are creating query for data provider.

For example like this:

$query = Option::find()
  ->with('tool'); //the param is name of relation

3) Add column from related model to GridView

Now you can simply add the column from related model as relation.column in your case it would be tool.tool_name.

Example:

<?= yii\grid\GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        'tool.tool_name',
        'option_name',
        'type',
    ]
]);