I have a table named friends with following structure:
id, from_id, to_id, confirmed, ....
This table contains the list of all friends of all users. The from_id and to_id contain ids of the users where from_id is the one who actually sent the friend request.
Now, I need to select the list of all the friends of a particular user into.
Is is better to do it using the following query or a query with union:
"select (from_id + to_id)- $user_id as friend_id, ... from friends where from_id=$user_id or to_id=$user_id"
Although I love your mathematic-fan solution I think it is not considering overflow issues. For example: what would happen if you add two extremely big numbers?
Apart from that, your solution relies on a numeric user_id. Using a union might take a bit more (you should test that to make sure) but will work with any data type.
I would use a
union (make sure you have indexes on
select from_id from friends where to_id = $user_id union select to_id from friends where from_id = $user_id
It's more clear this way in my opinion.
assuming you have keys on the ids the union will certainly be better, then performing the arithmetic operations.