jerdog is trying to create a Data Explorer query to find users who have filled out custom fields 1-4, with the goal of displaying all four fields as columns for each user. simon provided a query that should achieve this, using Common Table Expressions (CTEs) to join the custom fields. The query returns a single row for each user with all four fields, showing only users who have filled out at least one field. simon noted that this approach may not be the most efficient but meets the original requirements. jerdog expressed willingness to try the provided query and explore alternative approaches.
I have a data explorer query where I am trying to find all who have filled out user custom field 1-4, and would love it to only show those with all 4 as columns for each user, but my postgre skills are limited and I haven’t been able to figure it out:
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
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
If there is a better way I am so ok with trying/doing it! I’ll give this a shot though.
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.