Separation Request Degrees

Question

I have a member-member join table. The schema is member_id, friend_id, is_active. I want to create a list of connections between people who are friends of friends. I am not sure how to resolve this issue, not to mention the semi-optimized form.

In the above table, it works so that member_id and friend_id are essentially the same in another table. On my system, these identifiers are usually called member_id, with the exception of this table. For example, suppose my member_id is 21. My number can be in an infinite number of other lines, like either member_id or friend_id, or based on who originally initiated the actual friendship request, and I don’t need redundant data, I would have cheated ranks to basically do the same.

I would like to have a query where I can not only set the degree level (think LinkedIn), but I can also set how many common friends a single person can display (think on Facebook). Here x is the is_active column that I mentioned earlier. This column can be 0 or 1. This is a simple tinyint column that acts as an on / off switch. Any friendships with 1 will be active friendships, while 0 is expecting. I need to base this request on my active friends and their active friends and so on. Where none of my friends' active friends are my active friends.

How can I build such a query (even if I can’t show the separation level and get only the mutual count)? Right now, I can come up with something, but it includes a query after requesting some nested in loops, and yes, I just can’t imagine that this is good for the overall performance or performance of my servers over time.

Answer 1

Here's how to do a search using width and shortest path searches using JOIN. There is no magic in this algorithm, since we use MySQL to find our answer, and we do not include any fantastic search algorithm that uses any heuristics or optimizations.

" " , , " 1 2" " 2 1". is_active, :

:

member_id   friend_id
1           2
1           3
1           4
2           1
2           3
2           5
2           6
3           2
3           1
4           1
5           2
6           2
6           7
7           6
7           8
8           7

1, , 7, ..? 0 , 1 "".

SELECT COUNT(*)
FROM friends f1
WHERE f1.member_id = 1
  AND f1.friend_id = 7

, ?

SELECT COUNT(*)
FROM friends f1
JOIN friends f2
  ON f2.member_id = f1.friend_id
WHERE f1.member_id = 1
  AND f2.friend_id = 7

, ?

SELECT COUNT(*)
FROM friends f1
JOIN friends f2
  ON f2.member_id = f1.friend_id
JOIN friends f3
  ON f3.member_id = f2.friend_id
WHERE f1.member_id = 1
  AND f3.friend_id = 7

...

"1 2", " 2 6" " 6 7", .

(- ), - . , , , .

, 1:

SELECT f2.friend_id
FROM friends f1
JOIN friends f2
  ON f2.member_id = f1.friend_id
LEFT JOIN friends f3
  ON f3.member_id = f1.member_id
  AND f3.friend_id = f2.friend_id
WHERE f1.member_id = 1
  AND f2.friend_id <> f1.member_id // Not ourself
  AND f3.friend_id IS NULL // Not already a friend

Answer 2

... , ( , , / ), .

ex:

select
      case when table.MemberID < table.FriendID
         then table.MemberID else table.FriendID end as FirstPerson,
      case when table.MemberID < table.FriendID
         then table.FriendID else table.MemberID end as SecondPerson
   from
     ...
   where...

,

member ID   Friend ID
1           2
1           3
1           4
2           1
2           3
2           5
3           2
5           2

and you queried for friends / associations with member ID 1 you would start with
1  2
1  3
1  4

but then friendships from ID #2 would return
1  2  (reversal of 2 / 1 entry) would be duplicate
2  3
2  5

then from friendship 3
2  3  (reversal of 3 / 2 entry) would be duplicate

then from friendship 5 from member 2
2  5  (reversal of 5 / 2 entry) would be dupliate

, , , " ", /. "" /, , , , , - .

Answer 3

, coalesce , . :

SELECT COALESCE( (SELECT 1 FROM friends f1 WHERE f1.member_id = 1 AND f1.friend_id = 7 LIMIT 1), (SELECT 2 FROM friends f1 JOIN friends f2 ON f2.member_id = f1.friend_id WHERE f1.member_id = 1 AND f2.friend_id = 7 LIMIT 1) /*, ..ETC* ) as degrees_away