# Data Explorer Query CSV Export reports user\_id while results display user name

**URL:** https://meta.discourse.org/t/data-explorer-query-csv-export-reports-user-id-while-results-display-user-name/341019
**Category:** Data & reporting
**Created:** [December 6, 2024, 8:27pm UTC](https://meta.discourse.org/t/data-explorer-query-csv-export-reports-user-id-while-results-display-user-name/341019 "2024-12-06T20:27:07Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![cogdog](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/cogdog/32/116536_2.png) [@cogdog](https://meta.discourse.org/u/cogdog)
#### Post date: [December 6, 2024, 8:27pm UTC](https://meta.discourse.org/t/data-explorer-query-csv-export-reports-user-id-while-results-display-user-name/341019/1 "2024-12-06T20:27:07Z")

</div>

This is likely an inexperienced query writer (me). I run [Data Explorer](https://meta.discourse.org/t/32566?silent=true) query to report all users who have entered some text in a custom field (we ask for their Fediverse username):

```plaintext
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)](https://global.discourse-cdn.com/meta/original/4X/4/5/5/455e8b46659540401dfea3197ce199d4b542237d.jpeg)

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

 ![query-csv](https://global.discourse-cdn.com/meta/original/4X/2/7/c/27c445771127d459d17b9fa6e55c5ed2ab631c4c.jpeg)

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

---

<div class="post-metadata">

### Author: ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [December 6, 2024, 8:33pm UTC](https://meta.discourse.org/t/data-explorer-query-csv-export-reports-user-id-while-results-display-user-name/341019/2 "2024-12-06T20:33:53Z")

</div>

Try this:

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

```

---

<div class="post-metadata">

### Author: ![cogdog](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/cogdog/32/116536_2.png) [@cogdog](https://meta.discourse.org/u/cogdog)
#### Post date: [December 8, 2024, 11:13pm UTC](https://meta.discourse.org/t/data-explorer-query-csv-export-reports-user-id-while-results-display-user-name/341019/4 "2024-12-08T23:13:55Z")

</div>

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](https://meta.discourse.org/t/32566?silent=true) Results, but now I get the right values in CSV export.

---

<div class="post-metadata">

### Author: ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)
#### Post date: [January 7, 2025, 11:14pm UTC](https://meta.discourse.org/t/data-explorer-query-csv-export-reports-user-id-while-results-display-user-name/341019/5 "2025-01-07T23:14:13Z")

</div>

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