CODEWITHSUNDEEP
phpmysqllaravel
mitch2k
mitch2k

Reputation: 526

Where clause not respected?

I have a problem that a where clause in my query is not respected. This is the query I am using:

Capsule::table('tblhosting')
        ->select('*','tbldomains.nextduedate as domainnextduedate' , 'tblhosting.nextduedate as hostingnextduedate')
        ->join('tblclients', 'tblhosting.userid', '=', 'tblclients.id')
        ->join('tbldomains', 'tblhosting.domain', '=', 'tbldomains.domain')
        ->where('tblhosting.nextduedate', '!=', 'tbldomains.nextduedate')
        ->where('tbldomains.status', '=', 'Active')
        ->where('tblhosting.termination_date', '=', '0000-00-00')               
        ->get()

If I do a vardump, I get these values:

    [56] => stdClass Object
    (
        [id] => 406
        [userid] => 9
        [orderid] => 730
        [packageid] => 35
        [server] => 9
        [regdate] => 2016-12-23
        [domain] => xxx.net
        [paymentmethod] => banktransfer
        [firstpaymentamount] => 0.00
        [amount] => 0.00
        [billingcycle] => Annually
        [nextduedate] => 2017-12-23
        [nextinvoicedate] => 2017-12-23
        [termination_date] => 0000-00-00
        [completed_date] => 0000-00-00
        [domainstatus] => Active            
        [domainnextduedate] => 2017-12-23
        [hostingnextduedate] => 2017-12-23
    )

As you can see, tbldomains.nextduedate and tbldomains.nextduedate are both '2017-12-23', so they shouldn't have been returned, since I use

->where('tblhosting.nextduedate', '!=', 'tbldomains.nextduedate')

I thought it could be because I use aliases, but when I try 

->where('domainnextduedate', '!=', 'hostingnextduedate')

I get a PDO error:

Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'domainnextduedate' in 'where clause''

I am not sure what I am missing..

Thanks!

Upvotes: 0

Views: 87

Answers (2)

mrhn
mrhn

Reputation: 18916

The problem is instead of believing the right column is a column, it parses it as a string, so the result of the where is gonna be parsed to 2017-12-23 = tbldomains.nextduedate. To work around this you have to use whereRaw.

->whereRaw('tblhosting.nextduedate <> tbldomains.nextduedate')

EDIT

In Laravel you use whereDate, when dealing with dates in queries. So your Query should look something like this:

->whereDate('tblhosting.nextduedate', '!=', 'tbldomains.nextduedate')

Upvotes: 1

LC Yoong
LC Yoong

Reputation: 1842

You can give this code a try:

->whereColumn('tblhosting.nextduedate', '!=', 'tbldomains.nextduedate')

Upvotes: 2

Related Questions