0

I'm on MySQL trying to get the user's daily post count grouped by post category.

So for every day in a given date range, with no gap, I need the post count for each existing post category for a given user. When the user has not posted anything in a category for that day, that category must still be listed with a null or, better yet, 0 postCount.

I've solved the daily date part by using a sequence table. But I'm failing to include the empty categories. I believe I need to emulate full outer join with union, but I'm not sure where to start.

My current query:

select 
    `list_of_dates`.`date` as `date`, 
    `post_categories`.`name` as `categoryName`, 
    count(`posts`.`id`) as postCount 
from ( 
    SELECT 
        DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) AS date 
    FROM 
        seq_0_to_999 AS seq 
    WHERE 
        DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) <= '2017-03-28' 
) as list_of_dates 
left join 
    ( 
        SELECT `posts`.* FROM posts WHERE `posts`.user_id = 65 
    ) as posts on DATE(`posts`.`created_at`) = `date`
left join 
    `post_categories` on `post_categories`.`id` = `posts`.`category_id` 
group by 
    `date`, `categoryName` 
order by 
    `date` asc
2

You need to first CROSS JOIN the two tables, list_of_dates and post_categories and then LEFT JOIN posts:

SELECT 
    ld.date     AS date, 
    pc.name     AS categoryName, 
    COUNT(p.id) AS postCount 
FROM 
    ( SELECT 
          DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) AS date 
      FROM 
          seq_0_to_999 AS seq 
      WHERE 
          DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) <= '2017-03-28' 
    ) AS ld                   -- list_of_dates 
CROSS JOIN
    post_categories AS pc     -- list_of_categories 
LEFT JOIN 
    posts AS p
  ON  p.user_id = 65 
  AND p.created_at >= ld.date AND p.created_at < ld.date + INTERVAL 1 DAY
  AND p.category_id = pc.id 
GROUP BY 
    ld.date, 
    pc.id, pc.name 
ORDER BY
    ld.date ASC ;
0

LEFT JOIN includes all the records on the LEFT. Your join on post_categories is however your last join (i.e. it is on the RIGHT) - try it with RIGHT JOIN and you should get all the categories irrespective of whether they match.

Alternately move post_categories up so it is on the LEFT and you can continue using a LEFT JOIN on the posts.

  • Using RIGHT JOIN on post_categories has resulted in rows with null dates. – rzb Mar 28 '17 at 9:59
  • When moving post_categories up, what col would I join the tables on? – rzb Mar 28 '17 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.