Is there any way of telling how popular a Theme is?

Data & reporting
1

I started messing around in Data Explorer, but I’m not certain the right fields exist on the database?..

The query so far:

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

This doesn’t yet work. (And I’m far from a group and a count)

Something about the implementation I’m missing I suspect …

3 Likes
2

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)

2 Likes
3

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! :smiley: :

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

4

You don’t really need to look at the users table here to display usernames. Data Explorer will automatically display avatars and username if the result of your query has a user_id column which the user_options table does have. :slight_smile:

Anyway, this topic got me personally interested so I spent a few minutes playing with data explorer and I got this little query which shows you user-selectable themes with the number of users using each theme:

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
18 Likes
5

Now that was just showing off @Osama :wink:

Brilliant, thanks!

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

4 Likes
6

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