+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Friends count of my friends (/showthread.php?tid=53466)
Friends count of my friends - El Forum - 07-25-2012
[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
Friends count of my friends - El Forum - 07-25-2012
[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;
}Friends count of my friends - El Forum - 07-25-2012
[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.
Friends count of my friends - El Forum - 07-25-2012
[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;
}Friends count of my friends - El Forum - 07-25-2012
[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...
Friends count of my friends - El Forum - 07-25-2012
[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;
}Friends count of my friends - El Forum - 07-25-2012
[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;
}That's just giving me the number of friends for me whenever I try to call f_count.
Friends count of my friends - El Forum - 07-25-2012
[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.
Friends count of my friends - El Forum - 07-25-2012
[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.
Friends count of my friends - El Forum - 07-25-2012
[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;
}