How best do you think to fetch 10% of user to user PM messages in the last 30 days I’m trying to find a “randomise” function that could be called upon?
I run a website where people talk about largely illegal stuff - drugs mainly. As a platform we explicitly don’t allow sales or anything like that - I’m looking for some kind of “random call” (Sorry I don’t code I don’t know precisely how to articulate what I’m asking) randomised/fair way to defensibly say we “Spot check” PMs to make sure the rules are being followed.
I don’t obviously want to arbitrarily pick on people for this. We have flagged words and phrases and that does work but some people are incidentally getting around it, I would like (probably need) to be able to say we have community flags, moderators, watched words and “This” spot checking - I’m just not sure where to get the randomness … parameter from if it exists.
I’m not begging for free code/scripts either btw I’m just not sure how to ask for what I want if it’s going to the market place or is feasible.
You’d call the API to get that user’s messages. And messages are not stored as message records they are just normal posts, linked with topics, so you need to distinguish between post posts, and message posts.
Hmmmmmmmmmmmmmm
Right I think we’re going to call a crypto rand function to fetch topics that only have say 2 users in it and see how that goes, will get back with findings if anyone else runs communities where you might have legal need to spot check PMs in a fair way.
Oh that’s awesome mate, I can export those results then draw against them with random.org and check those users pms for being cheeky. I think random actually lets your save the draw list for reference as well if you pay them a small fee so there’s proof of no foul play on the admin side.
What if you can “search” in the PMs terms like “buy” or “drugs”
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.
It stumbled over how to handle the emoji, our welcome is Greeetings! so just left it as greetings and let it run and it largely works - we have about 20+ catch alls phrases though on the watched words log/flag section.
Because someone for example could be just chatting about the best LSD experience they ever had to a friend which is fine, what is not fine is “Where do I get” “How do I make” “Who has the best?” so we use the baked in flagging system to catch those and for that purpose it works well.
Legally though since there is “more” we could be doing to be doing everything “Reasonably” expected of us if it ever came to it, I can now say the community flags inappropriate content (as they are good and know the rules), we moderate, we have auto system flags, watched phrases and terms and we randomly spot check PMs for untoward behaviour.
So - yes your second one works, but is (without sounding mean) redundant for us specifically, since talking about drugs isn’t illegal - at least where we are, but trying to score or manufacture or whatever else is which we want no part of.
Thanks again mate, that first draw is minimal manual work and can be given to a member of staff to deal with and it’s systematic.
Greeetings! so just left it as greetings and let it run and it largely works - we have about 20+ catch alls phrases though on the watched words log/flag section.