Re: Combining two queries

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(dot)archie(dot)mckown(at)gmail(dot)com>
wrote:
>
> On Thu, Dec 18, 2014 at 2:10 PM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)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