==== Advanced examples of SQL queries ====

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

<code sql>
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)
;
</code>

=== Latest outgoing message per distinct recipient (to which a message was ever addressed): ===
<code sql>
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
</code>

=== Old messages, not archived or trashed, that belong to a thread whose most recent message  is older than one month: ===
<code sql>
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)
</code>
----

=== Messages from the thread with the most unprocessed messages ===
<code sql>
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
)
</code>

See the [[http://www.manitou-mail.org/doc/mdx/database-schema.html|database schema documentation]] for definitions of tables and columns.