Manitou-Mail Wiki

User Tools

Site Tools

Trace:
sample_user_queries

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Last revisionBoth sides next revision
sample_user_queries [2014/07/22 13:15] – danielsample_user_queries [2014/08/04 11: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 11:23 by daniel