Greedy download in Yii2, for those who want to understand what it is

The post tells why it is necessary to use "greedy loading", instead of "lazy loading", does not claim to be ideal. And it is unlikely to interest professionals, more suitable for beginners in the study of YII2.

Use the greedy download in your application. Suppose we have two tables with posts and categories. Each post can have one category, with categories 1 or more posts.



')
Suppose we have crooked models, controllers, views on these tables. Fill the list of posts. Please note that in the categories column the category names themselves are displayed, not the category id.



You can display the names of categories in the view: “views / post / index.php” by changing the GridView as follows:

<?php ... use app\models\Category; ?> <?= GridView::widget([ 'dataProvider' => $dataProvider, 'filterModel' => $searchModel, 'columns' => [ ['class' => 'yii\grid\SerialColumn'], 'id', [ 'attribute' => 'category_id', 'filter' => Category::find()->select(['name', 'id'])->indexBy('id')->column(), 'value' => 'category.name', ], 'name', 'content:ntext', ['class' => 'yii\grid\ActionColumn'], ], ]); ?> // 'attribute' => 'category_id',   category_id,   'value' => 'category.name',  name       // 'filter' => Category::find()->select(['name', 'id'])->indexBy('id')->column(), --      // 'value' => 'category.name', 

That's it, here we will find the “greedy download” useful. The fact is that if we look at the sql queries of our application.



We will see a lot of queries from the category table, this type

 SELECT * FROM `sb_category` WHERE `id`=x 

Where x - id from posts - category_id. If we have displayed on page 100, 500, 1000 posts, there will be as many samples in the post table. In this case, if you look at the execution time of the script, queries in the database are executed quickly. But if we can save resources, why not take advantage of it. To do this, create a "greedy download". Greedy loading is loading with redundant data, as if with an attached table.

In the app \ models \ PostSearc model, in the search method, we add a greedy load through the with method

  public function search($params) { $query = Post::find(); 

  public function search($params) { $query = Post::find()->with(['category']); 

After that we look at the result:



Now we see, instead of numerous queries in the database, we get a total of 2. A selection of data from the psot and category tables.
The with method collects all the category_id fields from the post table, and makes one query to the database, retrieving all categories by the WHERE `id` IN (1, 2, 3, 4, 5) condition. Ie in where all id's are listed that are in the first sample post

 SELECT * FROM `sb_post` LIMIT 20 

In order for filtering to work, sorting in a GridView. It is necessary instead of with (), use joinWith ().
joinWith () performs a greedy load and attaches an additional table using join, and this gives us the ability to add sorting conditions.