CODEWITHSUNDEEP
mysqlfeedfacebook-wall
mathon12
mathon12

Reputation: 181

DB structure for Twitter home/Facebook wall?

Basically a live feed of all your friends' recent posts.

In a stupid sort of approach I think I'd start by building a query like:

SELECT * FROM tblposts WHERE userid=friend_id_1OR userid=friend_id_2...... and so on

Where friend_id_% is the userid of a friend from your friends list. But this must be a very inefficient way of doing it, right? Is there any faster way of doing this in MySQL? Maybe some clever DB schema?

(I know FB uses Hadoob but I'm not experienced enough to go that far :( )

Upvotes: 1

Views: 897

Answers (2)

mathon12
mathon12

Reputation: 181

Sorry to bump, but I think the query above implies that the friends table has a lot of redundant records? (x is friends with y, y is friends with x; 2 records?)

If friends table has fields sender_id receiver_id and request_status instead, can the query above be modified in a way that it returns friends list against youruserid, i.e. select value of receiver_id if sender_id==youruserid and vice versa so we get one list of all friends of youruserid? I can't think of a better table to avoid duplication.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838116

If you have a list of IDs that you want to query then you should use IN:

SELECT * FROM tblposts WHERE userid IN (friend_id_1, friend_id_2, ...)

But in this case I think you can use a join instead.

SELECT * FROM tblposts AS T1
JOIN (
   SELECT friendid             -- I just made up this query.
   FROM friends                -- You need to replace it with the
   WHERE userid = 'youruserid' -- real query to get someone's friends.
) AS T2
ON T1.userid = T2.friendid

Upvotes: 2

Related Questions