CODEWITHSUNDEEP
phpmysql
Raouf Athar
Raouf Athar

Reputation: 1813

MySql Query to get friend list from Friends table

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"

Upvotes: 1

Views: 3903

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

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.

Upvotes: 1

David Chan
David Chan

Reputation: 7505

assuming you have keys on the ids the union will certainly be better, then performing the arithmetic operations.

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

I would use a union (make sure you have indexes on from_id and to_id):

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.

Upvotes: 1

Related Questions