CODEWITHSUNDEEP
phpsqldrupal
bflora2
bflora2

Reputation: 753

SQL query: Number of comments posted in last 24 hours by people a user is following

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

user_relationships

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 usr_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?

Upvotes: 0

Views: 419

Answers (4)

ontrack
ontrack

Reputation: 3043

using a join

SELECT
    COUNT(c.cid)
FROM
    user_relationships r
    INNER JOIN comments c ON c.uid=r.requester_id AND c.timestamp > NOW() - INTERVAL 24 HOUR
WHERE
    r.requestee_id={user_id}

Upvotes: 0

kevpie
kevpie

Reputation: 26108

SELECT COUNT(1) 
FROM user_relationships ur 
JOIN comment c ON (ur.requestee_id = c.uid)
WHERE c.timestamp >= (CURRENT_TIMESTAMP - <interval of 1day>) 
  AND ur.requester_id = <visitor>

Also hope this helps.

You'll have to figure out how to create an interval to subtract from the timestamp.

Upvotes: 2

dqhendricks
dqhendricks

Reputation: 19251

join is faster

SELECT COUNT(comments.uid) FROM comments JOIN user_relationships ON user_relationships.requestee_id = comments.uid WHERE user_relationships.requester_id = $some_id

Upvotes: 0

Stefan H
Stefan H

Reputation: 6683

    SELECT COUNT(*)
      FROM Comment
     WHERE uid in (SELECT user_relationships.requesterid
                     FROM user_relationships
                    WHERE user_relationships.requesteeid = @ThisID)

Hope this helps, Cheers,

Upvotes: 0

Related Questions