尝试查询已填写的用户自定义字段

社区 数据与报告
,
1

我有一个数据探索器查询，试图查找所有填写了 user custom field 1-4 的用户，并希望仅显示每个用户拥有全部 4 个字段作为列的记录，但我的 PostgreSQL 技能 有限，一直未能解决：

SELECT user_id, name, value
FROM user_custom_fields
WHERE name IN ('user_field_1','user_field_2','user_field_3','user_field_4')
    AND value IS NOT NULL
ORDER BY user_id ASC, name ASC
3 个赞
3

I’m sure there are better ways of approaching this, but this should work to give you a single row containing all of the fields for each user who has filled out at least one of your user fields.

WITH user_field_1 AS (
SELECT ucf.value,
ucf.user_id
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_1'
AND ucf.value IS NOT NULL
AND ucf.value != ''
),
user_field_2 AS (
SELECT ucf.value,
ucf.user_id
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_2'
AND ucf.value IS NOT NULL
AND ucf.value != ''
),
user_field_3 AS (
SELECT ucf.value,
ucf.user_id
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_3'
AND ucf.value IS NOT NULL
AND ucf.value != ''
),
user_field_4 AS (
SELECT ucf.value,
ucf.user_id
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_4'
AND ucf.value IS NOT NULL
AND ucf.value != ''
),
target_fields AS (
SELECT
u.id AS user_id,
uf1.value AS user_field_1,
uf2.value AS user_field_2,
uf3.value AS user_field_3,
uf4.value AS user_field_4
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
LEFT JOIN user_field_3 uf3
ON uf3.user_id = u.id
LEFT JOIN user_field_4 uf4
ON uf4.user_id = u.id
)

SELECT
*
FROM target_fields tf
WHERE tf.user_field_1 IS NOT NULL
OR tf.user_field_2 IS NOT NULL
OR tf.user_field_3 IS NOT NULL
OR tf.user_field_4 IS NOT NULL
ORDER BY tf.user_id
4 个赞
4

If there is a better way I am so ok with trying/doing it! I’ll give this a shot though.

1 个赞
5

By ‘better’ I mean more elegant and more efficient. This should give you the results that you are looking for. I was stuck for a while on how to get the results in a single row for each user. If that isn’t a requirement, the query could be simplified a lot.

1 个赞
6

此主题已在 2230 天后自动关闭。不再允许回复。