Yii2: creating complex queries using Query vs ActiveRecord

Question

I have these two queries, first written using Active Record, and secondly created using yii\db\Query. My localhost query written with Query is 2-4 miliseconds faster, but harder to write. In addition, a query written using AR will execute several database queries, plus SHOW CREATE TABLE, in general, it looks like 10 or 12 more queries that are executed when I do AR. In addition, AR requires that an AR model be defined for each table in the relationship network, and if you avoid AR, you will get fewer classes / files in your application.

My question is: are you using AR or will you write queries with yii\db\Query? AR is prettier and easier to write, but generates so many requests, is this a problem? I am working on a site where tables have several million rows and about 100,000 site visits occur every day.

Request AR:

return self::find()->select('id, news_users_id')
                   ->with([
                        'newsUsers' => function ($query) {
                            $query->select(['ID', 'cmpid']);
                        },
                    ])
                   ->with([
                        'newsUsers.firme' => function ($query) {
                            $query->select(['id', 'skraceni_naziv']);
                        },
                    ])
                   ->with([
                        'newsUsers.firmeBh' => function ($query) {
                            $query->select(['id', 'skraceni_naziv']);
                        },
                    ])
                   ->with([
                        'newsUsers.firmeOstalo' => function ($query) {
                            $query->select(['id', 'skraceni_naziv']);
                        },
                    ])
                   ->orderBy(['id' => SORT_DESC])
                   ->limit(6)
                   ->all();

Query:

$query = new Query;

$query->select(['club.id',
                'firme.id', 'firme.skraceni_naziv',
                'firme_bh.id', 'firme_bh.skraceni_naziv',
                'firme_ostalo.id', 'firme_ostalo.skraceni_naziv']) 

      ->from('club')
      ->innerJoin('news_users', 'club.news_users_id = news_users.ID')
      ->leftJoin('firme', 'news_users.cmpid = firme.id')
      ->leftJoin('firme_bh', 'news_users.cmpid = firme_bh.id')
      ->leftJoin('firme_ostalo', 'news_users.cmpid = firme_ostalo.id')
      ->orderBy(['club.id' => SORT_DESC])
      ->limit(6);

$command = $query->createCommand();

return $command->queryAll();

Answer 1

First of all, you can cache your schema and avoid SHOW CREATE TABLE queries. Define it using the attribute of enableSchemaCacheyour object db.

-, . , A B, B A A. .

yii2 with() joinWith(). with() 2 , .

, AR - , . .

Answer 2

: ActiveRecord:: find() ActiveQuery, Query. , ActiveRecord:: find() , Query.

: ActiveQuery , 'with', join, db, . ActiveQuery "with" ActiveRecord . Query , ActiveRecord.

, , db. . , - , .