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

Daten & Berichterstattung
,
1

I have a custom dropdown field setup on sign up where users select a ‘Category’ that best suits them.

How can I find users based on what they selected within that dropdown?

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 „Gefällt mir“
3

Trying to find the correct query to be able to search all users which have a custom user field Pro Email, but to find the users which are not in the Pro group?

4

I moved your post to this topic. I think the last query in my previous post in this topic will get you close to what you are looking for. If your custom field is a confirmation type, enter ‘true’ for the field_value parameter. That will return all users who have checked the confirmation box for the field name you supply for the query’s field_name parameter.

The query will need to be added to so that you can filter out users who are already in your Pro group.

2 „Gefällt mir“
5

Any example for a query on how to filter out users already in that group and then only return users not in that group?

I have not used the data explorer plugin for Discourse that much.

6

Here’s one way to do it. It adds an excluded_users temporary table that’s made up of members of the excluded group. You will need to supply the group’s Name for the query’s group_name parameter.

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

WITH ucf_name AS(
SELECT
CONCAT('user_field_', id) AS name
FROM user_fields
WHERE name = :field_name
),
excluded_users AS (
SELECT
gu.user_id
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_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
AND u.id NOT IN (SELECT user_id FROM excluded_users)
3 „Gefällt mir“
7

Thank you for that data explorer query, much appreciated.

8

What would need to defined in the field_value since I just need to have to process if it has a value set for the Pro Email user custom field?

9

If Pro Email is a confirmation field (checkbox) then setting the query’s field_value parameter to true will work.

SInce all you are wanting to do is check that the field has been set, I think this version of the query will work better for you. It removes the field_value parameter and checks that the named field has been set with (ucf.value = '') IS NOT TRUE. This will return false for any users who have not set the field, and will also return false for users who have set the field and then deleted the value they set:

--[params]
-- string :field_name
-- string :group_name

WITH ucf_name AS(
SELECT
CONCAT('user_field_', id) AS name
FROM user_fields
WHERE name = :field_name
),
excluded_users AS (
SELECT
gu.user_id
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_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 = '') IS NOT TRUE
AND u.id NOT IN (SELECT user_id FROM excluded_users)

If you’d like the query to return the field’s value, change the final select statement to:

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 (ucf.value = '') IS NOT TRUE
AND u.id NOT IN (SELECT user_id FROM excluded_users)
3 „Gefällt mir“
10

Thank you it works great!

11

Hallo @simon , welche dieser Abfragen würde funktionieren, wenn ich mehrere benutzerdefinierte Felder hätte und nur eine Liste von Benutzern und ihren Antworten auf diese benutzerdefinierten Felder zurückgeben wollte? Ich habe Fragen wie „Woher haben Sie von uns erfahren?“, die Kontrollkästchen für Mehrfachauswahl zulassen, und eine andere, die fragt, wie sie kontaktiert werden möchten: E-Mail, SMS und Post.
Danke
Clement

12

Keine der obigen Abfragen liefert Ihnen genau das, wonach Sie suchen. Ohne eine neue Abfrage zu schreiben, wäre der beste Ansatz, die erste von mir gepostete Abfrage mehrmals auszuführen. Führen Sie sie einmal für jedes Ihrer benutzerdefinierten Felder aus. Geben Sie den Namen des Feldes in die Eingabe field_name der Abfrage ein. Die Abfrage, auf die ich mich beziehe, ist diese:

--[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)
1 „Gefällt mir“