Create a Friendship system with Mysql.

Lately I have been asked by a client to create a friendship system for his app he is building. System in build with Node.js, Express.js and uses Bookshelf.js with Knex.js as orm/adapter for Mysql. He is using iBeacon minor, major variables, to identify users, as we would normally use ID.

Most of articles in internet are how to build Twitter’s like Following system, mine where mostly Facebook like Friendship.

So I need to have 3 routes.

/friends.json /friend_requests/in.json /friend_requests/out.json

So lets talk about /friends.json first:

Here is my realtions table:

CREATE TABLE `relations` ( `minor_from` int(11) DEFAULT NULL, `major_from` int(11) DEFAULT NULL, `minor_to` int(11) DEFAULT NULL, `major_to` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `type` varchar(11) DEFAULT NULL, KEY `type` (`type`), KEY `minor_from` (`minor_from`), KEY `major_from` (`major_from`), KEY `minor_to` (`minor_to`), KEY `major_to` (`major_to`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Lets insert one friendship

INSERT INTO `relations` (`minor_from`, `major_from`, `minor_to`, `major_to`, `created_at`, `updated_at`, `type`) VALUES (2, 2, 5, 5, ‘2014–06–07 09:37:58', ‘2014–06–07 14:03:21', ‘friend’), (5, 5, 2, 2, ‘2014–06–07 12:13:09', ‘2014–06–07 14:03:24', ‘friend’);

I use Bookshelf.js for Nodejs mysql, but that’s not important now. I will just explain sql queries.

So the challenge is to get all friend of user with minor = 2 and major = 2 and where both of them are friends, otherwise is just friend request.

The sql query will look like this:

select * from `relations` as `L1` where `L1`.`minor_from` = 2 and `L1`.`major_from` = 2 and `L1`.`type`=’friend’ and exists ( select * from `relations` as `L2` where ( L1.`minor_from` = L2.`minor_to` AND L1.`major_from` = L2.`major_to` and L1.`minor_from` = L2.`minor_to` AND L1.`major_from` = L2.`major_to` ) AND ( L2.`minor_from` = L1.`minor_to` AND L2.`major_from` = L1.`major_to` and L2.`minor_from` = L1.`minor_to` AND L2.`major_from` = L1.`major_to` ) )

This would give us one row form db, removed createdat and updatedat for aesthetic purposes.

minor_from major_from minor_to major_to type 2 2 5 5 friend

So we have a friendship, but what about a friend request I sent and friend request people sent to me.

So lets talk about /friend_requests/in.json and /friend_requests/out.json

Let add new row:

INSERT INTO `relations` VALUES (2, 2, 10, 10, ‘2014–06–10 12:26:02', ‘2014–06–10 12:26:08', ‘friend’);

So we made a friend request to user with minor:10 and major:10

Here is the beauty, logic initialised. What makes friend on friends query was and exists select. So if both users have sent friend requests, they are friends, wich mean that if just one use has sent friend request, they are just requests. I copied same query and said NOT exist.

select * from `relations` as `L1` where `L1`.`minor_from` = 2 and `L1`.`major_from` = 2 and `L1`.`type`=’friend’ and NOT exists ( select * from `relations` as `L2` where ( L1.`minor_from` = L2.`minor_to` AND L1.`major_from` = L2.`major_to` and L1.`minor_from` = L2.`minor_to` AND L1.`major_from` = L2.`major_to` ) AND ( L2.`minor_from` = L1.`minor_to` AND L2.`major_from` = L1.`major_to` and L2.`minor_from` = L1.`minor_to` AND L2.`major_from` = L1.`major_to` ) )

This is how we see all friend request that we have sent (/friend_requests/out.json), but what about request sent in.

Lets make users with major:6 and minor:6 sends us a friend request.

INSERT INTO `relations` VALUES (6, 6, 2, 2, ‘2014–06–09', NULL, ‘friend’);

This is same as Out but instead of

select * from relations as L1 where L1.minor_from = 2 and L1.major_from = 2

We say minor_to and major_to. So the whole query would look like this:

select * from `relations` as `L1` where `L1`.`minor_to` = 2 and `L1`.`major_to` = 2 and `L1`.`type`=’friend’ and NOT exists ( select * from `relations` as `L2` where ( L1.`minor_from` = L2.`minor_to` AND L1.`major_from` = L2.`major_to` and L1.`minor_from` = L2.`minor_to` AND L1.`major_from` = L2.`major_to` ) AND ( L2.`minor_from` = L1.`minor_to` AND L2.`major_from` = L1.`major_to` and L2.`minor_from` = L1.`minor_to` AND L2.`major_from` = L1.`major_to` ) )

Basically that’s it. My request was with minor and major but you can easly convert it to use User ID.

If you need more explanation or help let me know. Cheers.