# 如何判断一个主题的受欢迎程度？

**URL:** <https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2018年八月2日 19:11 UTC](https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796 "2018-08-02T19:11:10Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![merefield](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/merefield/32/176214_2.png) [@merefield](https://meta.discourse.org/u/merefield)\
**Post date:** [2018年八月2日 19:11 UTC](https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796/1 "2018-08-02T19:11:10Z")

</div>

我开始在数据探索器中尝试，但我不确定数据库中是否存在正确的字段？…

目前的查询如下：

`select u.username, t.id, t.name from users u left join user_options o on u.id = o.user_id left join themes t on o.theme_key_seq = t.id where o.theme_key_seq <> 0`

这目前还无法运行。（而且我离分组和计数还很远）

我怀疑是某些实现细节我遗漏了……

---

<div class="post-metadata">

**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\
**Post date:** [2018年八月2日 20:00 UTC](https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796/2 "2018-08-02T20:00:07Z")

</div>

Well, using LEFT JOIN might be returning a lot of NULL fields in the results. Try changing those to simple JOIN instead (INNER JOIN is the Postgres default for JOIN)

---

<div class="post-metadata">

**Author:** ![merefield](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/merefield/32/176214_2.png) [@merefield](https://meta.discourse.org/u/merefield)\
**Post date:** [2018年八月2日 20:08 UTC](https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796/3 "2018-08-02T20:08:56Z")

</div>

Yep, ok, thanks!

So here’s what I’ve got now with some more exploration.

Seems like current default theme is stored in user options field theme\_ids which is an ARRAY … and in the 1th element, so theme\_ids[1].

`select u.username, t.id, t.name from users u inner join user_options o on u.id = o.user_id inner join themes t on o.theme_ids[1] = t.id where theme_ids[1] <> 0`

In my case it’s not even worth grouping and counting because generally users don’t seem to have selected a different default theme to the default, default one - it comes back with two users (out of a regular bunch of 300!), and one of them is … ME! 😃 :

I guess the issue is you can’t see what users have set on their local, by device, settings?

---

<div class="post-metadata">

**Author:** ![Osama](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/osama/32/98013_2.png) [@Osama](https://meta.discourse.org/u/Osama)\
**Post date:** [2018年八月2日 20:51 UTC](https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796/4 "2018-08-02T20:51:04Z")

</div>

> [@merefield](#):
>
> `select u.username, t.id, t.name from users`

You don’t really need to look at the `users` table here to display usernames. [Data Explorer](https://meta.discourse.org/t/32566?silent=true) will automatically display avatars and username if the result of your query has a `user_id` column which the `user_options` table does have. 🙂

Anyway, this topic got me personally interested so I spent a few minutes playing with [data explorer](https://meta.discourse.org/t/32566?silent=true) and I got this little query which shows you user-selectable themes with the number of users using each theme:

```sql
WITH theme_stats AS (
    SELECT theme_ids[1] AS theme_id, COUNT(*)
    FROM user_options
    WHERE array_length(theme_ids, 1) <> 0
    GROUP BY theme_id
)

SELECT count AS "Number of users", themes.name AS "Theme name"
FROM theme_stats
JOIN themes
ON themes.id = theme_stats.theme_id
WHERE themes.user_selectable
ORDER BY "Number of users" DESC

```

---

<div class="post-metadata">

**Author:** ![merefield](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/merefield/32/176214_2.png) [@merefield](https://meta.discourse.org/u/merefield)\
**Post date:** [2018年八月2日 21:02 UTC](https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796/5 "2018-08-02T21:02:31Z")

</div>

Now _that_ was just showing off @Osama 😉

Brilliant, thanks!

(But yeah, sigh, confirms that I only have one user that uses the feature aside from me 😉 )

---

<div class="post-metadata">

**Author:** ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)\
**Post date:** [2018年九月1日 21:02 UTC](https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796/6 "2018-09-01T21:02:34Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.