Trust level 0 + custom field badge

Community Building Data & reporting

AI-generated summary

Trust Level 0 + Custom Field Badge

User @cosdesign is trying to create a badge based on a user’s trust level (0) and a chosen option from a dropdown custom field named “Gender”. The badge should be granted as follows: “Warrior Male” for male users and “Warrior Female” for female users.

Initially, the query was not working due to an error, but after implementing a solution from this example, the custom field part is now working. However, the issue persists with the trust level, as the number of potential users remains the same regardless of the trust level entered.

The current query is:

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

@cosdesign seeks help in resolving this issue.

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
image646×269 17.7 KB

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
image385×317 17 KB

Any thoughts?