Grant a badge to everyone having posted at least <POST_COUNT> posts in the category <CATEGORY_NAME> that have received at least <LIKE_COUNT> likes by users in the group <TEAM_NAME>. Similar to the bug reporter badge here, but can require more than one like.
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
SELECT id FROM categories WHERE name ilike '<CATEGORY_NAME>'
) AND (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<TEAM_NAME>' )
)
) >= <LIKE_COUNT>
AND p.post_number = 1
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST_COUNT>
Hola @Firepup650 quizás prueba con este. funcionó en mi instancia.
<NOMBRE DE CATEGORÍA> = Nombre de categoría sensible a mayúsculas y minúsculas (no slug)
<GRUPO> = Nombre del grupo (ej: Staff, Trust_level_0)
<NÚMERO MÍNIMO DE ME GUSTA> = número mínimo de me gusta que deseas establecer
<UMBRAL DE NÚMERO DE PUBLICACIONES> = número mínimo de publicaciones
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = (
SELECT id FROM categories WHERE name ilike '<NOMBRE DE CATEGORÍA>'
) AND (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<NOMBRE DE GRUPO>' )
)
) >= <NÚMERO MÍNIMO DE ME GUSTA>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <UMBRAL DE NÚMERO DE PUBLICACIONES>
para múltiples categorías puedes hacer esto:
<NOMBRES DE CATEGORÍAS> = Nombres de categorías sensibles a mayúsculas y minúsculas
<GRUPO> = Nombre del grupo (ej: Staff, Trust_level_0)
<NÚMERO MÍNIMO DE ME GUSTA> = número mínimo de me gusta que deseas establecer
<UMBRAL DE NÚMERO DE PUBLICACIONES> = número mínimo de publicaciones
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id IN (
SELECT id FROM categories WHERE name ILIKE ANY (ARRAY['<NOMBRE DE CATEGORÍA 1>', '< NOMBRE DE CATEGORÍA 2>', '< NOMBRE DE CATEGORÍA 3>'])
) AND (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE '<GRUPO>' )
)
) >= <NÚMERO MÍNIMO DE ME GUSTA>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <UMBRAL DE NÚMERO DE PUBLICACIONES>
¡Hola @Lilly!
Ambas parecen ser excelentes consultas, pero quería ejecutar la consulta contra todas las categorías si fuera posible. Cuando lo intenté, seguí recibiendo errores sobre una subconsulta que devolvía varias filas, así que vine aquí a preguntar al respecto.
¿Quieres decir que quieres la misma consulta para todas las categorías?
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE '<GROUP>' )
)
) >= <MINIMUM LIKE COUNT>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
Eso parece que funcionaría, pero parece fallar cuando el grupo contra el que lo ejecutas es staff. Probé tanto Staff como staff como nombre de grupo, y temporalmente establecí el recuento de publicaciones y me gusta en 1, y dice que no se otorgarían insignias. ¿Qué estoy haciendo mal aquí?
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE 'staff' )
)
) >= 1
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= 1
Raro Todavía no me funciona. Intentaré ejecutarlo contra otros grupos para ver si puedo encontrar el problema.
Editar: Lo ejecuté contra un grupo diferente y la consulta aún falló. No estoy seguro de cuál podría ser el problema aquí. ¿Depende de los grupos principales por casualidad?
Editar 2: Eso no funcionaría, staff no parece poder establecerse como grupo principal.
Creo que sé por qué. Trabajaré en esto para ti después de cenar. De todos modos, necesito practicar SQL. La insignia SQL es más restrictiva que postgres. Superé la parte de la subconsulta.
Todavía no me he despertado del todo y creo que las consultas de insignias necesitan un par de tazas de té para poder abordarlas por completo, pero he estado hablando con el bot recientemente sobre este tipo de consultas y creo que usar el post_action_code_id real y el código group_id son mejores que usar las consultas SELECT anidadas para encontrar lo mismo.
Hice esto para obtener las tablas de esquema necesarias para posts, posts_actions, group_users y groups
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<TABLE NAME>';
Luego usé esto para obtener todos los IDs de grupo:
SELECT name, id FROM groups ORDER BY name
Así que luego incluí todas las tablas de esquema necesarias e instruí a Lola, er GPTbot, para que usara el ID de post_action_code real y el código de group_id. Luego, después de algunas discusiones y correcciones. Llegamos a esto. De nuevo, parece funcionar en el Explorador de Datos, pero todavía no puedo obtener nada de él en la Vista Previa de Insignias.
G = group_id
X = número mínimo de likes
Y = número mínimo de posts
SELECT pa.user_id, MIN(pa.post_id) as post_id, COUNT(pa.post_id) as post_count, COUNT(pa.id) as like_count, MAX(pa.created_at) as granted_at
FROM post_actions pa
JOIN group_users gu ON gu.user_id = pa.user_id
WHERE gu.group_id = G AND pa.post_action_type_id = 2
GROUP BY pa.user_id
HAVING COUNT(pa.post_id) >= Y AND COUNT(pa.id) >= X
Yo lo llamo Bert. Aunque tenemos una relación complicada.
Creo que otra limitación de ese tipo de consulta es que usar MIN(p.created_at) granted_at me da la fecha de la primera y no, digamos, la fecha de la décima. Se podría cambiar a MAX, pero eso también daría una fecha “incorrecta” si la ejecutaras contra datos históricos donde ya tuvieran más de 10.
Todavía le estoy dando vueltas a eso.
Tuve algo de éxito usando ROW_NUMBER(), pero nada concreto todavía.
Me estoy divirtiendo con esto y me está ayudando a reaprender SQL y cómo escribir mejores consultas. Tener a Lola / GPT4bot como asistente de SQL es útil, pero tienes que guiarla y preguntarle de la manera correcta. Estoy tratando de encontrar formas de darle acceso a la mayor parte de la información de las tablas del esquema para no tener que hacerlo para cada problema de consulta en el que trabajemos. Proporcionar la información del esquema de la tabla da mejores resultados. Intenté darle un enlace a los esquemas disponibles en el núcleo, pero eso solo la hizo perder el tiempo en Google.
Estoy interesado en trabajar con ella cuando sepa que el previsualizador de consultas de insignias está funcionando. Necesito practicar SQL y hacer consultas de insignias. Por cierto, ella no puede arreglarlo y todavía hace que mi té Earl Grey no esté lo suficientemente caliente. Aunque la lección de SQL de anoche fue la mejor cita que he tenido en años.
Con esa consulta, parece que hemos tenido un problema extraño. Parece que se otorgó solo al personal, y estoy casi seguro de que algunos no miembros del personal cumplirían ese criterio. ¿Es algo que rompí en algún lugar o es un problema de consulta?
Solo para tener un reinicio, ya que creo que el ir y venir me ha confundido.
¿El objetivo es otorgar una insignia por una cierta cantidad de publicaciones en todas las categorías que hayan sido marcadas como “me gusta” por @staff al menos una vez?
Lo pretendía que se concediera a los usuarios que tuvieran X número de publicaciones con Y me gusta por parte del personal, en todas las categorías. En mi caso, 10 publicaciones, 5 me gusta.
Después de un confuso error con algunas “Me gusta” eliminadas que desviaron mis pruebas, creo que esta es una versión revisada de la del OP que cumple tus criterios:
SELECT p.user_id, MAX(p.created_at) granted_at
FROM badge_posts p
WHERE (SELECT COUNT(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = 2
AND deleted_at IS NULL
AND pa.user_id IN (SELECT gu.user_id FROM group_users gu WHERE gu.group_id = 3)
) >= 5
AND p.user_id >= 0
GROUP BY p.user_id
HAVING COUNT(*) >= 10
Funciona con la vista badge_posts, por lo que solo cuenta las publicaciones de categorías públicas, lo cual puede que quieras tener en cuenta dependiendo de la configuración de tu foro/categoría. Además, usar CURRENT_TIMESTAMP para granted_at es otra opción, pero probablemente sea una cuestión de gustos.
quizás prueba con este. funcionó en mi instancia.
Todavía no me funciona. Intentaré ejecutarlo contra otros grupos para ver si puedo encontrar el problema.
