# 查询用户的电子邮件设置

**URL:** <https://meta.discourse.org/t/query-for-users-email-settings/203646>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2021 年9 月 15 日 15:29 UTC](https://meta.discourse.org/t/query-for-users-email-settings/203646 "2021-09-15T15:29:29Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

作者： ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
发布日期： [2021 年11 月 29 日 20:21 UTC](https://meta.discourse.org/t/query-for-users-email-settings/203646/2 "2021-11-29T20:21:08Z")

</div>

是指他们在 [偏好设置/电子邮件](https://meta.discourse.org/my/preferences/emails) 页面上的设置吗？该字段位于 `user_options` 表中（`email_level`），还有一个私信字段（`email_messages_level`）。

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

| **键** | 电子邮件级别 |
| --- | --- |
| 0 | 始终 |
| 1 | 仅在离开时 |
| 2 | 永不 |

```plaintext
-- [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

```

如果我搞砸了，请告诉我。 🙂👍

* * *

**更新：**

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

```plaintext
-- [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

```

---

_[View the full topic](https://meta.discourse.org/t/query-for-users-email-settings/203646)._