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