Query to filter users by sign-up 'custom fields'

data & reporting
,
2

To get all values for a named field, try this, with the name of your field entered into the field_name input:

--[params]
-- string :field_name

WITH ucf_name AS(
SELECT
CONCAT('user_field_', id) AS name
FROM user_fields
WHERE name = :field_name
)

SELECT
u.id AS user_id,
ucf.value
FROM users u
JOIN user_custom_fields ucf
ON ucf.user_id = u.id
WHERE ucf.name = (SELECT name FROM ucf_name)

To get the value of a user field for a specific user:

--[params]
-- string :field_name
-- string :username

WITH ucf_name AS(
SELECT
CONCAT('user_field_', id) AS name
FROM user_fields
WHERE name = :field_name
)

SELECT
u.id AS user_id,
ucf.value
FROM users u
JOIN user_custom_fields ucf
ON ucf.user_id = u.id
WHERE ucf.name = (SELECT name FROM ucf_name)
AND u.username = :username

To only get users who have entered a specific value for a field, try this, with the name of the field entered into the field_name input, and the value you are looking for entered into the field_value input:

--[params]
-- string :field_name
-- string :field_value

WITH ucf_name AS(
SELECT
CONCAT('user_field_', id) AS name
FROM user_fields
WHERE name = :field_name
)

SELECT
u.id AS user_id
FROM users u
JOIN user_custom_fields ucf
ON ucf.user_id = u.id
WHERE ucf.name = (SELECT name FROM ucf_name)
AND ucf.value = :field_value
8 Likes