A query for users with a custom title?

Data & reporting
1

Anyone able to provide the query for all users with a custom title (not a badge granted title)?

2 Likes
2

I don’t think this is strictly possible, because a custom title could be character-by-character identical to a badge granted one (possible of a badge the user doesn’t have).

Here’s a query that might do what you want:

SELECT username FROM users
WHERE title IS NOT NULL

AND (
    SELECT count(*) FROM badges
    WHERE allow_title = true
    AND name = title
    ) = 0

AND (
    SELECT count(*) FROM groups
    WHERE groups.title = users.title
    ) = 0

It selects every title any user has, minus every title where a corresponding badge exists, minus every title that could come from a group membership. It does not test whether the user has the badge or the group membership.

Maybe it does help you, at least as a starting point :slight_smile:

2 Likes
3

Thanks @fefrei, that seems to work, but as you mentioned it might not cover all the edge cases. I did have to change the first line SELECT title FROM users to SELECT username FROM users as I was looking for the usernames, not the custom titles.

@Mittineague was able to create a query that seems to work as well

SELECT 
users.username
, users.title 
FROM users 
JOIN user_profiles 
ON user_profiles.user_id = users.id 
WHERE users.title IS NOT NULL 
AND users.title NOT LIKE '' 
AND user_profiles.badge_granted_title IS NOT TRUE
2 Likes
4

Oops, I forgot to copy the query again after fixing that :blush:

That query looks better – I didn’t find the badge_granted_title flag. I’m not sure how this query handles titles granted by group membership, though.

1 Like
5

Nor am I. However, this accomplishes our goal. For some background: we currently don’t have any badge_granted_titles available to users, as we have no custom badges, the only titles are regular and leader, and we’ve “disabled” TL3. We have granted titles in the past to exceptional community members. The community started asking about titles, so the mod team started discussing options. One thing that was agreed upon is that we wanted the custom titles to stand out from the “everyday” badge_granted_titles. To apply CSS to them, we needed to add all the users to a group, hence the request for a query to select them.

1 Like