mysql - 1 row friendship extra option

Question

I have an app that manage mutual friendship in one single row per relation.

This is my query

SELECT users.id, users.username, users.name, avatar.media_url  FROM users
  LEFT JOIN ( SELECT * FROM media ORDER BY media.id DESC ) avatar ON (avatar.media_author = users.id AND avatar.media_type = 'avatar') 
  WHERE users.id IN(
   (SELECT user_from FROM friends WHERE user_to = 53 AND status = 1 )
   UNION
   (SELECT user_to FROM friends WHERE user_from = 53  AND status = 1)
                    )
GROUP BY users.id 

And this is my table

CREATE TABLE `friends` (
  `user_from` bigint(20) NOT NULL,
  `user_to` bigint(20) NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 pending | 1 accepted | 2 declined | 3 blocked',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `temp_from_to` int(1) DEFAULT '2',
  `temp_to_from` int(11) DEFAULT '2',
  PRIMARY KEY (`user_from`,`user_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

As you said I've added two new field ( temp_from_to - temp_to_from ) where I want to save a value (1, 2 or 3). This value may be different based on relationship direction, for example:

user_1 have a relation with user_2, for the first user the relation has a value of 1, for the second the value of 3. How I can store that data and retrieve it to make sure that the relationship value tha i get and set is based on my logged-in user?

In a few word: I want be sure to have value of temp_from_to in row where the selected user is in user_from field, and temp_to_from in row where the selected user is in user_to field.

Any help is really appreciated.

标签： mysqlsql

Answer 1

You can do this a lot easier by simplifying your table structure.
If you want a table that keeps friends of users then you could do this:

 CREATE TABLE `friends` (
  `user_id` bigint(20) NOT NULL,
  `friend_id` bigint(20) NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 pending | 1 accepted | 2 declined | 3 blocked',
  `strength` tinyint(1) NOT NULL COMMENT '0 weak| 1 normal| 2 strong|',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`friend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The user_id is the ID of the user that requested the friendship and the friend_id is the ID of the user that got the friendrequest.

Get friends of logged in user:
SELECT * FROM friends WHERE (user_id = :loggedinID OR friend_id = :loggedinID) AND status = 1

Get friendrequests of logged in user:
SELECT * FROM friends WHERE friend_id = :loggedinID AND status = 0

Get blocked users of logged in user:
SELECT * FROM friends WHERE friend_id = :loggedinID AND status = 3

Get declined friendrequests of logged in user:
SELECT * FROM friends WHERE friend_id = :loggedinID AND status = 2