Differences

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

--- adv_sample_user_queries_plus [2014/08/04 11:14] – daniel
+++ adv_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: ===
@@ Line 14: / Line 14: @@
 </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.