Please help - can’t figure out how PMs are stored in the database. Is it still in topics/posts? If yes, then how would I identify, which topic is private and for which users?
It uses the same tables but PM topics have an
archetype = 'private_message'. To see who can access that private message, look inside the
topic_allowed_users for the
Here is the query to get all PMs for last 7 days.
SELECT p.user_id, p.topic_id, p.post_number, p.raw, p.created_at::date FROM posts p LEFT JOIN topics t on t.id = p.topic_id WHERE t.archetype = 'private_message' AND t.title <> 'YOUR_WELCOME_MESSAGE_TITLE' AND p.created_at::date > now()::date - 8 ORDER BY p.created_at DESC
YOUR_WELCOME_MESSAGE_TITLEby the exact title of your welcome message, so that all welcome messages generated automatically are excluded (is there a better way to exclude automatically generated messages?)
Adjust how many days to look back for by changing the number in
now()::date - 8.
8 means 7 days;
7 means 6 days
…and so on.
What cool data explorer queries have you come up with?
Who messaged the most?