Comecei a brincar no Data Explorer, mas não tenho certeza se os campos corretos existem no banco de dados?..
A consulta até agora:
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
Isso ainda não funciona. (E estou longe de um grupo e de uma contagem)
Suspeito que esteja faltando algo na implementação que estou ignorando…
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)
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?
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
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 )
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.