Friends count of my friends

[eluser]JamieBarton[/eluser]
Hey

How are you all doing? Hope all is well.

After a little help, yet again on a query.

What I want to do is count the number of friends my friends have.

I'm using CI's Active Record, it can be written in a query too if necessary.

Basically, when I go to a profile I want to display the friends count of my friends, so in a list view for example:

<b>Friends</b>
- John (California, 30 friends)
- Sophie (United kingdom, 984 friends)

The code I have at the moment is:

Code:

function get_connected_friends($user_id)
{
  $this->db->select('friends.*, users.*, users.id AS friend_user_id');
  
  $this->db->join('users', 'users.id = friends.friend_id');
  
  $this->db->where('friends.user_id', $user_id);
  
  $this->db->where('friends.approved', 1);
  
  return $this->db->get($this->table());
}

My friends table structured:

id / user_id / friend_id / approved


Regards,
Jamie