4

I would like to store friendships in a database. My idea is that when user1 becomes friends with user2 I store that friendship so that I can get all of either user's friends if I ever need it. At first I thought I would just store their id's in a table with one insert, but then I thought about some complications while querying the db.

If I have 2 users that have a user id of 10 and 20 should I make two inserts into the db when they become friends

ID USER1 USER2
1  10    20
2  20    10

or is there a way to query the db to only get a particular users friends if I only did one insert like so

ID USER1 USER2
1  10    20

I know the first way can definitely give me what I am looking for but I would like to know if this is good practice and if there is a better alternative. And if the second way can be queried to get me the result I would be looking for like all of user 10's friends.

gmustudent
  • 2,229
  • 6
  • 31
  • 43

2 Answers2

5

A friendship is a two-way bond (for all intents and purposes). Unlike another link (like a message that's one-way) a friendship should only have one entry. However, what you're seeing is correct; you would need to query against both columns to get a user's friends, but that's simple enough:

-- The uses of `1` below is where you'd insert the ID of
-- the person you're looking up friends on
SELECT      u.id, u.name
FROM        friendship f
  LEFT JOIN user u
  ON        (u.id = f.user1 OR u.id = f.user2)
    AND     u.id <> 1
WHERE       (f.user1 = 1 OR f.user2 = 1)

example here

Brad Christie
  • 100,477
  • 16
  • 156
  • 200
5

Brad Christie's suggestion of querying the table in both directions is good. However, given that MySQL isn't very good at optimizing OR queries, using UNION ALL might be more efficient:

( SELECT u.id, u.name
  FROM friendship f, user u
  WHERE f.user1 = 1 AND f.user2 = u.id )
UNION ALL
( SELECT u.id, u.name
  FROM friendship f, user u
  WHERE f.user2 = 1 AND f.user1 = u.id )

Here's a SQLFiddle of it, based on Brad's example. I modified the friendship table to add two-way indexes for efficient access, and to remove the meaningless id column. Of course, with such a tiny example you can't really test real-world performance, but comparing the execution plans between the two versions may be instructive.

Community
  • 1
  • 1
Ilmari Karonen
  • 49,047
  • 9
  • 93
  • 153
  • Quick question. Couldn't I just use Union instead of Union All? Because there should not be any duplicates here right? – gmustudent Dec 28 '12 at 02:32
  • 1
    You could, but `UNION` requires MySQL to actually check for duplicates and remove them if there are any. If you know there won't be duplicates anyway, `UNION ALL` is generally faster. – Ilmari Karonen Dec 28 '12 at 02:34
  • Great advice thank you. & How would limit work with this? Like if I only wanted 10 friends tops. If i added Limit 0,10 to each select would that return 10 in total or 20? Is there a way to only get 10 friends? – gmustudent Dec 28 '12 at 02:41
  • 1
    You can add a `LIMIT` clause to the entire union. However, note that [there might be performance issues](http://stackoverflow.com/questions/870080/why-are-union-queries-so-slow-in-mysql). (I'm not sure if those issues apply also to just `LIMIT` without `ORDER BY`, and in any case there may be differences here between MySQL versions. Try it and see.) The other (ugly) option, of course, is to just add `LIMIT`s to the individual `SELECT`s and to discard any unwanted extra rows at the client. – Ilmari Karonen Dec 28 '12 at 02:47
  • One last question please! I see that you have two tables in your from. I have never seen this before and usually use an inner join with an on clause. is there any benefit to doing it either way b/c I used an inner join to do this but now I came back and looked at your answer and see the difference. thank you – gmustudent Dec 30 '12 at 19:53
  • 1
    There is no real difference: `SELECT * FROM foo JOIN bar ON foo.x = bar.y` and `SELECT * FROM foo, bar WHERE foo.x = bar.y` both produce the same output and the same query plan. I usually prefer the `JOIN` ... `ON` style myself, but in this case I felt that using the other style looked nicer and made the symmetry between the two subqueries more apparent. [Here's a very nice explanation of the different styles](http://code.openark.org/blog/mysql/mysql-joins-on-vs-using-vs-theta-style). – Ilmari Karonen Dec 30 '12 at 20:16