Help with query

So I'm currently working with a following model system.

You follow this x-user and x-user may not follow you back.

I have been able to display the users that I'm following but I would like to display a text saying whether x-users follows me back as well or not.

For example I have this on my table:

id, user_id, friend_id, status, type.

and I'm calling these three methods in my controller function:

PHP Code:

// Already friends?
$data['relationship'] = $this->User_model->the_relationship($user->id);

// Get user followings
$data['following']   = $this->User_model->get_followings($user->id);

// Followings follows back Logged In user?
$data['followsback']   = $this->User_model->followsBack($user->id); 


one checks if there's already a relationship between both users(logged in and x-user), the second one gets the x-users which the logged in user is following and the third method should display a text of "Follows you back" and/or "Does not follows you".

Here are the three methods in my model:

PHP Code:

   // Relationship for users
    public function the_relationship($id){    
        $this->db->select('*');
        $this->db->from($this->relationship);
        $this->db->where('user_id', $this->session->userdata('user_id'));
        $this->db->where('friend_id', $id);
        $this->db->where('type', $this->type);

        $query = $this->db->get();

        if($query->num_rows() >= 1){
            return $query->row();
        } else {
            return false;
        }
    }

    // Get user followings
    public function get_followings($id){
        $this->db->select('*');
        $this->db->where('user_id', $id);
        $this->db->where('friend_id !=', $this->session->userdata('user_id'));
        $this->db->where('type', $this->type);
        $this->db->where('status', 'following');
        
        $query = $this->db->get($this->relationship);
        return $query->result();
    }

    // X user follows back Logged In user? --- HERE IS WHERE I'M HAVING TROUBLES
    public function followsBack($id){
        $this->db->select('*');
        //$this->db->where('user_id', $id);
        $this->db->where('type', $this->type);
        $this->db->where('status', 'following');

        $query = $this->db->get($this->relationship);
        return $query->row();
    } 


What would be the correct way to display the text in the view?

Thanks in advance.

NOTE: Do you use Twitter?; the system is similar if not exactly to it. You can see your following users and can see a text of follows you as well depending on each single user.

I do Front-End development most of the time 

@kirasiris,

Did you test your queries in mysql first just to see what the results are (of the last query)?

By the looks of it you are doing it 1 query by for every friend? That unfortunately creates infamous N+1 issue, if you have 100 friends, you are doing 100 DB queries.

The simplest solution is to get all users you follow, then all users that follow you, then find the overlap in PHP side.

PHP Code:

$following = [];
$followers = [];
$followbacks = [];

// get all users that current user is friends with
$q = $this->db->select('friend_id')
    ->where('user_id', $this->session->userdata('user_id'))
    ->get($this->relationship);

foreach ($q->result() as $row) {
    $following[$row->friend_id] = $row->friend_id;
}

// get all users that follow you
$q = $this->db->select('user_id')
    ->where('friend_id', $this->session->userdata('user_id'))
    ->get($this->relationship);

foreach ($q->result() as $row) {
    $followers[$row->user_id] = $row->user_id;
}

$followbacks = array_intersect($following, $followers); 


.....or

He could create a view that pulls all users that he is following and that are following him and have two variables that signify whether he is following them and whether they are following him. (Make the database due the hard work)

By the looks of it you are doing it 1 query by for every friend? That unfortunately creates infamous N+1 issue, if you have 100 friends, you are doing 100 DB queries.

The simplest solution is to get all users you follow, then all users that follow you, then find the overlap in PHP side.

PHP Code:

$following = [];
$followers = [];
$followbacks = [];

// get all users that current user is friends with
$q = $this->db->select('friend_id')
    ->where('user_id', $this->session->userdata('user_id'))
    ->get($this->relationship);

foreach ($q->result() as $row) {
    $following[$row->friend_id] = $row->friend_id;
}

// get all users that follow you
$q = $this->db->select('user_id')
    ->where('friend_id', $this->session->userdata('user_id'))
    ->get($this->relationship);

foreach ($q->result() as $row) {
    $followers[$row->user_id] = $row->user_id;
}

$followbacks = array_intersect($following, $followers); 


Not exactly what I wanted but it helped me to improve some code that I had used before. Thanks tho, I may just leave my query just like that as it is not necessary....just thoufgh it could look nice on the application I'm building.

I do Front-End development most of the time 

.....or

He could create a view that pulls all users that he is following and that are following him and have two variables that signify whether he is following them and whether they are following him. (Make the database due the hard work)

That's exactly what I did, I have two different views, one in which I display the users that I follow and the second one, I dispplay the users following me... Just wanted to add a little extra feature(display a text "follows you back" or "does not follows you") on each single result but it is becoming a bit more complex than what I really was hoping for. I may leave it just like that for now or I may get rid of that feature.

Thanks for the suggestions, tho.

I do Front-End development most of the time 

@kirasiris,

You could join the views into one view and use conditions to kickout the text that you want. If you include the query for the views I can show you what I mean.