CODEWITHSUNDEEP
phpmysqlsql
CdB
CdB

Reputation: 4908

Select rows combining Join & Count

I am building a simple Question/Reply system using php and mysql.
What i am trying to do is display all questions, among with their replies count and the timestamp of the last reply.
Here is the structure of my tables: 

Table 'Discussion':  
id | title | description | user_id | timestamp
--------------------------------------------------------------------------------
Table 'Reply':  
id | reply_text | user_id | discussion_id | timestamp

What i'm doing right now is select the discussions and get the replies count like this: 

SELECT 
     d.*,
     count(dr.id) AS replies_count
FROM discussion d
LEFT JOIN discussion_reply dr ON d.id = dr.discussion_id
GROUP BY d.id

This returns rows like id | title | description | user_id | timestamp | replies_count

Then (via PHP) I loop over the results and query the DB to get the reply with the most recent (max) timestamp for each discussion.
So, for every row returned by the first query, a new query is made.

Is there a way to get all the info i need, just by executing one query?
Like modify my initial query so that it returns:

id | title | description | user_id | timestamp | replies_count | latest_reply_timestamp

Thank you in advance

Upvotes: 0

Views: 83

Answers (1)

John Woo
John Woo

Reputation: 263853

The statement below will give you the latest reply in every discussion.

SELECT  a.id, a.title, a.description, a.user_id, a.timestamp,
        b.id ReplyID, b.reply_text, b.user_id, b.discussion_id, 
        b.timestamp ReplyTimestamp,
        COALESCE(c.totalReplies, 0) TotalReplyCount

FROM    Discussion a
        LEFT JOIN
        (
            SELECT  a.*
            FROM    Reply a
                    INNER JOIN
                    (
                        SELECT  discussion_id, MAX(timestamp) timestamp
                        FROM    Reply
                        GROUP   BY discussion_id
                    ) b ON  a.discussion_id = b.discussion_id AND
                            a.timestamp = b.timestamp
        ) b ON  a.discussion_id = b.discussion_id
        LEFT JOIN
        (
            SELECT  discussion_id, COUNT(*) totalReplies
            FROM    Reply
            GROUP   BY discussion_id    
        ) c ON a.discussion_id = c.discussion_id

Upvotes: 1

Related Questions