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

Both sides previous revision Previous revision
Next revision Both sides next revision
adv_sample_user_queries_plus [2014/08/31 01:23]
daniel
adv_sample_user_queries_plus [2016/03/13 23:24]
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: ===
Line 22: Line 22:
 </​code>​ </​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&​(16+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>​
 ---- ----
  
 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.txt ยท Last modified: 2021/05/15 14:21 by daniel