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 reportData 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!
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)
Wist ik ergens niet van. Heeft mijn probleem onmiddellijk opgelost, heel erg bedankt!
Trouwens, de werkende 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 (/* lijst met gebruikers-ID's hier, bijv. 1,2,3 */)
Ja, daar dacht ik ook aan. De initiële query was alles wat ik nodig had om dit probleem op te lossen, maar het terugkrijgen van e-mails in de query van de pollresultaten zal in de toekomst nuttig zijn.
Ik heb de AI het advies laten opvolgen. Met een beetje aanpassen (om de specifieke poll selecteerbaar te maken), kreeg ik het volgende:
-- [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
Het lijkt te werken, nogmaals bedankt voor het advies!
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.
