3

I'm currently representing friends in a table called relations, which has two columns (userA, userB) which are the user IDs of two users. If user 1 and user 3 are friends, I'd insert (1, 3) and (3, 1) into this table.

I also have a table called posts, which has, among other fields, an id and an ownerId.

So something like this:

relations:
 userA | userB
   1   |   3
   3   |   1
   3   |   4
   4   |   3
   4   |   2
   2   |   4

posts:
 id | ownerId | ...
  1 |    2    | ...
  2 |    3    | ...
  3 |    4    | ...

I'd like to select every post whose owner is a friend or friend of friend.

For example, if the user the query was selecting for was userId=1, the query should return posts 2 and 3, since userId=3 is a friend, and userId=4 is a friend of a friend).

I've come up with the following, but was wondering if there are better ways to do this (that don't involve a UNION).

SELECT posts.id FROM posts
INNER JOIN relations ON relations.userA=[userId]
WHERE relations.userB=posts.ownerId
UNION
SELECT posts.id FROM posts
INNER JOIN relations AS r1 ON r1.userA=[userId]
INNER JOIN relations AS r2 ON r2.userA=r1.userB
WHERE r2.userB=posts.ownerId AND r2.userB<>[userId];
  • 1
    What is wrong with UNION? What indexes do you have? Maybe adding a certain composite index is all you need. – Rick James Dec 20 '16 at 23:05
1

It is possible to solve this with a query that does not have a single UNION in it:

SELECT
  p.id
FROM
  relations AS r1
  INNER JOIN relations AS r2 ON r1 ON r1.userB = r2.userA
  INNER JOIN posts AS p ON p.ownerId IN (r1.userB, r2.userB)
WHERE
  r1.userA = @userID
  AND r2.userB <> @userID
;

The optimiser, however, might still internally rewrite your query to something very similar to a UNION query, so in the end the performance might not be much better, if at all, than that of query with an explicit UNION.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.