It’s time to add followers to our database application.
We will establish one more many-to-many relationship. Our join table will be the followers table.
The interesting thing with this table is that is the two foreign keys are referencing the same table, the users table. Therefore we need to query the followers with a self join.
Create the join table
- The OwnerID is the user who follow another user.
- The FollowingID is the user who is being followed.
CREATE TABLE Follows
(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
OwnerID INTEGER,
FollowingID INTEGER,
FOREIGN KEY (OwnerID) REFERENCES Users(ID),
FOREIGN KEY (FollowingID) REFERENCES Users(ID)
);
Insert data
Ex user with ID 1, Ben follow user with ID 2, Jim.
INSERT INTO Follows (OwnerID, FollowingID) VALUES
(1, 2),
(2, 1),
(2, 3);
Query with self join.
In this query we retrieve all users and the users they follow and then group by user.
SELECT
owner.username, followsUser.Username
FROM Follows f
JOIN Users owner ON f.OwnerID = owner.ID
JOIN Users followsUser ON f.FollowingID = followsUser.ID
GROUP BY owner.username, followsUser.Username;
We can also get all the users a particular user is following.
SELECT
owner.username, followsUser.Username
FROM Follows f
JOIN Users owner ON f.OwnerID = owner.ID
JOIN Users followsUser ON f.FollowingID = followsUser.ID
WHERE owner.Username = 'Ben';
Top comments (0)