Wow, I sure went overboard with the "friendship chain" thought. I don't know where I got the idea that was your question.
On Thu, Dec 18, 2014 at 3:46 PM, John McKown <john.archie.mck...@gmail.com> wrote: > > On Thu, Dec 18, 2014 at 2:10 PM, Robert DiFalco <robert.difa...@gmail.com> > wrote: > >> I have a table called friends with a user_id and a friend_id (both of >> these relate to an id in a users table). >> >> For each friend relationship there are two rows. There are currently ONLY >> reciprocal relationships. So if user ids 1 and 2 are friends there will be >> two rows (1,2) and (2,1). >> >> For 2 arbitrary ids, I need a query to get two pieced of data: >> * Are the two users friends? >> * How many friends do the two users have in common. >> >> Is there a way to do this with one query? Currently I've only been able >> to figure out how to do it with two. >> >> SELECT >> EXISTS( >> SELECT 1 >> FROM friends >> WHERE user_id = 166324 AND friend_id = 166325) AS friends, >> (SELECT COUNT(1) >> FROM friends f1 JOIN friends f2 ON f1.friend_id = f2.friend_id >> WHERE f1.user_id = 166324 AND f2.user_id = 166325) AS mutual; >> >> I'm wondering if there is a better way to do this using only one query. >> I've tried a couple of GROUP BY approaches but they haven't worked. >> > > This appears, to me, to require a RECURSIVE CTE. Similar to the > description on http://www.postgresql.org/docs/9.1/static/queries-with.html > towards the bottom, when it goes into avoiding loops on parts which are > made up of sub-parts which are themselves sub-parts to other parts. In your > case, this would be to eliminate multiple friendship paths which lead to a > given person. I.e. A friend of B, friend of C, friend of D, friend of B > leading to a recursive loop. In particular, the example: > > WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( > SELECT g.id, g.link, g.data, 1, > ARRAY[g.id], > false > FROM graph g > UNION ALL > SELECT g.id, g.link, g.data, sg.depth + 1, > path || g.id, > g.id = ANY(path) > FROM graph g, search_graph sg > WHERE g.id = sg.link AND NOT cycle > ) > SELECT * FROM search_graph; > > Could be a template for you to start with. Where "id" is the "user_id" > and "link" is the "friend_id". You could use that CTE to create a VIEW > where "search_graph" is "friends_of_friends". I don't have an exact query > for you, sorry. You then use the VIEW to do something like: > > -- number of friends in common: > SELECT COUNT(*) FROM ( > SELECT friend_id FROM friends_of_friends WHERE user_id = 166324 > INTERSECT > SELECT friend_id FROM friends_of_friends WHERE user_id = 166325 > ) > > -- Are two people direct friends: > > SELECT user_id, friend_id FROM friends > WHERE user_id = 16634 AND friend_id = 166325 > OR user_id = 166325 AND friend_id = 166324; > > If you want a "transitive" friendship, use the friends_of_friends view > instead of the friends table. > > -- > > While a transcendent vocabulary is laudable, one must be eternally careful > so that the calculated objective of communication does not become ensconced > in obscurity. In other words, eschew obfuscation. > > 111,111,111 x 111,111,111 = 12,345,678,987,654,321 > > Maranatha! <>< > John McKown > -- While a transcendent vocabulary is laudable, one must be eternally careful so that the calculated objective of communication does not become ensconced in obscurity. In other words, eschew obfuscation. 111,111,111 x 111,111,111 = 12,345,678,987,654,321 Maranatha! <>< John McKown
