Manitou-Mail Wiki

Advanced examples of SQL queries

Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag:

SELECT m.mail_id FROM mail m
JOIN mail_addresses ma ON (ma.mail_id=m.mail_id)
JOIN addresses USING(addr_id)
WHERE status&256<>0
AND email_addr LIKE '%@example.org'
AND NOT EXISTS
  (SELECT 1 FROM mail_tags mt JOIN (SELECT tag_id FROM tags WHERE name='Sample Tag') t ON (t.tag_id=mt.tag) WHERE mt.mail_id=m.mail_id)
;

Latest outgoing message per distinct recipient (to which a message was ever addressed):

SELECT mail_id FROM (SELECT DISTINCT ON (m1.addr_id) m.mail_id,m1.addr_id
 FROM mail_addresses m1 JOIN mail m USING(mail_id)
  WHERE m1.addr_type=2 AND m.status&129=129
  ORDER BY m1.addr_id,m.msg_date DESC) s

Old messages, not archived or trashed, that belong to a thread whose most recent message is older than one month:

SELECT mail_id FROM mail m
WHERE status&32=0 AND status&16 -- intentionally split to benefit from the index on mail.status&32=0
AND msg_date<now()-'1 month'::INTERVAL
AND thread_id IS NOT NULL
AND NOT EXISTS
 (SELECT 1 FROM mail m2
  WHERE m2.thread_id=m.thread_id
    AND m2.msg_date>now()-'1 month'::INTERVAL)

Messages from the thread with the most unprocessed messages

SELECT mail_id FROM mail WHERE thread_id IN
(SELECT thread_id
  FROM mail
   WHERE status&32=0 AND status&16=0
     AND thread_id IS NOT NULL
   GROUP BY thread_id
   ORDER BY COUNT(*) DESC
   LIMIT 1
)

See the database schema documentation for definitions of tables and columns.