CODEWITHSUNDEEP
phpmysqlsql
Aayush
Aayush

Reputation: 3098

Check If Some User ID's Are Friends With The Logged In User In A Single Query

I have a simple friends system on my site. Now I'm trying to create a multi-user group messaging system but only between friends. The "To" values of my messaging system are comma values, I want to check if they all are friend with the person sending the message.

For example, I can get all of a user's friends by the following query:

SELECT relation_setter user_id
FROM users_relationships
WHERE relation_getter = {$logged_in_user}
AND active = 1
UNION
SELECT relation_getter user_id
FROM users_relationships
WHERE relation_setter = {$logged_in_user}
AND active = 1

and I have natasha, reeta as a $_POST['to'] value from my message form which I then convert to user ids and get something like 126152, 112238

Now how do I check if these ids are both friends with the logged in user, in a single query. I don't want to run my is_friend function in a loop, which check if a single userid is friends with the logged in user.

Relationships Schema:

users_relationships

id PK
relation_setter FK user_id
relation_getter FK user_id
active smallint(1)

The relation_setter is the one who sent the friend request. To get all my friends I get all the IDs where my ID is either the relation_setter or relation_getter.

users

user_id PK
name
username
password
etc etc...

Upvotes: 2

Views: 278

Answers (1)

Raythe
Raythe

Reputation: 472

Your post offers vague insight into the schema, so I will use some assumptions

You can find all of the ids that match their friends via an IN statement. Since you already have them as numeric values with comma's you could do:

SELECT user_id
FROM users_relationships
WHERE relation_getter IN (126152,112238,123456)
AND active = 1

This will return ONLY the records of friends that match. You then could match the number of rows with the number of elements in the query to determine if they're friends or not. You could also just send to the ones that matched.

EDIT

SELECT user_id
FROM users_relationships
WHERE relation_getter IN (126152,112238,123456)
OR relation_setter IN (126152,112238,123456)
AND active = 1

This will return all user ID's of the person's friends be they the getter or setter and it's active.

EDIT 2

new table

relationships_members

id FK (from users_relationships; not unique)
user_id

Sample of relationships_members would be

  id  |  user_id  |   relation_setter
--------------------------------------
  1      12345               1  
--------------------------------------
  1      98765               0
--------------------------------------

Then if you queried, you would only receive users_relationships ID that were valid

select distinct a.id, b.user_id as friend
from (
    select distinct id as friend
    from relationships_members
    where user_id = {$logged_in_user}
) a, relationships_members b
WHERE a.id = b.id
and user_id IN (126152,112238,123456)

Upvotes: 1

Related Questions