User Tools

Site Tools


sample_user_queries

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Last revision Both sides next revision
sample_user_queries [2014/07/22 15:15]
daniel
sample_user_queries [2014/08/04 13:13]
daniel
Line 1: Line 1:
 ==== Sample SQL queries ==== ==== Sample SQL queries ====
  
-Messages ​that have a private note attached ​to them:+See the [[http://​www.manitou-mail.org/​doc/​mdx/​database-schema.html|database schema documentation]] for definitions of tables and columns. 
 + 
 +=== Messages ​with a private note attached ​===
 <code sql> <code sql>
 SELECT mail_id FROM notes SELECT mail_id FROM notes
 </​code>​ </​code>​
  
-The last 20 messages that have a private note:+=== The last 20 messages that have a private note ===
 <code sql> <code sql>
 SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20 SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20
 </​code>​ </​code>​
  
-Messages ​that have a private note which has been added or modified during the last 7 days:+=== Messages ​with a private note which has been added or modified during the last 7 days ===
 <code sql> <code sql>
 SELECT mail_id FROM notes WHERE last_changed>​=now()-'​7 days'::​interval SELECT mail_id FROM notes WHERE last_changed>​=now()-'​7 days'::​interval
 </​code>​ </​code>​
  
-Messages ​that have one or more pictures attached+=== Messages ​with one or more pictures attached ​===
 <code sql> <code sql>
 select mail_id from attachments where content_type like '​image/​%'​ select mail_id from attachments where content_type like '​image/​%'​
 </​code>​ </​code>​
  
-Outgoing messages that have been composed less than one month ago+=== Outgoing messages that have been composed less than one month ago ===
 <code sql> <code sql>
 SELECT m.mail_id FROM mail m WHERE msg_date>​=now()-'​1 month'::​interval AND status&​256=256 SELECT m.mail_id FROM mail m WHERE msg_date>​=now()-'​1 month'::​interval AND status&​256=256
 </​code>​ </​code>​
  
-Messages ​that have a lot of recipients in the //To// field (example: more than 20)+=== Messages ​with many recipients in To field === 
 +(example: more than 20 recipients)
 <code sql> <code sql>
 SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id having count(*)>​20 SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id having count(*)>​20
 </​code>​ </​code>​
  
-Messages ​that have a lot of recipients in the //To// or //Cc// fields ​(example: more than 20)+=== Messages ​with many recipients in To/Cc field === 
 +(example: more than 20)
 <code sql> <code sql>
 SELECT mail_id FROM mail_addresses WHERE addr_type in (2,3) GROUP BY mail_id having count(*)>​20 SELECT mail_id FROM mail_addresses WHERE addr_type in (2,3) GROUP BY mail_id having count(*)>​20
 </​code>​ </​code>​
  
-Messages ​for which any sender or recipient ​is from a given domain+=== Messages ​with any sender or recipient from a given domain ​===
 <code sql> <code sql>
 select a.mail_id from mail_addresses a join addresses using(addr_id) where email_addr like '​%@example.com'​ select a.mail_id from mail_addresses a join addresses using(addr_id) where email_addr like '​%@example.com'​
sample_user_queries.txt · Last modified: 2014/08/04 13:23 by daniel