[Joe Storm]

class User extends CActiveRecord
{
    ......
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id'),
        );
    }
}

/* Controller */

$user = User::model()->with(array('posts'))->findbyPk('1');
foreach($user->posts as $post) {
 echo $post->id; 
}

Why is count($user->posts) only 1 and loop only once?

There are multiple records in the db..

[ScallioXTX]

But are those multiple records in the db all from the user with PK 1?

This should show you all posts from all users:

$users = Users::model()->with(array('posts'=>array('order'=>'id')))->findAll(array('order'=>'id'));
forach($users as $user) {
  echo 'User '.$user->id.'<br />';
  foreach($user->posts as $post) {
    echo ' - '.$post->id.'<br />'; 
  }
  echo '<br /><br />';
}

Does that show what you'd expect?

[Joe Storm]

No not what I expected.. it loop through all the users and all show only 1 post..

the post table contain the field author_id and yes there are many record with author_id=1 and the rest of the users.

BUT the post table do not have a PRI key. Is this the cause? what is the workaround?

[ScallioXTX]

No I don't think the post table should have a PK to work (I've not tested it though). Does the Post model maybe have a defaultScope() set that's screwing things up for you?

[Joe Storm]

There is no defaultScope implemented, just clean models..

The relations are
User HAS_MANY Post

But I DID NOT declare the other way round... Post BELONGS_TO User.. does it matter?

[phtamas]

Joe Storm, on 22 June 2011 - 01:21 PM, said:

BUT the post table do not have a PRI key. Is this the cause?

Yes.
When multiple tables are joined, the same row may appear more than once in the result. In order to avoid populating duplicate data, Yii checks the primary key of populated objects. Object will be dropped if it has the same primary key as an already populated object in the same relation. Obviously, this approach will fail when the table doesn't have a primary key.

Joe Storm, on 22 June 2011 - 01:21 PM, said:

what is the workaround?

CActiveRecord::primaryKey()

[ScallioXTX]

phtamas, on 23 June 2011 - 09:55 AM, said:

When multiple tables are joined, the same row may appear more than once in the result. In order to avoid populating duplicate data, Yii checks the primary key of populated objects. Object will be dropped if it has the same primary key as an already populated object in the same relation. Obviously, this approach will fail when the table doesn't have a primary key.

What you're describing is that there would be too many results. The OP has not enough results (i.e. more in the database than Yii finds).

@Joe: no you don't have to specify a relation both ways to make it work; just the one way should suffice.
Could you post a dump of your data somewhere?

[kokomo]

In my opinion it is correct that it return only one row cause the docu says

Quote

Finds a single active record with the specified primary key. See find() for detailed explanation about $condition and $params.

Just use findAll and it should work like you expect it.

See also the ActiveRecord - Reading Record section there it advise to use findAll if multiple rows of data matches the query condition.

This post has been edited by kokomo: 24 June 2011 - 02:15 AM

[ScallioXTX]

Yes, but he uses findByPk for the User model only. The posts (which is where the problem lies!) are retrieved using the HAS_MANY relation from User to Post, which should retrieve all posts from this user, not just the one.

[softark]

I think it's suitable to use the lazy loading aproach in this case:

$user = User::model()->findbyPk('1');
foreach($user->posts as $post) {
 echo $post->id; 
}

[softark]

You'll get a number of redundant row data of the primary table when you use the eager loading approach with a HAS_MANY relation.

$users = Users::model()->with(array('posts'=>array('order'=>'id')))->findAll(array('order'=>'id'));
forach($users as $user) {
  echo 'User '.$user->id.'<br />';
  foreach($user->posts as $post) {
    echo ' - '.$post->id.'<br />'; 
  }
  echo '<br /><br />';
}

@ScallioXTX:
You seem to be expecting something like the following as the output of the above code:

User 1 
  Post 1
  Post 2
  Post 3
  Post 4
User 2
  Post 5
  Post 6
  Post 7
....

But I think you'll get something like this:

User 1
  Post 1
User 1
  Post 2
User 1
  Post 3
User 1
  Post 4
User 2
  Post 5
User 2
  Post 6
User 2
  Post 7
....

[EDIT]
I'm sorry. The argument above IS WRONG. I misunderstood the AR behavior.
Please read the following posts.

[ScallioXTX]

softark, on 24 June 2011 - 03:57 AM, said:

@ScallioXTX:
You seem to be expecting something like the following as the output of the above code:

User 1 
  Post 1
  Post 2
  Post 3
  Post 4
User 2
  Post 5
  Post 6
  Post 7
....

But I think you'll get something like this:

User 1
  Post 1
User 1
  Post 2
User 1
  Post 3
User 1
  Post 4
User 2
  Post 5
User 2
  Post 6
User 2
  Post 7
....

In terms of what is returned from the database, yes. In terms of what is printed on the screen, no. Try it.

[softark]

ScallioXTX, on 24 June 2011 - 09:32 AM, said:

In terms of what is returned from the database, yes. In terms of what is printed on the screen, no. Try it.

Ah, I was wrong. You are right. I misunderstood the behavior of Yii relational AR.

The fact is ...

• An eager loading of HAS_MANY (and MANY_MANY) relation will construct a SQL to return many redundant rows.
  
• But Yii AR will reorganize those resulted rows into something easy to be handled.

Am I right now ?

[ScallioXTX]

softark, on 24 June 2011 - 10:02 AM, said:

Am I right now ?

It doesn't return many redundant rows, but many redundant fields (or columns if you will).
All rows contain useful information, but all rows contain the same information for the fields in the primary table, and only the fields of the secondary table in the rows are useful.
This is not necessarily a bad thing by the way; like most things in programming the question to the answer what would be better/faster, eager or lazy, is "it depends" [on too many things to explain here].

But that's just a technicality. So yeah, you're right

[Joe Storm]

Ok I found the problem. If it was a straight forward User / Post table, most of us would have declared Post with a "id" Primary Key and this HAS_MANY loop problem will never appear.

But no, I was using User/Post as an example of my actual application Site / SiteToModuleMap table. And the old SiteToModuleMap table DO NOT have a Primary Key declared.

So @phtamas is right.

The problem is caused by the lack of primary key, hence my HAS_MANY loop only show 1 result. And the workaround is indeed adding this to the model

  public function primaryKey()
  {
    return "id"; 
    // For composite primary key, return an array like the following
    // return array('pk1', 'pk2');
  }

This is definitely a beginner problem. I will update the title to help others.

Thanks for the effort ScallioXTX and softark!!



from The doc

2. Performing Relational Query

The simplest way of performing relational query is by reading a relational property of an AR instance. If the property is not accessed previously, a relational query will be initiated, which joins the two related tables and filters with the primary key of the current AR instance. 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:

[Dan O'Donnell]

softark, on 24 June 2011 - 03:57 AM, said:

You'll get a number of redundant row data of the primary table when you use the eager loading approach with a HAS_MANY relation.

$users = Users::model()->with(array('posts'=>array('order'=>'id')))->findAll(array('order'=>'id'));
forach($users as $user) {
  echo 'User '.$user->id.'<br />';
  foreach($user->posts as $post) {
    echo ' - '.$post->id.'<br />'; 
  }
  echo '<br /><br />';
}

@ScallioXTX:
You seem to be expecting something like the following as the output of the above code:

User 1 
  Post 1
  Post 2
  Post 3
  Post 4
User 2
  Post 5
  Post 6
  Post 7
....

But I think you'll get something like this:

User 1
  Post 1
User 1
  Post 2
User 1
  Post 3
User 1
  Post 4
User 2
  Post 5
User 2
  Post 6
User 2
  Post 7
....

[EDIT]
I'm sorry. The argument above IS WRONG. I misunderstood the AR behavior.
Please read the following posts.

I'm not sure you are wrong. I have the opposite problem to this poster and I was a) expecting what you thought he was expecting and getting what you thought he'd probably get.

[MR.Ton]

i have used :
public function primaryKey()
{
return 'tid';
}
in model
but also only 1 result in self::HAS_MONY model.