I'm on MySQL trying to get the user's daily
post count grouped by
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