CodeIgniter Forums
Help with query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: General Help (https://forum.codeigniter.com/forum-24.html)
+--- Thread: Help with query (/thread-71663.html)



Help with query - kirasiris - 09-11-2018

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.


RE: Help with query - php_rocs - 09-11-2018

@kirasiris,

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


RE: Help with query - Pertti - 09-11-2018

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); 



RE: Help with query - php_rocs - 09-11-2018

.....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)


RE: Help with query - kirasiris - 09-11-2018

(09-11-2018, 08:32 AM)Pertti Wrote: 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.


RE: Help with query - kirasiris - 09-11-2018

(09-11-2018, 10:16 AM)php_rocs Wrote: .....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.


RE: Help with query - php_rocs - 09-11-2018

@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.