How to select all USERS who didnt POST, grouped by dates?

Question

I have USERS table and POSTS table. I can easy select all users who posted from 01/01/2020 to 01/04/2020 and group the results by date:

User::whereDoesntHave('posts', function($query) use($first_date, $last_date) {
    $query->whereBetween('posts.date', [$first_date, $last_date]);
})
->get();

What I need help with is getting the USERS who didnt post from 01/01/2020 to 01/04/2020 and group it by dates. It means I want all users who dont have a row on the POST table in a specific range of dates. Is it possible?

Exemple of how I want the result:

01/01/2020
- John
- Ana
- Sandra

01/02/2020
- Ana
- Sandra

01/03/2020
- John
- Lucas
- Charlie

01/04/2020
- Charlie
- Ana



users table:

id = (int)
name (varchar)

posts table

id (int)
user_id (int)
msg (text)
date (datetime)

You could do an inverted `select` with a subquery from this return. `select * from table where userid not in (select userid from previous_result where ...);` — user3783243, Mar 09 '20 at 16:10
Would that group every result by date? I dont have skills with mysql so I dont really know how to make that query, I would like someone to type the query for me so I can adapt it to laravel framework — Nadiow White, Mar 09 '20 at 16:21
Well there wouldn't be date**s** for `USERS who didnt post from 01/01/2020 to 01/04/2020` because it is one range. If you want each date individually it'd be a bit different. — user3783243, Mar 09 '20 at 16:33
Please describe your tables. Have you made any attempt to solve this yourself? — miken32, Mar 09 '20 at 16:51
Welcome to SO. Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Mar 09 '20 at 17:13
Alright I am sorry. I am going to describe all my tables right now. And I havent tried to do this query by myself because I am all new to mysql and databases. — Nadiow White, Mar 09 '20 at 17:31
But you're working with Laravel? Your question is tagged as such. The whole point is not to work with databases. — miken32, Mar 09 '20 at 17:34
Yeah I was hoping I could get the query as an answer and then I could adapt it to Laravel, maybe using a raw query. If I get the answer with laravel eloquent orm, even better. Actually I managed to make the query on my own, the thing is that I can group it by date because since those are absent rows, there is no date field on them. Do you understand what I mean? I also forgot to say that I am also new to SO. — Nadiow White, Mar 09 '20 at 17:38
Does `posts` table not have an `id` column? You aren't using standard Laravel timestamps on your model? — miken32, Mar 09 '20 at 17:42
I am sorry about the id, I forgot to type that on my post. I am not using standard laravel timestamps on my model. is this a problem? My query is like this atm: User::whereDoesntHave('posts',function($query) use ($first_date,$last_date){ $query->whereBetween('posts.date',[$first_date,$last_date]); })->get(); And it is working fine so far. it brings me all the users who didnt post from x to y. The only thing I need is to group it by dates. Which date from the date range I passed that they didnt post? That is what I want to know. — Nadiow White, Mar 09 '20 at 17:48
Please edit your questions to include that detail, don't put it in comments. — miken32, Mar 09 '20 at 17:51
Your code that you have now returns all users that don't have *any* posts between start and end days. So your list of names will look the same for every day. — miken32, Mar 09 '20 at 21:24

spencer7593 · Answer 1 · 2020-03-09T17:03:37.613

we could use an antijoin pattern

briefly... a query to return all dates and users, with an outer join to include matching rows from posts, and then exclude all rows that had a matching row from posts, leaving rows that didnt't have a matching row in posts

As a starting point, we can get a Cartesian product (cross join) of all dates with all users.

SELECT d.dt
     , u.user_name
  FROM ( SELECT '2018-01-01' + INTERVAL 0 DAY AS dt
         UNION ALL SELECT '2018-01-01' + INTERVAL 1 DAY
         UNION ALL SELECT '2018-01-01' + INTERVAL 2 DAY
         UNION ALL SELECT '2018-01-01' + INTERVAL 3 DAY
       ) d
 CROSS
  JOIN `USERS` u
 ORDER
    BY d.dt
     , u.user_name

(To return rows from a query, we need a source for those rows. We can't get "missing" rows returned from just the POSTS table. We need the full set of rows, then we can compare to the contents of the POSTS table.)

We we can add outer join, to identify matching rows, then exclude rows that had a match.

SELECT d.dt
     , u.user_name
  FROM ( SELECT '2018-01-01' + INTERVAL 0 DAY AS dt
         UNION ALL SELECT '2018-01-01' + INTERVAL 1 DAY
         UNION ALL SELECT '2018-01-01' + INTERVAL 2 DAY
         UNION ALL SELECT '2018-01-01' + INTERVAL 3 DAY
       ) d
 CROSS
  JOIN `USERS` u

    -- anti-join, exclude rows that have a matching row in posts
  LEFT
  JOIN `POSTS` p
    ON p.user_name = u.user_name
   AND p.dt        = d.dt
 WHERE p.dt IS NULL

 ORDER
    BY d.dt
     , u.user_name

The example query returns each individual date, along with users that didn't have a post on that individual date. That satisfies one possible interpretation of the rather vague specification "who didn't post from ... to ... and group it by dates"

For this query to return the expected results, we could craft sample data:

USERS   
user_name
--------- 
Ana
Charlie
John
Lucas
Sandra

and

POSTS
user_name  dt
---------  ----------
Charlie    2020-01-01
Lucas      2020-01-01
Charlie    2020-01-02
John       2020-01-02
Lucas      2020-01-02
Ana        2020-01-03
Sandra     2020-01-03
John       2020-01-04
Lucas      2020-01-04
Sandra     2020-01-04

We could use a NOT EXISTS with a correlated subquery, in place of the anti-join, to return equivalent results ...

SELECT d.dt
     , u.user_name
  FROM ( SELECT '2018-01-01' + INTERVAL 0 DAY AS dt
         UNION ALL SELECT '2018-01-01' + INTERVAL 1 DAY
         UNION ALL SELECT '2018-01-01' + INTERVAL 2 DAY
         UNION ALL SELECT '2018-01-01' + INTERVAL 3 DAY
       ) d
 CROSS
  JOIN `USERS` u

 WHERE NOT EXISTS
       ( SELECT 1
           FROM `POSTS` p
          WHERE p.user_name = u.user_name
            AND p.dt        = d.dt
       )

 ORDER
    BY d.dt
     , u.user_name

Are you sure this would work? I am trying to adapt your query with my table and fields. What do you mean when you use: AND p.dt = d.dt ?? Only POST has a data field btw. And would this work if it is a datetime field? — Nadiow White, Mar 09 '20 at 16:49
without a specification, without the definition of the table, we are just guessing. note that the sample table I provided has a column dt of `DATE` datatype. If your `POSTS` table has a DATETIME or TIMESTAMP or VARCHAR datatype column from which you can tell what day a post was on, then of course the query could be modified. Use whatever condition you would use in a query to get the `POSTS` that a `user_name` had on a particular day, e,g, `2020-01-03`, and adapt that. — spencer7593, Mar 09 '20 at 16:55
Normative pattern for determining if a DATETIME `dt` falls on a particular date is a range comparison, for example, `p.dt >= '2018-01-03' AND p.dt < '2018-01-04'`. — spencer7593, Mar 09 '20 at 16:58
If `POSTS` table `dt` column is `DATETIME`, then I would do `AND p.dt >= d.dt + INTERVAL 0 DAY AND p.dt < d.dt + INTERVAL 1 DAY` ... just like I would do if I was checking for posts on a particular day... *"easy select all users who posted from ... to ... "* — spencer7593, Mar 09 '20 at 16:59
Thank you a lot and I am sorry for not being that clear. I am all new to mysql and I am in a rush to finish this task. the POST table has the user_id field, but it also has the date field which is datetime type. Also I didnt understand the use of the interval thing. Couldnt I just use a range of date? — Nadiow White, Mar 09 '20 at 17:04
The query need a row source for the date values, Consider a date value where none of the users post. There needs to be a source for that row. In place of the inline view `d` we could substitute a calendar table that returns all dates in the range `SELECT cal.dt FROM cal WHERE cal.dt >= '2020-01-01' AND cal.dt < '2020-01-01' + INTERVAL 4 DAY`. The point remains the same... we need a rowsource that returns those date values, ... a SQL query isn't going to materialize rows there isn't a source for ... — spencer7593, Mar 09 '20 at 17:07
The interval thing is really simple... it's a way of adding/subtractin an incremental amount of time to a DATE or DATETIME datatype... `SELECT NOW() + INTERVAL -1 YEAR AS one_year_ago, NOW() + INTERVAL 2 HOUR as two_hours_from_now` , or we can use the more clumsy ADDDATE or DATE_ADD functions https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_adddate https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add — spencer7593, Mar 09 '20 at 17:12

How to select all USERS who didnt POST, grouped by dates?

1 Answers1