Laravel $q->where() between dates where date 根据条件 日期

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);
});

up vote43down voteaccepted

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(); shareimprove this answer edited Jul 7 '15 at 12:11 Michel Ayres 3,10963966 answered Jul 18 '14 at 12:26 Tom 2,46921022

6   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 add a comment

Did you find this question interesting? Try our newsletter

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

  

up vote10down vote	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(); shareimprove this answer answered Mar 2 '15 at 13:01 Edmund Sulzanok 616617
	3   Good solution. Note that Carbon is an extension of the DateTime Class, though. If you replace DateTimewith Carbon in your solution, the result is exactly the same. – Loek Aug 16 '16 at 10:30 add a comment

up vote2down vote

@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. shareimprove this answer answered Oct 27 '15 at 9:27 Jaykumar Patil 8819

add a comment

up vote-1down vote	Kindly not that  whereBetween('date',$start_date,$end_date) is not inclusive of the first date. eg if selecting from data 1 to 20. Laravel will return records from date 2. A trick for this is to reduce the $start_date by on day ie  $start_date->subDay(); shareimprove this answer answered Jun 1 '15 at 8:37 Edwin M 14512
	5   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