Welcome Guest, Not a member yet? Register   Sign In
Friends count of my friends
#1

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

[eluser]rwestergren[/eluser]
Something like this should get you the number of friends:

Code:
function get_connected_friends($user_id)
{
    $query = $this->db->query("
    SELECT COUNT(*) as friend_count
    FROM friends f
    INNER JOIN users u on(u.id = f.friend_id)
    WHERE f.user_id = $user_id AND f.approved = 1
    ");
    return $query;
}
#3

[eluser]JamieBarton[/eluser]
Is it not possible to bind that query to the original query?

So I don't have to run queries on every row.
#4

[eluser]rwestergren[/eluser]
Not sure what you mean by the original query. If you're trying to get the number of friends for all users:
Code:
function get_users()
{
    $query = $this->db->query("
    SELECT *,
            (SELECT COUNT(*) as friend_count
            FROM friends f
            INNER JOIN users u on(u.id = f.friend_id)
            WHERE f.user_id = $user_id AND f.approved = 1) AS f_count
    FROM users u1
    ");
    return $query;
}
#5

[eluser]JamieBarton[/eluser]
I'm trying to get all my friends, join the user table on friend_id and count the number of friends that the friend_id user has...
#6

[eluser]rwestergren[/eluser]
Ahh, I see. This will give you your friends and the count of how many friends each of them have:
Code:
function get_connected_friends($user_id)
{
    $query = $this->db->query("
    SELECT *,
            (SELECT COUNT(*) as friend_count
            FROM friends f2
            WHERE f2.user_id = f.user_id AND f2.approved = 1) AS f_count
    FROM friends f
    INNER JOIN users u on(u.id = f.friend_id)
    WHERE f.user_id = $user_id AND f.approved = 1
    ");
    return $query;
}
#7

[eluser]JamieBarton[/eluser]
[quote author="rwestergren" date="1343230912"]Ahh, I see. This will give you your friends and the count of how many friends each of them have:
Code:
function get_connected_friends($user_id)
{
    $query = $this->db->query("
    SELECT *,
            (SELECT COUNT(*) as friend_count
            FROM friends f2
            WHERE f2.user_id = f.user_id AND f2.approved = 1) AS f_count
    FROM friends f
    INNER JOIN users u on(u.id = f.friend_id)
    WHERE f.user_id = $user_id AND f.approved = 1
    ");
    return $query;
}
[/quote]

That's just giving me the number of friends for me whenever I try to call f_count.
#8

[eluser]rwestergren[/eluser]
Exactly, that's what you asked for. Each row is a friend, each has a column that has the number of friends they have.

Provide some sample data to eliminate confusion.
#9

[eluser]JamieBarton[/eluser]
What I meant was it's saying each of my friends have 4 friends each. Some don't have any, some only have 1, or more than 4.

I have 4 friends however and that is what it is calling.

Sorry for confuse matters.
#10

[eluser]rwestergren[/eluser]
Ahh, I see the error. This should do it:

Code:
function get_connected_friends($user_id)
{
    $query = $this->db->query("
    SELECT u.*,
            (SELECT COUNT(*) as friend_count
            FROM friends f2
            WHERE f2.user_id = u.id AND f2.approved = 1) AS f_count
    FROM friends f
    INNER JOIN users u on(u.id = f.friend_id)
    WHERE f.user_id = $user_id AND f.approved = 1
    ");
    return $query;
}




Theme © iAndrew 2016 - Forum software by © MyBB