Manitou-Mail Wiki

Messages that have a private note attached to them:

SELECT mail_id FROM notes

The last 20 messages that have a private note:

SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20

Messages that have a private note which has been added or modified during the last 7 days:

SELECT mail_id FROM notes WHERE last_changed>=now()-'7 days'::INTERVAL

Messages that have one or more pictures attached

SELECT mail_id FROM attachments WHERE content_type LIKE 'image/%'

Outgoing messages that have been composed less than one month ago

SELECT m.mail_id FROM mail m WHERE msg_date>=now()-'1 month'::INTERVAL AND status&256=256

Messages that have a lot of recipients in the To field (example: more than 20)

SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id HAVING COUNT(*)>20

Messages that have a lot of recipients in the To or Cc fields (example: more than 20)

SELECT mail_id FROM mail_addresses WHERE addr_type IN (2,3) GROUP BY mail_id HAVING COUNT(*)>20

Messages for which any sender or recipient is from a given domain

SELECT a.mail_id FROM mail_addresses a JOIN addresses USING(addr_id) WHERE email_addr LIKE '%@example.com'