How to verify if someone is a friend of a friend

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

[eluser]CroNiX[/eluser]
In your 2nd query I'd try adding a DISTINCT or GROUP BY clause to eliminate the duplicates.

[eluser]RMinor[/eluser]
I tried putting DISTINCT() and also a GROUP BY on my second query and still get 4.

[eluser]RMinor[/eluser]
Does anybody have any ideas on this?

[eluser]RMinor[/eluser]
After more research I was able to come up with a solution. Here it is and hopefully it can help someone else. If anybody sees anything wrong with it or can improve upon it feel free to let me know.

Code:

public function friends_of_friends($profile_id, $member_id) {
    // Subquery #1
    $this->db->select('profiles.id')
        ->from('profiles')
        ->join('friends', 'friends.to_id = profiles.id OR friends.from_id = profiles.id', 'left')
        ->where('(to_id = ' . $profile_id . ' OR from_id = ' . $profile_id . ')')
        ->where('profiles.id !=', $profile_id)
        ->where('friends.status', 'approved');
    $first_where_clause = $this->db->get_compiled_select();
    // Subquery #2
    $this->db->select('profiles.id')
        ->from('profiles')
        ->join('friends', 'friends.to_id = profiles.id OR friends.from_id = profiles.id', 'left')
        ->where('(to_id = ' . $member_id . ' OR from_id = ' . $member_id . ')')
        ->where('profiles.id !=', $member_id)
        ->where('friends.status', 'approved');
    $second_where_clause = $this->db->get_compiled_select();
    // Main query
    $this->db->select('COUNT(*) AS count')
        ->from('profiles')
        ->where('profiles.id IN (' . $first_where_clause . ')', null, false)
        ->where('profiles.id IN (' . $second_where_clause . ')', null, false);
    $query = $this->db->get();
    return ($query->num_rows() > 0) ? $query->result() : false;
}

A quick note, I had to add a method to the system/database/DB_active_rec.php file in order for my query to work. That method is below.

Code:

public function get_compiled_select($table = '', $reset = TRUE) {
    if ($table != '') {
        $this->_track_aliases($table);
        $this->from($table);
    }
    $select =  $this->_compile_select();
    if ($reset === TRUE) {
        $this->_reset_select();
    }
    return $select;
}