I have the following table schema
user(id, email, pass)
user_contact(user1_id, user2_id, date_requested); // defines user friendship/contacts
user1_id and user2_id is a composite primary key.
I’m not sure how to define a relation named “contacts”, so that when i get an instance of User model I could simply get all the related contacts that belong to that user. I assume that you have to add a sql condition that would do something like “user1_id = :user_id OR user2_id = :user_id” as a part of the relation definition.
$user = User::model()->findByPk(1);
foreach($user->contacts as $contact){
// ... do something with $contact
}
I’ve tried something like this without luck in the relations() method.
'contacts' => array(
self::HAS_MANY, 'User',
'user1_id, user2_id',
'select'=>'id, first_name, last_name',
'through'=> 'userContacts',
'on' => '(userContacts.user1_id = contacts.id) OR (userContacts.user2_id = contacts.id) '
)
Does anyone know how to define this via relations?
If there are no ways to implement it using the Yii’s relations, is it possible to retreive it using CActiveRecord methods, of course without writing plain sql, since I need to have models returned, because they contain various methods that generate urls and paths. Plain sql queries in Yii return arrays not CActiveRecord instances.
This will be my current implementation, since I can’t find any other way.
In User model
public function getContacts(){
$criteria = new CDbCriteria;
$criteria->addCondition('user1_id = :uid');
$criteria->addCondition('user2_id = :uid', 'OR');
$criteria->params = array(':uid' => $this->id);
$result = UserContact::model()->with(array('user1','user2'))->findAll($criteria);
$contacts = array();
foreach($result as $r){
$u = ($r->user1->id != $this->id) ? $r->user1 : $r->user2;
$contacts[] = $u;
}
return $contacts;
}
And in UserContact model
public function relations() {
return array(
'user1' => array(self::BELONGS_TO, 'User', 'user1_id'),
'user2' => array(self::BELONGS_TO, 'User', 'user2_id'),
);
}
Does anyone know any more cleaner way of doing this?
With pagination
public function getContacts($page = 0, $amount = 10){
$criteria = new CDbCriteria;
$criteria->addCondition('user1_id = :uid');
$criteria->addCondition('user2_id = :uid', 'OR');
$criteria->params = array(':uid' => $this->id);
/* Handle pagination */
$count = UserContact::model()->count($criteria);
$pages = new CPagination($count);
$pages->pageSize = $amount;
$pages->currentPage = $page;
$pages->applyLimit($criteria);
// Retrieve the UserContact instances
$result = UserContact::model()->with(array('user1','user2'))->findAll($criteria);
$contacts = array();
foreach($result as $r){
$u = ($r->user1->id != $this->id) ? $r->user1 : $r->user2;
$contacts[] = $u;
}
return $contacts;
}
Thanks for your reply, for a moment I thought I was in a ghost town.
Unfortunately, that doesn’t target the problem I’m facing. I’m not using widgets and the relation is a bit complicated since the second table has a composite key structure and the join should take place ON user1_id OR user2_id. As far as I’ve found out by now, this cannot be achieved using Yii relations. I hope Yii 2.0 would have add this to it’s features, If it’s not a bad practice.
