1

For my Log model in a Laravel application, I am trying to select all records that are "in sequence within a certain diff". What I mean with that is selecting all consequetive logs as long as for the given datetime column the difference between the previous and the next record is within the given time difference limit.

See the query below with some made-up syntax to try to get my intentions across.

Log::query()
  ->whereDateDiff('created_at', '<', '2 hours') // This method does not exist.
  ->get();

So suppose I have the following data. Here I would like to select the records up to and including row 4, since the difference in time between row 4 and 5 is bigger than 2 hours.

----------------------------
| id | created_at          |
----------------------------
| 1  | 2022-11-17 08:01:52 |
| 2  | 2022-11-17 08:31:14 |
| 3  | 2022-11-17 09:02:49 |
| 4  | 2022-11-17 09:03:30 |
| 5  | 2022-11-17 16:21:12 |
| 6  | 2022-11-17 17:01:27 |
----------------------------

I have searched the internet for an Eloquent approach, but I did not find a way to define some kind of query yet. Learning from this Stack Overflow question ("Date Difference between consecutive rows"), we know how to calculate the difference between consecutive rows in SQL in the SELECT statement, but I would like to have this in the WHERE. I hope this is possible within one query, preferably using an Eloquent syntax, but I am afraid this is might be only possible within the PHP layer. What is the best approach for this problem?

Robin Bastiaan
  • 572
  • 2
  • 8
  • 21

1 Answers1

0

I would try to solve this by using more than 1 condition. For example:

Log::where('created_at', '>=', $from)->where('created_at', '<=', $to)->get();

I suppose it won't be difficult for you to produce the $from and $to variables. One of them is going to be the specific time that you want and then using Carbon, you can provide the other variable. Then you can provide them in your query.

gkouem
  • 51
  • 3
  • The challenge is that I do not know the `$to` because I do not know when the sequence is broken. This can be 2 hours after first and only record, but it can also be days in the future because the sequence keeps going. – Robin Bastiaan Nov 17 '22 at 15:10