Help with creating a query to randomly check PMs for certain terms

data & reporting
,
4

PMs with term

I have a new idea @DNSTARS !

What if you can “search” in the PMs terms like “buy” or “drugs” :lol:

Check this out:

-- [params]
-- int :limit = 10
-- string :term = %term%
SELECT p.user_id, p.topic_id, p.post_number, p.raw, p.created_at::date
FROM posts p
LEFT JOIN topics t on t.id = p.topic_id
WHERE t.archetype = 'private_message'
  AND t.title <> 'WELCOME-TITLE-from-discobot'
  AND p.created_at::date > now()::date - 8
  AND p.raw ILIKE :term
ORDER BY p.created_at DESC
LIMIT :limit

Replace WELCOME-TITLE-from-discobot by the exact title of your welcome message, so that all welcome messages generated automatically are excluded.

The query’s idea source is from guru’s @meglio posted here.

I only add some little changes :wink:

Query list updated :rocket:

5 Likes