adv_sample_user_queries_plus
This is an old revision of the document!
Advanced sample SQL queries
Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag:
SELECT m.mail_id FROM mail m JOIN mail_addresses ma ON (ma.mail_id=m.mail_id) JOIN addresses USING(addr_id) WHERE status&256<>0 AND email_addr LIKE '%@example.org' AND NOT EXISTS (SELECT 1 FROM mail_tags mt JOIN (SELECT tag_id FROM tags WHERE name='Sample Tag') t ON (t.tag_id=mt.tag) WHERE mt.mail_id=m.mail_id) ;
Latest outgoing message per distinct recipient (to which a message was ever addressed):
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
See the database schema documentation for definitions of tables and columns.
adv_sample_user_queries_plus.1409441033.txt.gz · Last modified: 2014/08/30 23:23 by daniel