Combining two queries

Robert DiFalco wrote

For 2 arbitrary ids, I need a query to get two pieced of data:
* Are the two users friends?

This seems easy...ROW(u_id, f_id) = ROW(n1, n2)

* How many friends do the two users have in common.

SELECT f_id FROM [...] WHERE u_id = n1
INTERSECT
SELECT f_id FROM [...] WHERE u_id = n2

Put those into WITH/CTE and use the main query to combine them in whatever
way seems appropriate.

David J.

--
View this message in context: http://postgresql.nabble.com/Combining-two-queries-tp5831378p5831391.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Dec 18, 2014 at 12:10 PM, Robert DiFalco
<robert.difalco@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.

Assuming the friendships are not repeated (that is, if 1 is friends
with 3, then the tuple (1, 3) appears only once), you can find just
the mutual friends by using this one:

(This would be for users 1 and 2):

SELECT friend_id FROM friends WHERE user_id IN (1, 2) GROUP BY
friend_id HAVING count(friend_id) > 1;

You can additionally test if 1 and 2 are friends by doing:

SELECT friend_id FROM friends WHERE user_id IN (1, 2) OR (user_id = 1
AND friend_id = 2) GROUP BY friend_id HAVING (count(friend_id) > 1 OR
friend_id = 1);

If 1 appears in the list, then 1 and 2 are friends. Any other rows are
the mutual friends.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Dec 18, 2014 at 2:10 PM, Robert DiFalco <robert.difalco@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

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.mckown@gmail.com>
wrote:

On Thu, Dec 18, 2014 at 2:10 PM, Robert DiFalco <robert.difalco@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

Is the intersect any better than what I originally showed? On the ROW
approach, I'm not sure where the context for that is coming from since it
may not be in the intersection. Consider n1 and n2 are NOT friends but they
have >0 mutual friends between them.

On Thu, Dec 18, 2014 at 1:29 PM, David G Johnston <
david.g.johnston@gmail.com> wrote:

On Thu, Dec 18, 2014 at 3:02 PM, Robert DiFalco <robert.difalco@gmail.com>
wrote:

Is the intersect any better than what I originally showed? On the ROW
approach, I'm not sure where the context for that is coming from since it
may not be in the intersection. Consider n1 and n2 are NOT friends but they
have >0 mutual friends between them.

​The INTERSECT is a lot more direct about finding mutual friends. The
ROW() = ROW() piece is independent of the mutual friends question - it
should be put in a WHERE clause and you can test whether a row is returned
which, if one is, means the two people are friends.​

​"One Query" does not mean you need to do everything​ all-at-once. I
suggest you make use of CTEs (WITH) subqueries for each distinct
calculation you need then join all of the CTE items together in a final
query the outputs the data in the format desired.

David J.

On Thu, Dec 18, 2014 at 1:57 PM, Robert DiFalco
<robert.difalco@gmail.com> wrote:

Thanks! So how would I combine them so that I would get a single row with
the mutual friend count and isFriends for a given pair of users? I can't
figure out how to modify what you've posted so that it gives the results
like the compound query I quoted in my original post.

On Thu, Dec 18, 2014 at 1:31 PM, Patrick Krecker <patrick@judicata.com>
wrote:

On Thu, Dec 18, 2014 at 12:10 PM, Robert DiFalco
<robert.difalco@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.

Assuming the friendships are not repeated (that is, if 1 is friends
with 3, then the tuple (1, 3) appears only once), you can find just
the mutual friends by using this one:

(This would be for users 1 and 2):

SELECT friend_id FROM friends WHERE user_id IN (1, 2) GROUP BY
friend_id HAVING count(friend_id) > 1;

You can additionally test if 1 and 2 are friends by doing:

SELECT friend_id FROM friends WHERE user_id IN (1, 2) OR (user_id = 1
AND friend_id = 2) GROUP BY friend_id HAVING (count(friend_id) > 1 OR
friend_id = 1);

If 1 appears in the list, then 1 and 2 are friends. Any other rows are
the mutual friends.

(adding back psql-general)

Well it would not be a single SELECT statement anymore :)

There are probably other ways of doing this, I just came up with this
one off the top of my head:

SELECT is_user, c FROM (
SELECT friend_id = USER1 AS is_user, count(friend_id) OVER
(partition BY friend_id = USER1) AS c FROM (
SELECT friend_id FROM friends WHERE user_id IN (USER1, USER2)
OR (user_id = USER1 AND friend_id = USER2) GROUP BY friend_id HAVING
(count(friend_id) > USER1 OR friend_id = USER1)
) AS t1)
AS t2 GROUP BY is_user, c;

It should return 2 rows, one with is_user = t and one with is_user =
f. is_user = t will be present if the two users are friends, and will
always have c = 1. is_user = f will be present if there are mutual
friends, and c will be the number of mutual friends.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The INNER JOIN to itself with a count turns out to have a lower cost query
plan than the INTERSECT approach. On the ROW approach, it also seems to
take longer than the simple EXISTS query. But I suppose I can put both of
those into CTEs for convenience. I guess I was just hoping there was a
lower cost approach than what I was already doing.

On Thu, Dec 18, 2014 at 2:07 PM, David Johnston <david.g.johnston@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.

Hi,
this should do the job, but requires an additional check constraint user_id <> friend_id to be on the safe side:

SELECT count(case when c1=1 then true end)=1 as are_friend,
count(*)-1 as common_friends
FROM
(
SELECT count(*) as c1
FROM friends
WHERE user_id IN (USER1, USER2)
GROUP BY case when user_id = USER2 then USER1 else USER1 end,
friend_id
HAVING COUNT (*) =2
OR COUNT(case when friend_id =USER1 then true end)=1
) q1

regards,
Marc Mamin

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.

Hi,
this should do the job, but requires an aditional check constraint user_id <> friend_id to be on the safe side:

SELECT count(case when c1=1 then true end)=1 as are_friend,
count(*)-1 as common_friends
FROM
(
SELECT count(*) as c1
FROM friends
WHERE user_id IN (USER1, USER2)
GROUP BY case when user_id = USER2 then USER1 else USER1 end,
friend_id
HAVING COUNT (*) =2
OR COUNT(case when friend_id =USER1 then true end)=1
) q1

fix:

SELECT count(case when c1=1 then true end)=1 as are_friend,
count(case when c1=2 then true end) as common_friends
FROM
(
SELECT count(*) as c1
FROM friends
WHERE user_id IN (USER1, USER2)
GROUP BY case when user_id = USER2 then USER1 else USER1 end,
friend_id
HAVING COUNT (*) =2
OR COUNT(case when friend_id =USER1 then true end)=1
) q1

regards,
Marc Mamin