When you use email_in functionality, you should keep track of what emails are configured and associated with a category or a group. This data-explorer query will show you a list of category slug or group name and associated email address so you can copy-paste it into a post to get a clickable list of such associations, e.g., to verify that the description mentions the right email address, or to verify your Mail Transfer Agent setup.
Use with Import: configured-email-addresses.dcquery.json (1.2 KB)
-- Return all configured emails for categories and groups, ordered by name
--
-- Build category slugs
WITH cat_slug AS (
-- Capture category slug to make it clickable in a post
SELECT CONCAT('#', c.slug) AS identifier, c.id
FROM categories c
WHERE c.parent_category_id IS NULL
GROUP BY identifier, c.id
UNION ALL
-- Do this for subcategories as well
SELECT CONCAT('#', p.slug, ':', c.slug) AS identifier, c.id
FROM categories c, categories p
WHERE c.parent_category_id = p.id
GROUP BY identifier, c.id
ORDER BY identifier
)
-- Capture @group, email pairs
SELECT CONCAT('@', g.name) AS name, g.incoming_email AS email
FROM groups g
WHERE g.incoming_email IS NOT NULL
GROUP BY name, email
UNION ALL
-- Capture #category, email pairs
SELECT cs.identifier AS name, c.email_in AS email
FROM categories c, cat_slug cs
WHERE c.id = cs.id AND c.email_in IS NOT NULL
GROUP by cs.identifier, name, email
-- Provide a nice alphabetically ordered list
ORDER BY name