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

Answer 1

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