+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Using Active Record for Mutiple Joins on Same Table (/showthread.php?tid=58874)
Using Active Record for Mutiple Joins on Same Table - El Forum - 07-29-2013
[eluser]RMinor[/eluser]
I am trying to retrieve a list of all friends for a particular member of a social networking web site I am working on. The table structure is as follows:
Friends Table: (id, profile_id, friend_id, created_at, updated_at)
Friend Requests Table: (id, to_id, from_id, created_at, updated_at)
When a member sends another member a friend request and it is inserted into the friend request table. If approved, the sender's id will go into the profile_id field and the receiver's id will go into the friend_id field. I want to retrieve all information for both the friend_id and profile_id.
So far I have the following:
Code:
public function get_friends($profile_id) {
$this->db->where('profile_id', $profile_id);
$this->db->or_where('friend_id', $profile_id);
$query = $this->db->get('friends');
return ($query->num_rows() > 0) ? $query->result() : false;
}How would I use the Active Record class to retrieve information for all friends. I basically want the profile_id, first_name, last_name, avatar fields so that I can put them into a table display in my view. Also, is my table structure correct (separate table for friend requests and friends)?
Using Active Record for Mutiple Joins on Same Table - El Forum - 07-30-2013
[eluser]RMinor[/eluser]
Anybody have any ideas?
Using Active Record for Mutiple Joins on Same Table - El Forum - 07-30-2013
[eluser]itz4mesays[/eluser]
Try this:
public function get_friends($profile_id) {
$this->db->where('profile_id', $profile_id);
$this->db->or_where('friend_id', $profile_id);
$query = $this->db->get('friends');
if($query->num_rows()>0)
{
foreach($query->result_array() as $row)
{
$data['']=$row;
}
return $data;
}
}
Using Active Record for Mutiple Joins on Same Table - El Forum - 07-30-2013
[eluser]RMinor[/eluser]
I have no problem retrieving the information from my posted query, but I am not sure how to do the joins to get the information for each friend that corresponds to the profile table.
Using Active Record for Mutiple Joins on Same Table - El Forum - 07-30-2013
[eluser]itz4mesays[/eluser]
This should work for you...
public function get_friends($profile_id) {
$this->db->where(array(‘profile_id’=>$profile_id, 'friend_id'=>$profile_id));
$query = $this->db->get(‘friends’);
if($query->num_rows()>0)
{
foreach($query->result_array() as $row)
{
$data[’‘]=$row;
}
return $data;
}
}
Using Active Record for Mutiple Joins on Same Table - El Forum - 07-30-2013
[eluser]RMinor[/eluser]
Okay thank you. I will give it a shot in a bit.