The discussion revolves around extracting SQL posts with unchecked checkboxes in a specific category. meglio requests a query to extract such posts, including excerpts. Arkshine provides a potential SQL query using regular expressions to match unchecked checkboxes. The query uses raw post data, but JammyDodger notes that using cooked data might be an alternative approach, as it converts checkboxes into icons. Checkboxes are represented as regular text, e.g., [] or [ ], and can be searched using the provided SQL query.
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)?
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)
I might have misunderstood something, but I believe it would be a good starting point.
Oh, so are checkboxes just regular text in the message, e.g. [], [ ], [*] etc?
That’s right. 
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?
