查询 user_custom_fields

社区 数据与报告
,
1

我们的市场部门需要一些数据。我可以查询 Discourse 来获取。不过，我在处理 user_custom_fields 时遇到了一些麻烦：我们有几个自定义字段，每个字段在表中似乎都是每个用户的一行。因此，每个用户可能会有 6-7 行。我正在尝试进行连接查询，但结果导致每条记录出现多行，所以我需要弄清楚如何正确地进行查询。

这是我目前的内容：

SELECT
    u.id,
    u.username_lower AS "username",
    u.created_at,
    u.last_seen_at,
    u.ip_address,
    ue.email,
    (SELECT COUNT(*)
        FROM   user_badges ub
        WHERE ub.user_id = u.id
        ) AS badge_count
FROM users u
LEFT OUTER JOIN user_emails ue on u.id = ue.user_id
LEFT OUTER JOIN user_custom_fields ucf on u.id = ue.user_id
WHERE u.active = true
AND u.username_lower='slackmoehrle' 
ORDER BY u.id;

user_custom_fields 的表结构如下：

38%20PM
38%20PM3212×810 261 KB

理想情况下，我希望每个用户只返回一条记录，显示我需要的字段，并包含 user_custom_fields 中各行的值。

关于连接方式或语法，有什么建议吗？

2

You could try using WITH queries to define temporary tables for your user custom fields. As an example, I have User Fields for phone number and address. I know that the phone number field is user_field_1 in my database and address is user_field_2. Here’s a query that will return the user’s email address, phone number, and street address, with one row for each user:

WITH user_field_1 AS (
SELECT ucf.value,
ucf.user_id
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_1'
),
user_field_2 AS (
SELECT ucf.value,
ucf.user_id
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_2'
)

SELECT
u.id AS user_id,
ue.email,
uf1.value AS phone_number,
uf2.value AS address
FROM users u
LEFT JOIN user_field_1 uf1
ON uf1.user_id = u.id
LEFT JOIN user_field_2 uf2
ON uf2.user_id = u.id
JOIN user_emails ue
ON ue.user_id = u.id

The easiest way I know of to find the name value of your user fields is to view the json of your user fields page (/admin/customize/user_fields.json). You’ll see the id for each field in the json data. A field with the id of 1 creates a user_custom_field with the name user_field_1. A field with the id of 2 creates a user_custom_field with the name user_field_2.

3

This looks promising. I will work with it and see how I make out. Thank you for taking time out of your day to answer my post.

EDIT: This is the perfect solution. I integrated this to my existing work and things are performing great.