Обсуждение: Combining two queries
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.
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.
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.
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.SELECTEXISTS(SELECT 1FROM friendsWHERE user_id = 166324 AND friend_id = 166325) AS friends,(SELECT COUNT(1)FROM friends f1 JOIN friends f2 ON f1.friend_id = f2.friend_idWHERE 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
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:
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.SELECTEXISTS(SELECT 1FROM friendsWHERE user_id = 166324 AND friend_id = 166325) AS friends,(SELECT COUNT(1)FROM friends f1 JOIN friends f2 ON f1.friend_id = f2.friend_idWHERE 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 = 166324INTERSECTSELECT friend_id FROM friends_of_friends WHERE user_id = 166325)-- Are two people direct friends:SELECT user_id, friend_id FROM friendsWHERE user_id = 16634 AND friend_id = 166325OR 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,321Maranatha! <><
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
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:
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
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.
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:
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.
>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