Welcome Guest, Not a member yet? Register   Sign In
Using Active Record for Mutiple Joins on Same Table
#1

[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)?
#2

[eluser]RMinor[/eluser]
Anybody have any ideas?
#3

[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;
}

}
#4

[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.
#5

[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;
}

}
#6

[eluser]RMinor[/eluser]
Okay thank you. I will give it a shot in a bit.




Theme © iAndrew 2016 - Forum software by © MyBB