Tengo una consulta SQL de insignia que otorga una insignia según el número de temas nuevos creados en una categoría específica que también contiene una frase específica (una URL específica) en la primera publicación del tema.
Funciona, pero no exactamente como se pretendía:
SELECT badge_posts.user_id, min(badge_posts.created_at) granted_at, MIN(badge_posts.id) AS post_id
FROM badge_posts
JOIN topics ON topics.id = badge_posts.topic_id
WHERE category_id = 17
AND badge_posts.post_number = 1
AND badge_posts.raw LIKE '%dronescene.co.uk%'
GROUP BY badge_posts.user_id
HAVING COUNT(*) > 49
ORDER BY post_id DESC
Tengo cuatro insignias disponibles, bronce, plata, oro, platino y uso cosas como HAVING count(*) > 1 para el bronce y HAVING count(*) > 10 para la plata, etc. Todas las insignias se otorgan automáticamente, sin problemas.
El problema que parece tener es que todas las insignias se otorgan para el primer ID de tema en esa categoría, en lugar del último ID de tema.
Por ejemplo, tengo un miembro con tres insignias y las tres insignias se han otorgado contra el primer tema que hizo y que coincide con mi cláusula WHERE, en lugar del último ID de tema que coincide con la cláusula WHERE.
¿Alguien puede decirme qué estoy haciendo mal con mi ORDER BY?
Si intercambio MIN con MAX, obtengo el ID del tema más reciente (gracias @JammyDodger ), pero ahora, habiendo visto algunos cambios en la vista previa, creo que mi lógica aquí es fundamentalmente defectuosa de todos modos
Usar MAX entonces hace que todas las demás insignias de este grupo se otorguen para el ID del tema más reciente en su lugar.
Es el mismo problema, solo que a la inversa.
D’oh
Mi lógica era simplemente:
El problema es que si alguien gana el bronce en enero, la plata en marzo y el oro en junio, las tres insignias se otorgan para su primer ID de tema en enero
Creo que eso se debe a que lo estás ejecutando contra personas que ya califican para los cuatro. Si se introdujera “fresco”, creo que se otorgaría automáticamente en la fecha del último tema tan pronto como un usuario superara un umbral.
Para una ejecución “heredada”, ¿es posible que necesites agregar un LIMIT?
Editar: Devolver los primeros (por ejemplo) 25 registros elegibles por usuario en una consulta está más allá de mis habilidades actuales.
Sin embargo, dependiendo de cuántos usuarios califiquen para varias insignias, ¿podrías otorgar las heredadas manualmente usando información de una consulta de data-explorer? Y luego, ejecutar la consulta de insignias automáticas anterior debería otorgar la insignia correcta para la publicación correcta después de que se hayan otorgado esas.
Algo como:
-- [params]
-- int :user_id = 1
-- int :limit = 50
SELECT bp.user_id, bp.created_at, bp.id as post_id
FROM badge_posts bp
JOIN topics t ON t.id = bp.topic_id
WHERE bp.user_id = :user_id
AND t.category_id = 17
AND bp.post_number = 1
AND bp.raw LIKE '%dronescene.co.uk%'
ORDER BY bp.created_at ASC
LIMIT :limit
Me disculpo por cualquier error tipográfico.
(El Bronce debería ser factible con la versión MIN, y el Platino con la versión MAX, por lo que solo son los de 10 y 25)
