Yii eager loading — Fatal error: Out of memory

Question

I have a problem with Yii eager loading. I open user profile page and use:

$model=User::model()->with('routes', 'likes', 'comments', 'questions', 'cityname')->findByPk($id);

Relations is:

public function relations()
    {
        return array(
            'routes'=>array(self::HAS_MANY, 'Route', 'author_id', 'order'=>'routes.id DESC'),
            'questions'=>array(self::HAS_MANY, 'Question', 'author_id', 'order'=>'questions.id DESC'),
            'comments'=>array(self::HAS_MANY, 'Comment', 'author_id', 'order'=>'comments.id DESC',),
            'likes'=>array(self::HAS_MANY, 'Like', 'author_id', 'order'=>'likes.id DESC'),
            'cityname'=>array(self::BELONGS_TO, 'City', 'city'),
        );
    }

When i have around 70 (or more) comments in Comment table, i have error:

Fatal error: Out of memory (allocated 348651520) (tried to allocate 78 bytes) in /home/milk/kolyasya.ru/diplomyii/framework/db/CDbCommand.php on line 516

The interesting part of this problem, is if i comment any element of with(), for example:

$model=User::model()->with('routes', 'likes', 'comments', /* 'questions' */, 'cityname')->findByPk($id);

then all works as it should.

I checked all relations in all models and set ini_set('memory_limit', '512M'), but i can't find a source of the problem.

Maybe I need to use lazy loading?

Answer 1

You're suffering from exploding number of row combinations. Take a look at this question, it describes the same problem on a smaller scale. Basically, you are running a huge query with multiple one-to-many joins, similar to this:

SELECT ... FROM `User` `t` 
  LEFT JOIN `Route` routes ON t.id = routes.author_id
  LEFT JOIN `Question` questions ON t.id = questions.author_id
  LEFT JOIN `Comment` comments ON t.id = comments.author_id
  LEFT JOIN `Like` likes ON t.id = likes.author_id
  LEFT JOIN `City` city ON t.city = city.id
  WHERE t.id = :id
  ORDER BY routes.id DESC, questions.id DESC, comments.id DESC, likes.id DESC

You can take this query, modify it to SELECT COUNT(*) and run it in phpMyAdmin to see how many rows it returns. It will be equal to the number of routes multiplied by the number of questions multiplied by the number of comments multiplied by the number of likes created by this user.

---

In this situation, it would be a lot more efficient to fetch each HAS_MANY relation in a separate query. Yii can do that:

$model=User::model()
  ->with(array(
     'routes' => array('together' => false),
     'likes' => array('together' => false),
     'comments' => array('together' => false),
     'questions' => array('together' => false),
     'cityname' => array(),
  ))
  ->findByPk($id);

If you do so, Yii will instead produce multiple SQL queries with less memory usage, similar to the following:

SELECT ... FROM `User` `t` 
  LEFT JOIN `City` `city` ON `t`.`city` = `city`.`id`
  WHERE `t`.`id` = :id;

SELECT ... FROM `Route` `routes`
  WHERE `author_id` = :id
  ORDER by `routes`.`id` DESC;

SELECT ... FROM `Question` `questions`
  WHERE `author_id` = :id
  ORDER BY `questions`.`id` DESC;

SELECT ... FROM `Comment` `comments`
  WHERE `author_id` = :id
  ORDER BY `comments`.`id` DESC;

SELECT ... FROM `Like` `likes`
  WHERE `author_id` = :id
  ORDER BY `likes`.`id` DESC;

The results will be aggregated and returned to your code just like before.