# Ajuda na criação de uma consulta para verificar aleatoriamente PMs por certos termos

**URL:** https://meta.discourse.org/t/help-with-creating-a-query-to-randomly-check-pms-for-certain-terms/275030
**Category:** Data & reporting
**Tags:** moderation, sql-query
**Created:** [12 Julho , 2018 08:08 UTC](https://meta.discourse.org/t/help-with-creating-a-query-to-randomly-check-pms-for-certain-terms/275030 "2018-07-12T08:08:22Z")
**Posts on this page:** 1
**Showing post:** 4

<div class="post-metadata">

### Author: ![SidV](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sidv/32/119460_2.png) [@SidV](https://meta.discourse.org/u/SidV)
#### Post date: [12 Julho , 2018 15:37 UTC](https://meta.discourse.org/t/help-with-creating-a-query-to-randomly-check-pms-for-certain-terms/275030/4 "2018-07-12T15:37:59Z")

</div>

### PMs com termo

Tenho uma nova ideia @DNSTARS!

E se você pudesse “buscar” nos MPs termos como “`buy`” ou “`drugs`” :lol:

Dê uma olhada:

```sql
-- [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

```

> Substitua `WELCOME-TITLE-from-discobot` pelo título exato da sua mensagem de boas-vindas, para que todas as mensagens de boas-vindas geradas automaticamente sejam excluídas.
> 
> A fonte da ideia da consulta é do guru @meglio _[publicada aqui](https://meta.discourse.org/t/sql-query-to-get-the-queue-of-all-the-pms-in-the-system/38089/3?u=sidv)_.
> 
> Eu apenas fiz algumas pequenas alterações 😉

_[Lista de consultas](https://github.com/SidVal/discourse-data-explorer/blob/queries/querys.md)_ atualizada 🚀

---

_[View the full topic](https://meta.discourse.org/t/help-with-creating-a-query-to-randomly-check-pms-for-certain-terms/275030)._