Export a list of User Emails by User_ID

Data & reporting
1

Hello,

My apologies if this information is already somewhere else, I’ve looked but couldn’t find a solid answer.

I’m trying to figure out the easiest way to find a user’s email using either their username or user ID en masse. I’m the admin for my site, so I have the permissions, and I’m aware that I can find this information on a user’s profile. However, I need to do this for potentially thousands of users.

The situation is that, unfortunately, thousands of bot accounts were created to vote in a poll we were running, and I need to accurately pair down the final counts to appropriately determine winners. I’ve used the Poll results report Data Explorer query to return the voting results for the poll, but that doesn’t return user emails. I assume it would be possible to write another query for the data explorer plugin that could take a list of IDs or Usernames and return a corresponding list of emails. But I’m not knowledgeable in SQL, so I’m a little lost figuring it out.

If someone could point me in the right direction, or has a better suggestion for how to get this information, I would appreciate it!

Thank you.

2

I recommend that you go to https://ask.discourse.com/ and enter the query you have and tell it that you need the user emails (you need to join the user_id that I think you have to the UserEmails table to get the email addresses)

1 Like
3

Somehow didn’t know about that. Immediately resolved my issue, thank you very much!

Btw the working query is:

SELECT users.id AS user_id, user_emails.email
FROM users
LEFT JOIN user_emails ON users.id = user_emails.user_id AND user_emails.primary
WHERE users.id IN (/* list of user IDs here, e.g. 1,2,3 */)
1 Like
4

I think you can make a more clever one that will fill in the “list of users” with another select.

Give it the query from the poll results and ask if it can combine them.

1 Like
5

Yeah, I thought of that too. The initial query was all I needed to resolve this issue, but having emails returned in the poll results query will be useful for the future.

I went ahead and had the AI do as you suggested. With a little fiddling (to make the specific poll selectable), I got the following:

-- [params]
-- int :topic_id
-- int :post_number
-- text :poll_name

SELECT polls.name AS "Poll Name",
       poll_options.html AS "Answer",
       poll_votes.user_id AS "User ID",
       users.username AS "Username",
       user_emails.email AS "Email"
FROM poll_options
INNER JOIN poll_votes ON poll_options.id = poll_votes.poll_option_id
INNER JOIN polls ON polls.id = poll_votes.poll_id
INNER JOIN users ON users.id = poll_votes.user_id
LEFT JOIN user_emails ON users.id = user_emails.user_id AND user_emails.primary
WHERE poll_options.poll_id IN (
    SELECT polls.id FROM polls
    WHERE polls.post_id = (
      SELECT posts.id FROM posts WHERE topic_id = :topic_id AND post_number = :post_number
    ) AND polls.name = :poll_name
)
ORDER BY polls.name, poll_options.html

Seems like it’s working, thanks again for the advice!

6

Me telling you what to tell the AI what to do is some next level vibe coding! :rofl:

1 Like
7

I think this could be simplified to something like this (and also lean on some of the data explorer magic too):

-- [params]
-- post_id :post_url
-- text :poll_name = poll

SELECT 
    pv.user_id,
    po.html AS answer,
    ue.email
FROM poll_options po
  JOIN poll_votes pv ON po.id = pv.poll_option_id
  JOIN polls p ON p.id = pv.poll_id
  JOIN user_emails ue ON pv.user_id = ue.user_id AND ue.primary
WHERE p.post_id = :post_url
  AND p.name = :poll_name
ORDER BY po.html

The post_id parameter type allows you to paste in the post url and get its post_id that way (eg. https://meta.discourse.org/t/export-a-list-of-user-emails-by-user-id/373768/5). And most single polls in a post are simply called ‘poll’ unless a second one is added to the same post (or added manually), so having that as a default for :poll_name seemed useful.

Also, user_id has some magic to it so it will show an avatar and username as a link to the user card/profile rather than simply the plain user id itself. Very useful for viewing results ‘onsite’, though if you plan on exporting them then you would need to add the users table back in to get the text version of the username.