User Tools

Site Tools


adv_sample_user_queries_plus

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
adv_sample_user_queries_plus [2014/07/22 13:18] – created danieladv_sample_user_queries_plus [2021/05/15 12:21] (current) daniel
Line 1: Line 1:
-==== Advanced sample SQL queries ====+ ==== Advanced examples of SQL queries ====
  
-Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag:+=== Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag: ===
  
 <code sql> <code sql>
Line 14: Line 14:
 </code> </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. See the [[http://www.manitou-mail.org/doc/mdx/database-schema.html|database schema documentation]] for definitions of tables and columns.
  
adv_sample_user_queries_plus.1406035113.txt.gz · Last modified: 2014/07/22 13:18 by daniel