Skip to content

Published and viewed articles

Affiche les activités des articles réalisés (vues), groupées par article

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT
    article.published_at AS "Date de publication de l'article",
    article.title AS "Nom de l'article",
    article_category.name AS "Catégorie de l'article",
    COUNT(user_activity.id) AS "Nombre de vues"
FROM
    article
    JOIN article_category_pivot ON article_category_pivot.article_id = article.id
    JOIN article_category ON article_category.code = article_category_pivot.article_category_code
    JOIN activity_article_pivot ON activity_article_pivot.article_id = article.id
    JOIN user_activity ON user_activity.activity_id = activity_article_pivot.activity_id
WHERE
    user_activity.ended_at IS NOT NULL
    AND published_at >= "2024-01-01"
GROUP BY
    article.title;