Search Postgresql Archives

Re: Combining two queries

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux