Laravel $q->where() between dates

Question

I am trying to get my cron to only get Projects that are due to recur/renew in the next 7 days to send out reminder emails. I've just found out my logic doesn't quite work.

I currently have the query:

$projects = Project::where(function($q){
    $q->where('recur_at', '>', date("Y-m-d H:i:s", time() - 604800));
    $q->where('status', '<', 5);
    $q->where('recur_cancelled', '=', 0);
});

However, I realized what I need to do is something like:

Psudo SQL:

SELECT * FROM projects WHERE recur_at > recur_at - '7 days' AND /* Other status + recurr_cancelled stuff) */

How would I do this in Laravel 4, and using the DATETIME datatype, I've only done this sort of thing using timestamps.

Update:

Managed to solve this after using the following code, Stackoverflow also helps when you can pull bits of code and look at them out of context.

$projects = Project::where(function($q){
    $q->where(DB::raw('recur_at BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()'));
    $q->where('status', '<', 5);
    $q->where('recur_cancelled', '=', 0);
});

Updated Question: Is there better way to do this in Laravel/Eloquent?

Update 2:

The first resolution ended up not been right after further testing, I have now resolved and tested the following solution:

$projects = Project::where(function($q){
    $q->where('recur_at', '<=', Carbon::now()->addWeek());
    $q->where('recur_at', '!=', "0000-00-00 00:00:00");
    $q->where('status', '<', 5);
    $q->where('recur_cancelled', '=', 0);
});

As far as I know, you *have* to use `DB::raw` so no, there is no other way to do so using Eloquent on its own. On the other hand, how come you're using a DATETIME instead of TIMESTAMP column? What I'm asking is unrelated to original question, but TIMESTAMP fits better in many use cases compared to DATETIME. — N.B., Jul 18 '14 at 12:13
@N.B. DATETIME seems to be the 'standard' time field that Laravel uses, so I kept with it. — Jono20201, Jul 18 '14 at 12:15
@MrShibby I think I kind of like the `where(DB::raw())` format, as it seems a little clearer. Is there any technical reason why `whereRaw()` is better? — Jono20201, Jul 18 '14 at 12:19
@Jono20201 - no, it's not the standard time field. Standard time field is, and always will be a `TIMESTAMP`. The reason is that TIMESTAMP is always in UTC, where with DATETIME you're supposed to know what timezone it came from so you can perform time conversion for a user that's in some other time zone. Even Eloquent uses timestamp type and not datetime. Both types show you the exact same formatted date so I would strongly advise you to use timestamp instead of datetime. — N.B., Jul 18 '14 at 12:31

score 63 · Accepted Answer · edited May 23 '17 at 10:31

63

You can chain your wheres directly, without function(q). There's also a nice date handling package in laravel, called Carbon. So you could do something like:

$projects = Project::where('recur_at', '>', Carbon::now())
    ->where('recur_at', '<', Carbon::now()->addWeek())
    ->where('status', '<', 5)
    ->where('recur_cancelled', '=', 0)
    ->get();

Just make sure you require Carbon in composer and you're using Carbon namespace (use Carbon\Carbon;) and it should work.

EDIT: As Joel said, you could do:

$projects = Project::whereBetween('recur_at', array(Carbon::now(), Carbon::now()->addWeek()))
    ->where('status', '<', 5)
    ->where('recur_cancelled', '=', 0)
    ->get();

edited May 23 '17 at 10:31

Community

1
1

answered Jul 18 '14 at 12:26

Tom

3,654
2
16
23

9

Looks good, but just an heads-up that you don't need the double where clauses, you can use whereBetween instead. – Joel Hinz Jul 18 '14 at 12:38
@JoelHinz Nice! Definetly going to use it in the future. A quick look in the api and thanks to you I found out that there's also orBetween and so on... – Tom Jul 18 '14 at 12:40
I managed to get the desired effect by using `$q->where('recur_at', '<=', Carbon::now()->addWeek());`. I prefer to use the `function($q)` as I think it makes the code easier for me to read. – Jono20201 Jul 18 '14 at 13:02
Oh, now I see that I gave date in the past, and You've wanted an upcoming week, sory for that, but You've got the point :) I've edited the answer – Tom Jul 18 '14 at 13:10
@Tom Great, ticked your answer. I spent a few minutes trying to figure the maths out with my business partner and using Carbon made it simpler. – Jono20201 Jul 18 '14 at 14:00

score 12 · Answer 2 · answered Mar 02 '15 at 13:01

12

Didn't wan to mess with carbon. So here's my solution

$start = new \DateTime('now');
$start->modify('first day of this month');
$end = new \DateTime('now');
$end->modify('last day of this month');

$new_releases = Game::whereBetween('release', array($start, $end))->get();

answered Mar 02 '15 at 13:01

Edmund Sulzanok

1,883
3
20
39

4

Good solution. Note that Carbon is an extension of the DateTime Class, though. If you replace `DateTime` with `Carbon` in your solution, the result is exactly the same. – Loek Aug 16 '16 at 10:30

score 3 · Answer 3 · answered Oct 27 '15 at 09:27

@Tom : Instead of using 'now' or 'addWeek' if we provide date in following format, it does not give correct records

$projects = Project::whereBetween('recur_at', array(new DateTime('2015-10-16'), new DateTime('2015-10-23')))
->where('status', '<', 5)
->where('recur_cancelled', '=', 0)
->get();

it gives records having date form 2015-10-16 to less than 2015-10-23. If value of recur_at is 2015-10-23 00:00:00 then only it shows that record else if it is 2015-10-23 12:00:45 then it is not shown.

Edwin M · Answer 4 · 2017-06-13T20:05:19.753

-1

Edited: Kindly note that
whereBetween('date',$start_date,$end_date)
is inclusive of the first date.

edited Jun 13 '17 at 20:05

answered Jun 01 '15 at 08:37

Edwin M

351
3
4

6

Not true. What you're probably seeing is an inaccuracy in the date time. Just do `$startDate->startOfDay();` and `$endDate->endOfDay()` otherwise it defaults to whatever time it is right now. – Troy Gilbert Jul 27 '15 at 19:38

Laravel $q->where() between dates

4 Answers4

Linked

Related