CODEWITHSUNDEEP
phpactiverecordyii
humeniuc
humeniuc

Reputation: 291

Init related objects after parent objects were initialized

I have an array of Active Record objects of the same class. That objects have a defined relation. Is there a way to read the related objects in a "bulk" manner after the "parent" objects was initialized?

Example I have defined AR classes Post, User, Comment *Post* have relation owner (BELONGS_TO) to class User and a relation comments (HAS_MANY) to class Comments

I have an array $posts of objects of class Post. For different reason the $posts cannot be initialized from the start with 

'with' => array('owner', 'comments')

If I do this:

foreach ($posts as $post) {
   var_dump($post->owner);
   var_dump($post->comments);
}

For every $post a query whould be made for retriving owner and a query for retriving comments. Which could lead to lots of querys and slow execution of scrips. A better aproach is to read all the related objects of one type in one step.

Does something like this exists for retriving all related objects of a certain relation for an array of objects?:

Post::readRelatedObject($posts, 'owner'); 
Post::readRelatedObject($posts, 'comments');

I need this aproach for a few reasons:

  1. sometime I cannot tell in advance if I need a relation to be initialized.
  2. It could be much faster to read the related objects after the "parents", because joins could be slow.
  3. The cache could be affected, because sometime the "parent" object cannot have the same cache time with the related object, so reading the related objects in a grouped/bulk manner, after the parents objects was extracted from cache could be usefull. Ex: The $post was cached, but I cannot cache last 3 comments along with the post.

Does yii contains something like this? Or an plugin which could help?

Thank you

Upvotes: 2

Views: 343

Answers (3)

Paystey
Paystey

Reputation: 3242

If you're trying to optimize for large result sets like this you're almost always better off rolling your own. The Yii developers even recommend not using activerecord for complex situations like this (very different loading order).

Although

To begin with, I would suggest building your own CDBCriteria based on the IDs of all posts you retrieve. If you do this within the Posts model it's also tied nicely (obvisouly not perfectly) with the criteria defined for the relation.

static public function loadComments(array $ids)
{
    $crit = Comment::model()->dbCriteria;
    $crit->addInCondition('post_id', .....

    .....
    return Comment::model()->findAll($crit);
}

This would give you all comments back for that set of IDs, you could even do some extra processing to index them by the post ID they belong too.

But, really

In your comment you say this 100 posts with 200 comments each could mean loading 2000 (actually 20 000). But if you're loading that many posts + comments on a single page, you're optimizing in the wrong place.

You have a few things to implement before worrying about DB query size in my opinion:

  1. Don't load comments until they're needed, be this on clicking a button, scrolling to that posts' title or simply progressively down the page until it's complete. This way the server only looks up comments for one post at a time, and the user may not even want to see comments for post number 99.

  2. Don't load 100 posts per page, unless they're very small posts (in which case, what's the worry). Progressive scrolling again will help, or even pagination

  3. Filter your results to only show those relevant, there are very likely not 100 posts the user wants to see, and if there's 200 comments, how many users will read more than 10?

If you implement even 1 of these, I think the problem of result set size will simply go away.

However

If you really do need to worry about 20 000 rows, ActiveRecord instantiation should be first on your radar. 20 000 rows is nothing to worry about in any normal setup, but 20 000 active record objects certainly is, even on higher spec systems. You ought to be using DAO if you're dealing with very large and/or complex data sets.

Upvotes: 2

Compleat Guru
Compleat Guru

Reputation: 21

If your main concern is to avoid lazy loading and wanted to retrieve all Post with its related records of owner and comments.

This maybe useful for you.

$criteria = new CDbCriteria();
$criteria->with = array('comments','owner');
$result = $Post->together()->findAll($criteria);

Reference: http://www.yiiframework.com/doc/api/1.1/CActiveRecord#together-detail http://www.yiiframework.com/doc/api/1.1/CDbCriteria#with-detail

Hope this answers your query.

Upvotes: 0

Kai
Kai

Reputation: 39651

I don't know about an extension which could do this. Maybe you can create one yii should know everything about the data to solve this task.

Let's say comment has a post_id which links to the id in the post table.

There are several options to load the related comments of the post.

Option 1: Using the IDs to query the needed comments. Some DBMS restricts the length of the SQL queries so you might have to be careful and don't put to many IDs in the IN clause. Maybe this must be split into multiple queries.

  1. Iterate the given $posts and store the post ids in a list.
  2. Run an SQL query like SELECT * FROM comments WHERE post_id IN ([the IDs from the list]).
  3. Merge the result into your $posts.

Option 2: To fix a possible IN clause restriction use a JOIN instead.

  1. Run a SQL query like SELECT * FROM comments JOIN [the SELECT query for the posts].

Option 3 (my preferred): Depending on your use case it could be the cleanest to just reload all posts with comments and author (by defining with on the criteria in the posts model).

  1. add a conditional with: if ($loadDetails) { criteria->'with' => array('owner', 'comments'); }

Upvotes: 0

Related Questions