What is the best database access method to use in Yii2?

Question

Yii

Hello. I recently started learning the wonderful Yii2 framework. The instructions indicate several ways to select from the database. You can use the query builder, you can use ActiveRecord. In the first case, I can make a query like this:

$data= (new Query())
        ->select(['title', 'text', 'category'])
        ->from('Posts')
        ->leftJoin('category', 'posts.id_categoryes = category.id_category')
        ->all();

and then display information in the view

<?php foreach ($data as $key => $row): ?>
         <?= $row['title']?>
         <?= $row['category']?>
         <?= $row['text']?>
<?php endforeach ?>

In the second case, the query looks like this:
$data = Posts::find()->all();
much more pleasing to the eye and simpler, but there is a pitfall for me, namely the relational relationship described in the model

public function getIdCategoryes()
    {
        return $this->hasOne(Category::className(), ['id_category' => 'id_categoryes']);
    }

due to which the view is a little more complicated

<?php foreach ($data as $key => $row): ?>
          <?= $row['title']?>
          <?= $row->idCategoryes->category;?>
          <?= $row['text']?>
 <?php endforeach ?>

What are the disadvantages I get if I use the query builder? It seems to me more concise, I choose practically on pure sql only the fields I need.

Answer 1

If these are single objects, you can safely use ActiveRecord. The brevity of the code and the convenience of writing and maintaining the code outweighs a small overhead in creating objects (which, in practice, will not play any role).
In general, there is almost no difference between using a "query builder" and using AR, except to use DAO - a small add-on over PDO, which is available through Yii::$app->db (that is, a standard database connection component) .
But if there are dozens and hundreds of objects, I usually use asArray()->all(); to get the result from the table in the form of arrays. This covers 95% of the necessary code for working with databases (well, I think). If you need a lower-level interface, you can always define a method in the AR object that will return the necessary data, and the necessary request will occur inside it.
By the way, I advise you to enable caching of table schemas - it helps a lot in speeding up AR in the future.