Differences

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

--- sample_user_queries [2012/02/17 22:12] – created daniel
+++ sample_user_queries [2014/08/04 11:23] (current) – daniel
@@ Line 1: / Line 1: @@
 ==== 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>
 SELECT mail_id FROM notes
 </code>
-The last 20 messages that have a private note:
+=== The last 20 messages that have a private note ===
 <code sql>
 SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20
 </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>
 SELECT mail_id FROM notes WHERE last_changed>=now()-'7 days'::interval
 </code>
+=== Messages with one or more pictures attached ===
+<code sql>
+select mail_id from attachments where content_type like 'image/%'
+</code>
+=== Outgoing messages that have been composed less than one month ago ===
+<code sql>
+SELECT m.mail_id FROM mail m WHERE msg_date>=now()-'1 month'::interval AND status&256=256
+</code>
+=== Messages with many recipients in To field ===
+(example: more than 20 recipients)
+<code sql>
+SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id having count(*)>20
+</code>
+=== Messages with many recipients in To/Cc field ===
+(example: more than 20)
+<code sql>
+SELECT mail_id FROM mail_addresses WHERE addr_type in (2,3) GROUP BY mail_id having count(*)>20
+</code>
+=== Messages with any sender or recipient from a given domain ===
+<code sql>
+select a.mail_id from mail_addresses a join addresses using(addr_id) where email_addr like '%@example.com'
+</code>
+=== Messages with attachments bigger than a given size ===
+(example: bigger than 1Mb)
+<code sql>
+select mail_id from attachments where content_size > 1024*1024
+</code>
+----
+See the [[http://www.manitou-mail.org/doc/mdx/database-schema.html|database schema documentation]] for definitions of tables and columns.