SQL Query to extract all posts with unchecked checkboxes

Data & reporting
1

Continuing the discussion from Topic Checkboxes - a dynamic check list showing all checkboxes from all topic messages:

What would be an SQL query to extract all posts with unchecked checkboxes in a particular category, including excerpts (i.e. the paragraph that contains that checkbox)?

2

Something like that?

-- [params]
-- int_list :categories

SELECT p.id post_id,
       t.id topic_id,
       c.id category_id,
       (regexp_matches(p.raw, '\[\s\]\s*[^]\r\n]+', 'g'))[1] AS html$excerpt
FROM posts p
JOIN topics t ON p.topic_id = t.id
JOIN categories c ON t.category_id = c.id
WHERE p.raw ~ '\[\s\]\s*[^]\r\n]+'
  AND c.id IN (:categories)

image
image1361×463 20.6 KB

I might have misunderstood something, but I believe it would be a good starting point.

1 Like
3

Oh, so are checkboxes just regular text in the message, e.g. [], [ ], [*] etc?

1 Like
4

That’s right. :+1:

Though if you use cooked instead of raw you’d see that they get converted into the icon. I’m not sure if that would be easier to search on though?

1 Like