SQL query to get the queue of all the PMs in the system

Support
1

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?

2

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 topic_id.

2 Likes
3

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

  • Replace YOUR_WELCOME_MESSAGE_TITLE by 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.

4 Likes
4

This topic was automatically closed after 3064 days. New replies are no longer allowed.