Im trying to get all pending friend requests from a table with schema:
| id | user_id | friend_id |
|---|
A pending request would just be a single row such as:
(Meaning user 1 sent a request to user 2)
| id | user_id | friend_id |
|---|---|---|
| 1 | 1 | 2 |
and when accepted it becomes two rows, and I am able to join two of this table to find all accepted (this is working just fine).
An accepted request for reference:
| id | user_id | friend_id |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 2 | 1 |
My accepted query looks like this (Im using bookshelf js and knex.js):
const friends = new Friends();
return friends.query((qb) => {
qb.select('friends.user_id', 'friends.friend_id');
qb.join('friends as friendsTwo', 'friends.user_id', 'friendsTwo.friend_id');
qb.where('friends.user_id', '=', id);
}).fetchAll();
How can I modify this to only get the one way relationships? My first thought was leftJoin and I couldnt seem to get it to work, so if anyone knows an answer or has seen a good answer please lead me to it, thanks :).
I think you can completely go away from idea of join in this project. I had done something similar in my previous projects and I think it will be best to add a third field isMutual meaning if they are mutual or not. The field is self-explanatory and think you got the idea. After that your table must look like
| id | user_id | friend_id | isMutual |
|---|---|---|---|
| 1 | 1 | 2 | false |
| id | user_id | friend_id | isMutual |
|---|---|---|---|
| 1 | 2 | 1 | true |
| 2 | 1 | 2 | true |
I believe doing this will actually benefit your system as your query will be faster and try making an compound index for (user_id, friend_id). This solution is more towards shifting the load.
Using this, you can achieve faster queries, but all the hard work done in during READ OPERATION in previous schema will shift to WRITE OPERATION.
But I think this will reduce writing speed by more.
And yeah, make sure you use transaction doing updates for isMutual field.
qb.leftJoin('friends as friendsTwo', 'friends.user_id', 'friendsTwo.friend_id');
qb.whereNull('friendsTwo.user_id');
This will LEFT JOIN, which keeps all rows (pending or accepted), but then filter to keep only those with no matching record in friendsTwo; thus returning only the pending links.