Relational Query Question

Yii 1.1 General Discussion
1

I have a question regarding a relational query, which may be easily solved. Firstly, here is my DB structure:




user table:

=============

id

first_name

last_name

etc...


user_follow table:

================

id

user_id

follow_user_id

User model relations:


'followers' => array(self::HAS_MANY, 'UserFollow', 'follow_user_id'),

'following' => array(self::HAS_MANY, 'UserFollow', 'user_id'),

So basically the principle behind this is that users can ‘follow’ other users.

I have created two identical functions in the User model to retrieve data for these relations, below is one:




public function getFollowers()

{

	$data = array();

	

	foreach($this->followers as $user_follow)

	{

		$data[] = $this->findByPk($user_follow->user_id);

	}

	

	return $data;

}

In controller I do the following:


$user = User::model()->findByPk($_GET['id']);


$followers = $user->getFollowers();

So I bring back an array of users (followers) and output them on my view. My questions are:

  1. Is this the most efficient way to retrieve this data?

  2. How can I bring back the list of followers ordered by first name?

2
  1. When outputting the list of followers, I need to display a "follow" or "unfollow" button depending on whether the user is following that user on not. I have created a separate function for this, but again I feel it can be done more efficiently:

public function getIsFollowing($user_id)

{

	$following_array = $this->findByPk($user_id)->following;

	

	$data = array();

		

	foreach($following_array as $row)

	{

		$data[] = $row->follow_user_id;

	}

		

	if(in_array($this->id, $data))

	{

		return true;

	}

		

	return false;

}

View:


<?php if($row->getIsFollowing($user->id) { ?>

	<span class="unfollow-button">Unfollow</span>

<?php } else { ?>

	<span class="follow-button">Follow</span>

<?php } ?>
3

try this in model




'followers' => array(self::HAS_MANY, 'UserFollow', 'follow_user_id','order'=>'first_name,last_name'),
4

That doesn’t work:

Unknown column ‘first_name’ in ‘order clause’.

5

Hi GSTAR,

You could consider using MANY_MANY relation instead of HAS_MANY.




'followers' => array(self::MANY_MANY, 'User', 'user_follow(follow_user_id, user_id)'),

'following' => array(self::MANY_MANY, 'User', 'user_follow(user_id, follow_user_id)'),

Then you will not need getFollowers() and getFollowing() method any more.

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#declaring-relationship

And if you want them to be sorted by first_name by default, then:




'followers' => array(self::MANY_MANY, 'User', 'user_follow(follow_user_id, user_id)', 'order' => 'followers.first_name'),

'following' => array(self::MANY_MANY, 'User', 'user_follow(user_id, follow_user_id)', 'order' => 'following.first_name'),

No.3 is a bit difficult …

6

Thanks softark. I have just made the changes you suggested but I got a slight problem when outputting the relation:


<?php foreach($user->followers as $row) { ?>

    <?php echo $row->first_name; ?>

<?php } ?>

It gives me error:

Property "UserFollow.first_name" is not defined.

7

Seems like ‘followers’ relation is not defined as ‘User’ but ‘UserFollow’.

8

Thanks softark, that has worked.

So do you have any ideas how I can achieve No.3?

9

What about this?




// Check user_follow table directly

public function getIsFollowing($user_id)

{

    $count = UserFollow::model()->countByAttributes(array(

        'user_id' => $user_id,

        'follow_user_id' => $this->id,

    ));

    return $count !== 0;

}