I see it as needing a modified version of https://github.com/elkarte/Elkarte/blob/development/sources/subs/Unread.class.php#L258 with COUNT(*) instead of all of the other stuff and a GROUP BY id_board. That is a lot of query for the boardindex.
It would be cool though.
One question: is that posts or topics?
Pretty much something along these lines:
$request = $this->_db->query('substring', '
SELECT
COUNT(*)
FROM {db_prefix}messages AS ms
INNER JOIN {db_prefix}topics AS t ON (t.id_topic = ms.id_topic AND t.id_first_msg = ms.id_msg)
INNER JOIN {db_prefix}messages AS ml ON (ml.id_msg = t.id_last_msg)' . ($join == 'topics' ? '
LEFT JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)' : '
LEFT JOIN {db_prefix}boards AS b ON (b.id_board = ms.id_board)') . '
LEFT JOIN {db_prefix}members AS mems ON (mems.id_member = ms.id_member)
LEFT JOIN {db_prefix}members AS meml ON (meml.id_member = ml.id_member)' . ($this->_have_temp_table ? '
LEFT JOIN {db_prefix}log_topics_unread AS lt ON (lt.id_topic = t.id_topic)' : '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:current_member})') . (!empty($custom_joins) ? implode("\n\t\t\t\t", $custom_joins) : '') . '
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = t.id_board AND lmr.id_member = {int:current_member})
WHERE t.id_board IN ({array_int:boards})
AND t.id_last_msg >= {int:min_message}
AND IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) < ml.id_msg' .
($this->_post_mod ? ' AND ms.approved = {int:is_approved}' : '') .
($this->_unwatch ? ' AND IFNULL(lt.unwatched, 0) != 1' : '') . '
GROUP BY t.id_board
array_merge($this->_query_parameters, array(
'current_member' => $this->_user_id,
'min_message' => $this->_min_message,
'is_approved' => 1,
))
);
Modern, Free, Powerful