SELECT concat('discussion:', m.id) AS objectid, m.thread AS ref, m.id AS ref2, m.message_type AS type, date_format(from_unixtime(m.time),'%Y-%m-%dT%TZ') AS postdate, c.name AS community, '' AS treatment, m.name AS author, m.subject, m2.subject AS subject2, r.name AS rec_name, t.message AS content, count(bt.id) as tag_count, group_concat(DISTINCT REPLACE(ta.tag,' ','_') SEPARATOR ' ') AS tag FROM ( SELECT id FROM jos_sb_messages m WHERE m.time BETWEEN UNIX_TIMESTAMP('$start') AND unix_timestamp('$end') UNION SELECT b.thingid AS id FROM jos_bookmarks_tags AS bt INNER join jos_bookmarks AS b ON (b.id = bt.bookmarkid AND b.type IN (0,1,10,11,12)) WHERE bt.time BETWEEN '$start' AND '$end' ) AS master LEFT JOIN jos_sb_messages AS m ON m.id = master.id LEFT JOIN jos_sb_categories AS c ON c.id = m.catid LEFT JOIN jos_sb_messages_text AS t ON t.mesid = m.id LEFT JOIN jos_sb_messages AS m2 ON (m2.id = m.thread AND m.thread != m.id AND m2.message_type = m.message_type) LEFT JOIN jos_sb_recommends AS r ON (r.id = m.thread AND m.message_type = 3) /* XXX - this doesn't distinguish between recommendation posts properly... */ LEFT JOIN jos_bookmarks AS b ON (b.thingid = ifnull(m.id,r.id) AND b.type IN (0,1,2,10,11,12)) LEFT JOIN jos_bookmarks_tags AS bt ON bt.bookmarkid = b.id LEFT JOIN jos_tags AS ta ON ta.id = bt.tagid INNER JOIN jos_users AS u ON (u.id = m.userid AND u.spammer != 1 AND u.block = 0) INNER JOIN jos_users AS u2 ON (u2.id = m2.userid AND u.spammer != 1 AND u.block = 0) GROUP BY m.id