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

[merefield (Robert)]

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 …

[Mittineague]

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)

[merefield (Robert)]

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?

[Osama]

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 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 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

[merefield (Robert)]

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 )