What are the best ways to form database queries in Yii2?

Question

Yii

Question in principle 2.
1. I have a MySQL database and Yii2, basically it will be necessary to make queries through Inner Join to 3 or more tables. What is the best way to do this through the ActiveRecord findBySql() method and composing your query or through the find() method, ?
At this stage, I did not understand how to implement a bunch in general in this case. Can it be done?

2. I have 3 tables:
-sotr(id; name)
--sotr_podr(id; id_sotr; id_podrazd)
---podrazd(id; podrazd)

In the model I made such a bunch

public function getPodrazd()
    {
        return $this->hasMany(Podrazdelenie::className(), ['id' => 'id_podrazd'])
            ->viaTable('sotr_podr', ['id_sotr' => 'id']);
    }

in the controller I write like this:
$sotrudniki=Sotrudnik::find()->asArray()->all();

however, there is no connection, the data is displayed only from the table sotr

Answer 1

If performance and good code are important to you , then for all readings we do regular SQL queries or use Query Builder from Yii2.
ActiveRecord works with objects , so it has a big performance hit when writing large and complex queries. In addition, in AR objects, not only the data itself, but also: behaviors, events, validation ...
If you take CRUD (Cread Read Update Delete), then use ActiveRecord for CUD (data modification) . And where R (reading) - we use SQL and QB. In this case, you will work with data in the form of an array, or in the form of simple DTOs, to which you yourself “map” the data from the request. DTO will be convenient to use for work. $dto->nameinstead of . Although, if you use the twig template engine, it doesn’t really matter to you. But in PHP, objects (DTOs) will also be convenient for you to use in the editor. With this approach, the code will be cleaner, and performance will be higher!$items['name']