How to correctly build a query in ActiveDataProvider?

Question

Yii

I am new to PHP and Yii2 so I have a lot of questions.
There are two tables
device [id, type_id, name]
device_type [id, type_name, category]
linked by the type_id field
How to correctly select all device records whose device_type.category is equal to a certain value, so that they can then be displayed in GridView ?
After searching and thinking, I got this in the controller

$device = new ActiveDataProvider([
            'query' => Device::find()
                ->from('device')
                ->leftJoin('device_type', 'device_type.id=device.type_id')
                ->where(['device_type.category' => 3]),
        ]);

One more question: if you need to display several identical tables by category, how to get rid of code repetition in the controller?

Answer 1

Good afternoon.
Use relationships for tables.
For example:

public functon getType(){
   return $this->hasOne(Model_name::className(), ['category' => 'type_id'])
}

In the controller ActiveDateProvider() get like this:

$query = Device::find()->with('type')
$device = new ActiveDataProvider([
    'query' => $query
])

In the gridview, you can get the value like this:

[
   'attribute' => 'id'
   'value' => 'type.name'
]

More details here (Working with linked data)

Answer 2

slo_nik Thanks for the answer.
Found the answer from your link.
Maybe someone will come in handy.
To access the fields of a related table in an ActiveDateProvider() query

$device = new ActiveDataProvider([
            'query' => Device::find()
                ->joinWith('type')
                ->where(['device_type.category'=>2]),
        ]);