checking if two users are friends - query advice

[sheardben]

Hi

I would just like some advice on designing a mysql query to check if two users are friends.
Ive got a table 'friends' with columns 'user1' and 'user2'. The difficultly is that a friend connection is represented by one row so if i want to find whether users 45 and 54 are friends i need to search the table for
user1 = 45 AND user2 = 54 OR user1 = 54 AND user2 = 45.
This is fine, but i need to integrate this as a JOIN so at the moment ive got something like:

"SELECT photos.url, photos.caption FROM photos
JOIN friends ON (friend1 = '$userid' && friend2 = photos.userid) || (friend1 = photos.userid && friend2 = '$userid') 

(a simplified example of what i want to do)
So i only want to get photos of users who i am friends with
The above query works, but im my Mysql query stats, this counts as a JOIN query that doesnt use indexs and so the select_full_join variable goes up, and as far as i understand i need to avoid this completely.
What is the best way to go about this? Im sure this isnt an uncommon thing to do or am i going about it completely the wrong way?

Thanks!

[ScallioXTX]

MySQL can't JOIN properly because of the OR in your query.
To overcome this you can use UNION instead

SELECT photos.url, photos.caption FROM photos JOIN friends ON friend1 = '$userid' && friend2 = photos.userid
UNION
SELECT photos.url, photos.caption FROM photos JOIN friends ON friend1 = photos.userid && friend2 = '$userid'

Of course you need indices on friends.friend1, friends.friend2 and photos.userid
I'm guessing (or rather, hoping) you defined friends.friend1, friends.friend2 as the primary key of friends. This is okay.

PS. Union by default removes duplicates. So if you (exactly) have (1,2) and (2,1) in the friends table you only get one result, not two, which is what you want.
So there is no need to use DISTINCT in some way.

[sheardben]

Thanks - so what i have something like this:

SELECT photos.url, photos.caption FROM photos JOIN friends ON friend1 = '$userid' && friend2 = photos.userid
UNION
SELECT photos.url, photos.caption FROM photos JOIN friends ON friend1 = photos.userid && friend2 = '$userid' 

My actual query is quite a bit more substantial with a number of different joins (this one being the only problematic one) so would i just repeat the entire query after UNION?
ie.

SELECT something, else
JOIN ...
JOIN ...
JOIN friends ON friend1 = '$userid' && friend2 = photos.userid
WHERE...
UNION
SELECT something, else
JOIN ...
JOIN ...
JOIN friends ON friend1 = '$userid' && friend2 = photos.userid
WHERE... 

And yes i have defined friend1 and friend2 as unique...

[ScallioXTX]

My actual query is quite a bit more substantial with a number of different joins (this one being the only problematic one) so would i just repeat the entire query after UNION?

Not entirely, you need to swap friend1 and friend2 in the second query to handle both sides of the relation, i.e.

SELECT something, ELSE
JOIN ...
JOIN ...
JOIN friends ON friend1 = '$userid' && friend2 = photos.userid
WHERE...
UNION
SELECT something, ELSE
JOIN ...
JOIN ...
JOIN friends ON friend2 = '$userid' && friend1 = photos.userid
WHERE...

[sheardben]

Oh sorry, meant to change it - i just copied and pasted

Thanks very much though!

[ScallioXTX]

And yes i have defined friend1 and friend2 as unique...

You have defined the combination of the two as unique I hope, otherwise I can only have 2 friends...

[sheardben]

You have defined the combination of the two as unique I hope, otherwise I can only have 2 friends...

Yip...