This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
adv_sample_user_queries_plus [2014/08/31 01:23] daniel |
adv_sample_user_queries_plus [2021/05/15 14: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: === | ||
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&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. | ||