SQL query to identify which users have not voted yet in which open polls


(Anton) #1

So, I’d like to invite people to vote in polls personally.
For this, I’d like to build an SQL query to identify in which polls and who have not voted yet.

To start with, how would I identify:

  1. the list of the polls - should I just search through messages with [poll token?

  2. who have voted / not voted for a particular poll

  3. I have run this query: SELECT DISTINCT name FROM post_custom_fields
    …and got the following result, can someone explain what these names are about?


(Régis Hanol) #2

Like you noticed, polls and their votes are stored in the “post_custom_fields” table as serialized JSON.

Nope, you can look for all the polls using this query

SELECT post_id, value 
FROM  post_custom_fields
WHERE name = "polls"

You can look for poll votes using this query

SELECT post_id, value
FROM post_custom_fields
WHERE name = "polls-votes"

Then you have a bit of work to de-serialize the JSONs and extract the data you want (note that there can be more than one poll per post ;))


(Gerhard Schlager) #3

Take a look at the query I wrote. It lists all votes made in a poll. I guess you can adapt it to your needs… At least it’s a good example on how to extract the data from JSON.