Check user email confirmed via DataExplorer

support
1

Via the API, I need to determine whether user ids have already confirmed their email address or not. Using DataExplorer I am querying the email_tokens table, but am confused about how to interpret multiple rows for a given user id. When there are multiple rows, N-1 of them have expired = true, but the row with expired = false doesn’t seem to be the current state of whether they have confirmed their email address. For example there are verified users whose email_tokens row with expired = false showing confirmed = false

Probably going about this the wrong way. Please advise. Thank you!

3 Likes
2

@michebs is it possible to track this using a Data Explorer query?

2 Likes
3

Hi David,

When the user confirms the email, the confirmed column is updated, so by adding it as true to the filter, you will list only confirmed emails.

Hope this helps.

SELECT 
    user,	
    email 
FROM email_tokens
WHERE 
    expired = false
    AND confirmed = true
4 Likes
4

What are you trying to accomplish?

6

@michebs - Yes. This is the query that I first tried, but found there is a small subset of our users that are active users, but are not returned by this query. Yet looking at one of those users on their Admin Users page, their email is valid (is not flagged not verified ).

@pfaffman - We use this data to drive one of the state transitions in the services we provide to our members both on the site. When a new member signs up and requests services, we want to first know that we can contact them to provide those services.

1 Like
7

To show an example, with this query:

SELECT
  e.user_id,
  e.expired,
  e.confirmed,
  e.email
FROM email_tokens as e
WHERE e.user_id = 33

Looking at one of our most active users returns:

user_id,expired,confirmed,email
33,false,false,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,true,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,false,somebody@somewhere.com
33,true,true,somebody@somewhere.com

The only non-expired row shows the email as non-confirmed.

8

If you have control over your system, then you might use a Discourse web hook to notify your system that the user is activated. But I’m pretty sure that the easy solution is You can check the active field in the user record. I’m pretty sure that it doesn’t get set until they’ve validated their email address.

http://localhost:3000/admin/users/1/jay.json will give you a record like:

{
id: 1,
username: "jay",
name: "Jay User",
avatar_template: "/user_avatar/localhost/jay/{size}/3_2.png",
email: "jay@nowehre.invalid",
secondary_emails: [ ],
active: true,
admin: true,
moderator: false,
last_seen_at: "2020-11-04T20:13:04.240Z",
last_emailed_at: null,
created_at: "2020-10-05T21:14:52.570Z",
last_seen_age: 27.86773951,
last_emailed_age: null,
created_at_age: 2588319.53785305,
trust_level: 1,
.....
},
4 Likes
9

users.active looks like exactly what I need. Thank you!

4 Likes
10

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.