[eluser]RMinor[/eluser]
I know this is more of a MySQL question, but I haven't had much luck on any other forum so I am going to ask it here. I want to determine if a member is a friend of a friend of a profile they are viewing. My (shortened) profiles table looks like this:
Code:
id, first_name, last_name, etc. (I have sample profiles with id's of 1, 2, and 3 for testing)
and my friends table looks like this:
Code:
CREATE TABLE `friends` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`to_id` int(11) NOT NULL,
`from_id` int(11) NOT NULL,
`status` char(10) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
INSERT INTO `friends` (`id`, `to_id`, `from_id`, `status`, `created_at`, `updated_at`) VALUES
(10, 2, 3, 'pending', '2013-08-10 00:00:00', '2013-08-10 00:00:00'),
(11, 1, 3, 'approved', '2013-08-11 00:00:00', '2013-08-11 00:00:00'),
(12, 2, 1, 'approved', '2013-08-15 20:51:20', '2013-08-15 20:51:20');
I can retrieve all friends with this query just fine:
Code:
public function get_all_friends($profile_id) {
$this->db->select('friends.id AS friend_id, profiles.id AS profile_id, profiles.username, profiles.first_name, profiles.last_name, profiles.thumbnail, countries.name AS country, profile_types.name AS type')
->join('friends', 'friends.to_id = profiles.id OR friends.from_id = profiles.id', 'left')
->join('profile_types', 'profiles.profile_type_id = profile_types.id', 'inner')
->join('countries', 'profiles.country_id = countries.id', 'inner')
->where('(to_id = ' . $profile_id . ' OR from_id = ' . $profile_id . ')')
->where('profiles.id !=', $profile_id)
->where('friends.status', 'approved');
$query = $this->db->get('profiles');
return ($query->num_rows() > 0) ? $query->result() : false;
}
I am not sure what would be the best, most efficient way to retrieve a count of all of my friends that are in an array of the profile I am viewing's friends. Maybe something like if my profiles.id is 1 and I am viewing profiles.id of 3.
Code:
SELECT COUNT(*) FROM profiles
LEFT JOIN friends ON (profiles.id = friends.to_id OR profiles.id = friends.from_id)
WHERE (friends.to_id = 1) OR (friends.from_id = 1)
AND profiles.id IN (
SELECT profiles.id
FROM profiles
LEFT JOIN friends ON (profiles.id = friends.to_id OR profiles.id = friends.from_id)
WHERE (friends.to_id = 3) OR (friends.from_id = 3)
)
AND friends.status = 'approved';
When I run this in my SQL editor I get 4 as the result. The result should be 1 since both member 1 and member 3 are friends with member 2. Anybody have any ideas on how to get this working? Thanks in advance.
