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)
בדרך כלשהי לא ידעתי על זה. פתרתי מיד את הבעיה שלי, תודה רבה!
דרך אגב השאילתה שהיא הציעה היא
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 (/* רשימת מזהי משתמשים כאן, למשל 1,2,3 */)
כן, גם אני חשבתי על זה. השאילתה הראשונית היא כל מה שהייתי צריך כדי לפתור את הבעיה הזו, אבל קבלת אימיילים בתוצאות השאילתה תהיה שימושית לעתיד.
המשכתי וביקשתי מה-AI לעשות כפי שהצעת. עם קצת התעסקות (כדי להפוך את הסקר הספציפי לבחירה), קיבלתי את הדברים הבאים:
-- [פרמטרים]
-- 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
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.
