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

**URL:** <https://meta.discourse.org/t/query-to-filter-users-by-sign-up-custom-fields/100837>\
**Category:** Data & reporting\
**Tags:** sql-query, user-custom-fields\
**Created:** [October 30, 2018, 1:50am UTC](https://meta.discourse.org/t/query-to-filter-users-by-sign-up-custom-fields/100837 "2018-10-30T01:50:50Z")\
**Posts on this page:** 1\
**Showing post:** 9

<div class="post-metadata">

**Author:** ![simon](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simon/32/339122_2.png) [@simon](https://meta.discourse.org/u/simon)\
**Post date:** [January 16, 2020, 1:41am UTC](https://meta.discourse.org/t/query-to-filter-users-by-sign-up-custom-fields/100837/9 "2020-01-16T01:41:46Z")

</div>

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:

```sql
--[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:

```sql
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)

```

---

_[View the full topic](https://meta.discourse.org/t/query-to-filter-users-by-sign-up-custom-fields/100837)._