CODEWITHSUNDEEP
mysqlsql
brybott
brybott

Reputation: 157

MySQL insert after checking if not exists in another table

Context: I'm trying to set up a system where users can request other users to be their friends. Those users that have pending friend requests can choose to accept or deny said friend requests.

Problem: I would like to set up a MySQL query such that when a user makes a friend request, the query will first check to see if the person making the friend request is already friends with the person they are making the friend request to, before adding the request.

Right now I have a table with pending requests and a table with friends with the structure below:

 +---------+----------+          +-------------+-------------+
 |      friends       |          |       friend_requests     |
 +---------+----------+          +-------------+-------------+
 | userId  | friendId |          | requesterId | confirmerId |
 +---------+----------+          +-------------+-------------+
 |   11111 |   22222  |          |             |             |
 |   22222 |   11111  |          |             |             |
 |   11111 |   33333  |          |             |             |
 |   33333 |   11111  |          +-------------+-------------+
 +---------+----------+

Now lets say user 11111 wants to try and request user 22222 as a friend again. The query should look into the friends table and see that they are already friends (ie. a row exists where userId = 11111 and friendId = 22222) and therefore not insert anything into the friend_request table. If they are not already friends, then a new row should be inserted into friend_requests.

What I've tried so far (and getting a syntax error):

INSERT INTO friend_requests (requesterId, confirmerId) 
    SELECT 11111, 22222 WHERE NOT EXISTS (
      SELECT * FROM friends WHERE userId = 11111 and friendId = 22222
    )

I'm hoping to do this process in one query if possible. Any help in writing this query would be appreciated.

Upvotes: 0

Views: 892

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Don't do it this way! Instead, have the database check the integrity of the friendship. In other words, just create a unique constraint on the userId, friendId pair:

create unique index idx_userId_friendId on friends(userId, friendId)

This will give the database the job of ensuring that there are no duplicates -- a safer alternative then doing it at the application layer. For instance, your query has race conditions if two users befriend each other at the same time.

This does mean that you need to check for errors when you add friends. I would suggest using an on duplicate key set method:

The assignment actually does nothing, except prevent an error when the unique constraint is violated.

Based on your example data, your "friend" relationship is commutative. If so, you should do both inserts at the same time:

INSERT INTO friend_requests (requesterId, confirmerId) 
    SELECT 11111, 22222
    ON DUPLICATE KEY requesterId = VALUES(requesterId);

EDIT:

Oops, those are two different tables. I think this will fix the error as well as allowing you to insert the values only once:

INSERT INTO friend_requests (requesterId, confirmerId) 
    SELECT requesterId, confirmerId
    FROM (SELECT 11111 as requesterId, 22222 as confirmerId) rc
    WHERE NOT EXISTS (SELECT 1
                      FROM friends f
                      WHERE f.userId = rc.requesterId and f.friendId = rc.confirmerId
                     );

Upvotes: 1

Dhaval
Dhaval

Reputation: 2379

In Sql Server I Done This thing 

  if ((SELECT count(*) FROM friends WHERE userId = 11111 and friendId = 22222)=0)
  begin
  INSERT INTO friend_requests (requesterId, confirmerId) 
  end

Upvotes: 0

Related Questions