Data explorer query for events?

data & reporting
,
1

I have tried querying something as simple as this, to start:

SELECT *
FROM discourse_post_event_invitees
WHERE post_id = :post_id

Unfortunately I’m getting 0 results back for a post that I know has 15 users marked as Going. Any ideas?

1 Like
2

That should work. Are you grabbing the right post id from the json?

I think you could link it up to the posts table and use a topic id, which may be less faff than finding the post id. Something like:

-- [params]
-- topic_id :topic_id

SELECT ei.user_id, ei.status
FROM discourse_post_event_invitees ei
JOIN posts p ON p.id = ei.post_id 
WHERE p.topic_id = :topic_id
2 Likes
3

Okay, getting closer. That returns everyone who is going or interested, how do you differentiate?

4

Ah, okay, the name of the key “status” confused me a bit. I was thinking that was some sort of status of the event.

status = 0 = Going
status = 1 = Interested
status = 2 = Not Going

2 Likes
5

You can make it a bit easier to read without remembering the codes too if you add in a little something like this:


-- [params]
-- topic_id :topic_id

SELECT ei.user_id,
      CASE 
       WHEN ei.status = 0 THEN 'Going' 
       WHEN ei.status = 1 THEN 'Interested'
       WHEN ei.status = 2 THEN 'Not going' 
        END AS "Going?"
FROM discourse_post_event_invitees ei
JOIN posts p ON p.id = ei.post_id 
WHERE p.topic_id = :topic_id
ORDER BY ei.status
4 Likes