Ok I have a table with comments. Now i would like to extract for each thread:
- total number of comments
- number of replies after last reply by user
- number of replies after last view by user
Currently I achieve this with the two following statement. I've been thinking about it a bit, but I have trouble figuring out how to combine them in one.
Total number per thread:
SELECT count(thread.id) as numberofcomments FROM thread LEFT JOIN comment ON ( thread.id = comment.threadid AND thread.website_id = comment.websiteid ) WHERE website_id = 2 GROUP BY thread.id, thread.website_id
Number after view and post:
SELECT count( comment.threadid ) AS numberofcomments, thread.id, thread.website_id, thread.title, notification.type, comment. *
FROM thread
LEFT JOIN notification ON ( thread.id = notification.threadid
AND thread.website_id = notification.websiteid
AND notification.user_id =3 )
LEFT JOIN COMMENT ON ( thread.id = comment.threadid
AND thread.website_id = comment.websiteid
AND notification.last_update < comment.created_at )
WHERE website_id =2
GROUP BY thread.id, thread.website_id, notification.type
LIMIT 0 , 30
Cheers,
Thierry
