SQL query: Number of comments posted in last 24 hours by people a user is following
- by bflora
I've got a site where users follow users they like and can read their latest updates.
I'd like to show my users a simple count of how many comments the people they follow have posted in the last 24 hours. I'm having trouble figuring out the query.
I have two tables.
comment
-cid (comment id)
-timestamp
-uid (id of person who posted the comment)
-comment (content of the comment)
user_relationships
-requester_id (id of the user who followed the person)
-requestee_id (id of the person who was followed)
In plain english, I think the query is something like this:
COUNT the cid.comment from the comments table where uid.comment is equal to requestee_id.user_relationships associated with requester_id.user_relationships value of X.
I need to get all the UIDs of people being followed by a given UID from the user_relationship table. Then count up how many comments those people have posted in the last 24 hours and spit that number out.
What would this query look like?