Init Related Objects After Object Was Initialized

Yii 1.1 General Discussion
1

Hello.

If I have an array of objects which can have a relation, is there a way that related object be initialized in one operation?

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');
2

Why can’t you load them as part of the initial request? As far as I know there’s no inbuilt support for what you’re trying to achieve, but even if there was, it would be messy. The code would have to bind a parameter for each record in the initial set. Something like this:




SELECT * FROM Comment

WHERE PostId IN (:p1, :p2, :p3, ...)

I suppose it could instead fetch every record from the Comment table and process them in PHP, but either way seems wrong. They should really be pulled as part of the initial query so they can be joined within the database engine.

3

Thank you for your response.

I have two reason for this aproach:

  1. Sometime the array of objects (the $posts in example) is a result from a different operation, process, etc, and that operation cannot be altered to include.

  2. I work with objects with lots of relations, and subrelations (I used $posts only to present the problem).

If I work with "with" the resulting query(s) are quite large and slow. JOINS in MySql are slow even on highly optimized tables. If the relations are read in distinct, sequential mode, the queryes are much lighter and execute faster than one big query with lots of slow joins.

4

You must be holding a huge number of records for MySQL to be running slowly with optimised tables. How many records are we talking about roughly?

5

For example I have an 13,000,000+ rows table and one of 83,000,000+ table.

6

I’m guessing you’ll have to write your own implementation. I’m not sure how you can do it without either pulling huge amounts of data into PHP or binding lots of parameters though.