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

Data & reporting
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
3 Likes
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.