From: | John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com> |
---|---|
To: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Combining two queries |
Date: | 2014-12-18 21:46:02 |
Message-ID: | CAAJSdjhYfZ30QhgFdvCC8ujuaET=1R6TqrYRXaNUV-nCuMQObg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | John McKown | 2014-12-18 21:51:58 | Re: Combining two queries |
Previous Message | Patrick Krecker | 2014-12-18 21:31:23 | Re: Combining two queries |