Database / Capsule - Where Conditions

[Fr3DBr]

Hi, guys.

 

Why the database interaction with Capsule, isn't working the same way as in Laravel ?

 

Capsule::table('tblinvoices')->where('status', 'Unpaid')->where('duedate', '<=', 'DATE(DATE_ADD(NOW(), INTERVAL -2 DAY))')->get()

 

When I use this statement, I'm getting invoices that I'm not supposed to, because it is ignoring the "<=" operator, any thoughts of why is this ?

 

Also tried this one:

 

Capsule::table('tblinvoices')->where([
                                                       ['status','=','Unpaid'],
                                                       ['duedate', '<=', 'DATE(DATE_ADD(NOW(), INTERVAL -2 DAY))'],
                                                       ])->get()

 

Where the result is still the same, getting invoices with a duedate of 2017/04/02 when it wasn't supposed to happen.

 

Ok, I could only fix it, by using whereRaw():

 

Capsule::table('tblinvoices')->whereRaw("status = 'Unpaid' AND duedate <= DATE(DATE_ADD(NOW(), INTERVAL -2 DAY))")->get()

 

But actually, it was supposed to work the way I've shown above before, wasn't it ?

Edited April 3, 2017 by Fr3DBr

[brian!]

is it the less than, or the date part of the where statement ? I suspect the latter....

 

you might want to take a look at the Carbon documentation for using dates like this.

[markhughes]

You are looking for whereDate - WHMCS uses the queries outlined in Laravel here: https://laravel.com/docs/5.4/queries

 

You can use whereDate, for example:

 

$invoices = Capsule::table('tblinvoices')
   ->whereDate('duedate', '2016-12-31')
   ->get()

 

So we can also use Carbon objects here - I am kind of obsessed with making my code look/work beautifully, and carbon helps do this!

 

Is this maybe what you're looking for?

 

use Carbon;

$invoices = Capsule::table("tblinvoices")
   ->where("status", "Unpaid")
   ->whereDate("duedate", "<=", Carbon::now()->subDays(2))
   ->get()

 

We can be better though, you know WHMCS has the Invoice class so you don't even have to play with the database?

 

use Carbon;
use WHMCS\Billing\Invoice;

// You don't have to order by, this is just an example 
$invoices = Invoice::orderBy("id", "desc")
   ->where("status", "Unpaid")
   ->whereDate("duedate", "<=", Carbon::now()->subDays(2))
   ->get();

foreach ($invoices as $invoice) {
   // these are all Invoice classes
}

 

Hopefully that helps