Why is Yii2's ActiveRecord using lots of single SELECTs instead of JOINs?

Question

I'm using Yii2's ActiveRecord implementation in (hopefully) exactly the way it should be used, according to the docs.

Problem

In a quite simple setup with simple relations betweens the tables, fetching 10 results is fast, 100 is slow. 1000 is impossible. The database is extremely small and indexed perfectly. The problem is definitly Yii2's way to request data, not the db itself.

I'm using a standard ActiveDataProvider like:

$provider = new ActiveDataProvider([
    'query' => Post::find(),
    'pagination' => false // to get all records
]);

What I suspect

Debugging with the Yii2 toolbar showed thousands of single SELECTs for a simple request that should just get 50 rows from table A with some simple "JOINs" to table B to table C. In plain SQL everybody would solve this with one SQL statement and two joins. Yii2 however fires a SELECT for every relation in every row (which makes sense to keep the ORM clean). Resulting in (more or less) 1 * 50 * 30 = 1500 queries for just getting two relations of each row.

Question

Why is Yii2 using so many single SELECTs, or is this a mistake on my side ? Addionally, does anybody know how to "fix" this ?

As this is a very important issue for me I'll provide 500 bounty on May 14th.

Answer 1

By default, Yii2 uses lazy loading for better performance. The effect of this is that any relation is only fetched when you access it, hence the thousands of sql queries. You need to use eager loading. You can do this with \yii\db\ActiveQuery::with() which:

Specifies the relations with which this query should be performed

Say your relation is comments, the solution is as follows:

'query' => Post::find()->with('comments'),

From the guide for Relations, with will perform an extra query to get the relations i.e:

SELECT * FROM `post`;
SELECT * FROM `comment` WHERE `postid` IN (....);

To use proper joining, use joinWith with the eagerLoading parameter set to true instead:

This method allows you to reuse existing relation definitions to perform JOIN queries. Based on the definition of the specified relation(s), the method will append one or multiple JOIN statements to the current query.

So

'query' => Post::find()->joinWith('comments', true);

will result in the following queries:

SELECT `post`.* FROM `post` LEFT JOIN `comment` comments ON post.`id` = comments.`post_id`;
SELECT * FROM `comment` WHERE `postid` IN (....);

---

From @laslov's comment and https://github.com/yiisoft/yii2/issues/2379

it's important to realise that using joinWith() will not use the JOIN query to eagerly load the related data. For various reasons, even with the JOIN, the WHERE postid IN (...) query will still be executed to handle the eager loading. Thus, you should only use joinWith() when you specifically need a JOIN, e.g. to filter or order on one of the related table's columns

TLDR:

joinWith = with plus an actual JOIN (and therefore the ability to filter/order/group etc by one of the related columns)

Answer 2

In order to use relational AR, it is recommended that primary-foreign key constraints are declared for tables that need to be joined. The constraints will help to keep the consistency and integrity of the relational data.

Support for foreign key constraints varies in different DBMS. SQLite 3.6.19 or prior does not support foreign key constraints, but you can still declare the constraints when creating tables. MySQL’s MyISAM engine does not support foreign keys at all.

In AR, there are four types of relationships:

• BELONGS_TO: if the relationship between table A and B is one-to-many, then B belongs to A (e.g. Post belongs to User);
• HAS_MANY: if the relationship between table A and B is one-to-many, then A has many B (e.g. User has many Post);
• HAS_ONE: this is special case of HAS_MANY where A has at most one B (e.g. User has at most one Profile);
• MANY_MANY: this corresponds to the many-to-many relationship in database. An associative table is needed to break a many-to-many relationship into one-to-many relationships, as most DBMS do not support many-to-many relationship directly. In our example database schema, the tbl_post_category serves for this purpose. In AR terminology, we can explain MANY_MANY as the combination of BELONGS_TO and HAS_MANY. For example, Post belongs to many Category and Category has many Post.

The following code shows how we declare the relationships for the User and Post classes.

class Post extends CActiveRecord
{
    ......

    public function relations()
    {
        return array(
            'author'=>array(self::BELONGS_TO, 'User', 'author_id'),
            'categories'=>array(self::MANY_MANY, 'Category',
                'tbl_post_category(post_id, category_id)'),
        );
    }
}

class User extends CActiveRecord
{
    ......

    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id'),
            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
        );
    }
}

The query result will be saved to the property as instance(s) of the related AR class. This is known as the lazy loading approach, i.e., the relational query is performed only when the related objects are initially accessed. The example below shows how to use this approach:

// retrieve the post whose ID is 10
$post=Post::model()->findByPk(10);
// retrieve the post's author: a relational query will be performed here
$author=$post->author;

You are somehow doing it the wrong please go through from the documentaion here http://www.yiiframework.com/doc/guide/1.1/en/database.arr