adv_sample_user_queries_plus
Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
adv_sample_user_queries_plus [2014/07/22 15:18] daniel created |
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: === |
| <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 15:18 by daniel
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
