Jhun Jhun - 4 months ago 7
SQL Question

Best way to store followed users

I know the title isn't so describing but it's really hard to find something generic to describe my situation. If someone wants to edit, feel free...

So, I have a postgres database, with a

table. I would like to store the users followed by one user, and I really don't see how I could do this. I would like to do like
SELECT followed_users FROM users WHERE username='username'
and this would return me every usernames, or id, or whatever of each followed users. But I don't see any clean way to do this.

Maybe an example would be more describing:
is following

How to store who
is following?

EDIT: I don't know how many users the user will follow.

Thank you for your help.


Expanding on my comment above, since it got wordy:

Create a new table called something like user_follows with columns like

user_id1 | user_id2 


follower_id | follows_id 

Then you can query:

SELECT t1.username as follower_username, t3.username as following_usernae
FROM users t1
    INNER JOIN user_follows t2 ON t1.user_id = t2.follower_id
    INNER JOIN users t3 ON t2.following_id = t3.user_id
WHERE t1.user_id = <your user>

In the end, think of your tables as "Objects". Then when you are presented with a problem like "How do I add users that are following other users" you can determine if this relationship is a new object, or an attribute of an existing object. Since a user might follow more than one other user than the relationship is not a good attribute for "Users", so it gets its own table user_follows.

Since user_follows is just one type of relationship that two users may have to one another, it might make sense to increase the scope of that object to relationships and store the relationship type as an attribute of the table:

user_id1 | user_id2 | relationship_type

where relationships.relationship_type might have values like follows, student of, sister of etc...

So the new query would be something like:

SELECT t1.username as follower_username, t3.username as following_username
FROM users t1
    INNER JOIN relationships t2 ON t1.user_id = t2.user_id1
    INNER JOIN users t3 ON t2.user_id2 = t3.user_id
WHERE t1.user_id = <your user> AND t2.relationship_type = 'Follows';