Feature request: PM status, Read/unread indication

Contribute Feature
2

I’m sure it could be improved and customized with some tweaking, but this query works OK using the Data Explorer plugin

WITH unread_messages AS (SELECT topic_users.user_id AS tuui
    , topic_users.topic_id AS tuti
    FROM topic_users 
    WHERE topic_users.last_read_post_number IS NULL 
    AND topic_users.highest_seen_post_number IS NULL 
    AND topic_users.notifications_reason_id = 2
    )
SELECT unread_messages.tuui
     , users.username
     , unread_messages.tuti
FROM unread_messages, topics, users 
WHERE topics.archetype LIKE 'private_message'
AND topics.subtype LIKE 'system_message'
AND topics.id = unread_messages.tuti
AND users.active 
AND ( users.suspended_till IS NULL
   OR users.suspended_till < NOW() )
AND NOT users.admin
AND NOT users.moderator
AND users.id = unread_messages.tuui 
ORDER BY unread_messages.tuui

tuui		username		tuti
15		TrollGuy1		55
15		TrollGuy1		54
15		TrollGuy1		115
21		PersecutorGuy1		109
1 Like