Data Explorer Query CSV Export reports user_id while results display user name

Community Building Data & reporting

AI-generated summary

The discussion revolves around a Data Explorer query that retrieves user custom field data, specifically for users who have entered a Fediverse username. cogdog initially queries the user_custom_fields table and gets the user_id in both the results and CSV export, but wants the actual username displayed. pfaffman suggests joining the users table to map the user_id to the username. cogdog confirms the solution works, noting the query needed an additional AND condition to properly link the user IDs, resulting in the correct username in the CSV export.

1

This is likely an inexperienced query writer (me). I run Data Explorer query to report all users who have entered some text in a custom field (we ask for their Fediverse username):

SELECT ucf.user_id,
       ucf.value,
       ucf.updated_at AS reltime$time
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_9'
AND LENGTH(ucf.value) > 1
ORDER BY ucf.updated_at DESC

It works great, I get results that show the discourse usernames and their entries:

The image shows a table with columns for "user," "value," and "time," containing user names, URLs, and timestamps. (Captioned by AI)
The image shows a table with columns for "user," "value," and "time," containing user names, URLs, and timestamps. (Captioned by AI)1200×538 30 KB

However when I export, I get a user_id in the first column, where I really want their username

query-csv
query-csv1632×502 98.5 KB

(I can see that’s what my query asks for). I am guessing the conversion fro user ID to username is built into discourse? How can I get the username in both the Explorer view and in the CSV exports?

2

Try this:

SELECT u.username, ucf.user_id,
       ucf.value,
       ucf.updated_at AS reltime$time
FROM user_custom_fields ucf, users u
WHERE ucf.name = 'user_field_9'
and ucf.user_id=u.id
AND LENGTH(ucf.value) > 1
ORDER BY ucf.updated_at DESC
4

Thanks! I was close, I forgot to include the additional AND for the condition to connet user IDs. Wow, are my query chops rusty.

It ends up a little redundant in the Data Explorer Results, but now I get the right values in CSV export.