Jump to content

Database / Capsule - Where Conditions


Recommended Posts

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:


                                                       ['duedate', '<=', 'DATE(DATE_ADD(NOW(), INTERVAL -2 DAY))'],


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 by Fr3DBr
Link to comment
Share on other sites

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')


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


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

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


Hopefully that helps :)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated