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/07/22 13:15] – daniel
@@ Line 15: / Line 15: @@
 SELECT mail_id FROM notes WHERE last_changed>=now()-'7 days'::interval
 </code>
+Messages that have 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 that have a lot of recipients in the //To// field (example: more than 20)
+<code sql>
+SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id having count(*)>20
+</code>
+Messages that have a lot of recipients in the //To// or //Cc// fields (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 for which any sender or recipient is 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>
+----
+See the [[http://www.manitou-mail.org/doc/mdx/database-schema.html|database schema documentation]] for definitions of tables and columns.