数据浏览器查询CSV导出报告显示user_id，而结果显示用户名

数据与报告
1

这可能是一位经验不足的查询编写者（也就是我）。我运行了一个 Data Explorer 查询，用于报告在自定义字段中输入了某些文本的所有用户（我们要求提供他们的 Fediverse 用户名）：

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

它运行得很好，我得到了显示 discourse 用户名及其输入结果：

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

但是，当我导出时，第一列会显示一个 user_id，而我实际上想要他们的用户名。

query-csv
query-csv1632×502 98.5 KB

（我可以看到这是我的查询所要求的）。我猜从 user ID 转换为用户名是内置在 discourse 中的？如何在 Explorer 视图和 CSV 导出中都获取用户名？

2

试试这个：

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 个赞
4

谢谢！我差一点就成功了，我忘了为条件包含额外的 AND 来连接用户 ID。哇，我的查询技巧生疏了。

它在 Data Explorer Results 中最终有点冗余，但我现在在 CSV 导出中获得了正确的值。

5

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