Je cherche de l’aide pour modifier cette requête SQL afin d’inclure également le nombre de solutions pour les catégories et le nombre de réponses :
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19
SELECT c.id category_id, COUNT(DISTINCT(t.id)) topics, COUNT(p.id) posts, sum(p.like_count) likes, sum(p.reads) reads
FROM categories c
INNER JOIN topics t ON (t.category_id = c.id)
INNER JOIN posts p ON (p.topic_id = t.id AND p.post_type = 1)
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC
Actuellement, la sortie de cette requête est comme ceci, ce qui est très utile, mais j’aimerais inclure ces autres métriques.
J’ai essayé de bricoler, mais mes compétences en SQL sont très faibles. Si quelqu’un pouvait m’orienter ou m’aider avec la requête, je lui en serais très reconnaissant
J’ai réussi à faire fonctionner quelque chose, avec l’aide de l’IA, qui correspond à mon propre contexte (vous devrez peut-être ajuster la référence solved).
Je partage les solutions finales au cas où d’autres pourraient trouver une utilité à cela.
La première itération :
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19
SELECT
c.id AS category_id,
COUNT(DISTINCT t.id) AS topics,
COUNT(p.id) AS posts,
SUM(p.like_count) AS likes,
SUM(p.reads) AS reads,
COUNT(DISTINCT CASE WHEN tf.name = 'accepted_solution_id' THEN t.id END) AS solutions,
COUNT(CASE WHEN p.reply_to_post_number IS NOT NULL THEN p.id END) AS replies
FROM categories c
INNER JOIN topics t ON t.category_id = c.id
INNER JOIN posts p ON p.topic_id = t.id AND p.post_type = 1
LEFT JOIN topic_custom_fields tf ON t.id = tf.topic_id AND tf.name = 'accepted_solution_id'
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC
La solution finale pour mon cas d’utilisation spécifique :
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19
SELECT
c.id AS category_id,
COUNT(DISTINCT t.id) AS topics,
COUNT(p.id) AS posts,
SUM(p.like_count) AS likes,
SUM(p.reads) AS reads,
COUNT(DISTINCT CASE WHEN ua.action_type = 15 THEN t.id END) AS solutions,
COUNT(CASE WHEN p.reply_to_post_number IS NOT NULL THEN p.id END) AS replies
FROM categories c
INNER JOIN topics t ON t.category_id = c.id
INNER JOIN posts p ON p.topic_id = t.id AND p.post_type = 1
LEFT JOIN user_actions ua ON ua.target_topic_id = t.id AND ua.action_type = 15
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC
,
