查询用户的电子邮件设置

数据与报告
1

How could I find out how many users have each email setting?

I’m asking as only about a tenth of users watching a topic are being emailed post notifications.

Thank you.

2

是指他们在 偏好设置/电子邮件 页面上的设置吗？该字段位于 user_options 表中（email_level），还有一个私信字段（email_messages_level）。

我认为这个查询可以实现。它会列出关注特定主题的用户，并按电子邮件级别进行计数：

键 电子邮件级别
0 始终
1 仅在离开时
2 永不 
-- [params]
-- int :topic_id

SELECT tu.topic_id AS topic_id,
uo.email_level,
COUNT(*)
FROM user_options uo
JOIN topic_users tu ON tu.user_id = uo.user_id
WHERE tu.notification_level = 3
AND tu.topic_id = :topic_id
GROUP BY topic_id, uo.email_level

如果我搞砸了，请告诉我。 :slightly_smiling_face::+1:

更新：

我认为这个查询也可以实现：（并且显示得更整洁）

-- [params]
-- int :topic_id

SELECT tu.topic_id AS topic_id,
COUNT(CASE WHEN tu.notification_level = 3 THEN 1 END) AS watching,
COUNT(CASE WHEN uo.email_level = 0 THEN 1 END) AS always,
COUNT(CASE WHEN uo.email_level = 1 THEN 1 END) AS only_when_away,
COUNT(CASE WHEN uo.email_level = 2 THEN 1 END) AS never
FROM user_options uo
INNER JOIN topic_users tu ON tu.user_id = uo.user_id
WHERE tu.notification_level = 3
AND tu.topic_id = :topic_id
GROUP BY topic_id
1 个赞