How to use a SELECT query inside an ON CLAUSE in a LEFT OUTER JOIN in MySQL
"last_active_user" sounds like

( SELECT user_login FROM users WHERE ... ORDER BY ... DESC LIMIT 1 )


count(comments.comment) sounds like

( SELECT COUNT(*) FROM abc_comments WHERE ... )


That is, get rid of the joins and replace the aggregate values by subqueries as above.

JOIN explodes the number of rows, then


You need a GROUP BY to get it back in check
COUNT counts too big a number
Items (such as user_login) that are not in GROUP BY end up with unpredictable values.
Etc.


(Yes, MAX(comment_posted), is the same as, but simpler than, MAX(CAST(comments.comment_posted AS CHAR)).)