Trust level 0 + custom field badge

data & reporting
1

Hi. I want to create a badge based on the trust level and the chosen option from a dropdown custom field and I keep getting this message ( also I’m not sure if the rest of the query is correct, so any help is appreciated ):

ERROR:  column "Woman" does not exist
LINE 10: ) AND LENGTH(cf.value) = "Woman"

How it should work:
So I’ve created a dropdown user custom field named “Gender” and two options, “Man” and “Woman”.
If user X is a man and registered ( level trust 0 ), the “Warrior Male” badge should be granted, otherwise the “Warrior Female” badge.

Also can someone please tell me where can I see the user field number for all my existing custom fields? ( in my example I’m using the field name, but just in case ).

The query so far (not working):

SELECT u.id, current_timestamp, cf.user_id user_id, cf.updated_at granted_at 
FROM users u, user_custom_fields cf 
INNER JOIN user_fields uf
      ON uf.id = (0 || regexp_replace(cf.name, 'user_field_', ''))::integer
      AND uf.name = 'Gender'
WHERE trust_level >= 0 AND (
  :backfill OR u.id IN (:user_ids)
) AND LENGTH(cf.value) = "Woman"

Thanks a lot! :slightly_smiling_face:

image

2 Likes
2

The custom field it works now ( I tried to implement this example ) but no matter what trust level I’ll type, the number of potential users stays the same:

    SELECT cf.user_id user_id, cf.updated_at granted_at
    FROM user_custom_fields cf
    INNER JOIN user_fields uf
      ON uf.id = (0 || regexp_replace(cf.name, 'user_field_', ''))::integer
      AND uf.name = 'Gender'
    WHERE cf.value = 'Woman'
AND EXISTS (
  SELECT u.id user_id, current_timestamp granted_at FROM users u
    WHERE trust_level >= 0 AND (
  :backfill OR u.id IN (:user_ids)
    )
)

image

Any thoughts?