CODEWITHSUNDEEP
mysqllaravellaravel-5
Adam
Adam

Reputation: 29019

Timestamp comparison on where clause randomly working?

I have an old database where the column FastStart_start and FastStart_End are varchars.

Using the Laravel query builder, the following query

$referrals = \DB::table('users')->join('order_details', 'order_details.user_id', 'users.id')
            ->join('users as sponsor', 'sponsor.id', '=', 'users.sponsor_id')
            //->where('order_details.created_at', '>=', 'sponsor.FastStart_Start')
            ->where('order_details.created_at', '<=', 'sponsor.FastStart_End')
            ->select('users.username', 'users.email',  'order_details.created_at', 'users.sponsor_id',
                 'sponsor.FastStart_Start', 'sponsor.FastStart_End')
            ->get();

gives this outcome:

 #items: array:3 [
    0 => {#2792
      +"username": "annalise92"
      +"email": "[email protected]"
      +"created_at": "2020-06-15 21:32:23"
      +"sponsor_id": 2085929
      +"FastStart_Start": "2020-06-01 00:00:00"
      +"FastStart_End": "2020-06-30 00:00:00"
    }
    1 => {#2832
      +"username": "nbarton"
      +"email": "[email protected]"
      +"created_at": "2020-06-15 21:32:23"
      +"sponsor_id": 2085929
      +"FastStart_Start": "2020-06-01 00:00:00"
      +"FastStart_End": "2020-06-30 00:00:00"
    }
    2 => {#2836
      +"username": "maxine56"
      +"email": "[email protected]"
      +"created_at": "2020-06-15 21:32:23"
      +"sponsor_id": 2085929
      +"FastStart_Start": "2020-06-01 00:00:00"
      +"FastStart_End": "2020-06-30 00:00:00"
    }
  ]

From the outcome its clear that FastStart_Start is less then order_details.created_at.

However, if I uncomment the line

//->where('order_details.created_at', '>=', 'sponsor.FastStart_Start')

from the query builder, the result set will be empty.

How is this possible? Does it have to do with the fact that FastStart_Start is a varchar column? If so, why does it not affect the comparison with FastStart_End?

Using toSql command I see that query builder is creating this query:

select users.username
     , users.email
     , order_details.created_at
     , users.sponsor_id
     , sponsor.FastStart_Start
     , sponsor.FastStart_End 
  from users 
  join order_details 
    on order_details.user_id = users.id 
  join users sponsor 
    on sponsor.id = users.sponsor_id 
 where order_details.created_at >= ? 
   and order_details.created_at <= ?

Any idea why the resulting set is empty if I uncomment the where line?

EDIT: The tosql result for the other query is:

select users.username
     , users.email
     , order_details.created_at
     , users.sponsor_id
     , sponsor.FastStart_Start
     , sponsor.FastStart_End 
  from users 
  join order_details 
    on order_details.user_id = users.id 
  join users sponsor 
    on sponsor.id = users.sponsor_id 
 where order_details.created_at <= ?

Upvotes: 1

Views: 77

Answers (1)

alariva
alariva

Reputation: 2139

It is related to the fact that the final SQL statement is treating sponsor.FastStart_Start as a literal string.

The reason for your query working is that from the string comparison perspective:

'2020-06-15 21:32:23' >= 'sponsor.FastStart_Start' is FALSE

as well as

'2020-06-15 21:32:23' >= 'anystring' is also FALSE

but also

'2020-06-15 21:32:23' <= 'sponsor.FastStart_Start' is TRUE

as well as

'2020-06-15 21:32:23' <= 'anystring' is also TRUE

Solution 1 (Preferred)

Use the ->whereColumn() method for performing your comparison, so that you tell Laravel that you don't want to treat your value as a string literal. This is documented here

$referrals = \DB::table('users')->join('order_details', 'order_details.user_id', 'users.id')
            ->join('users as sponsor', 'sponsor.id', '=', 'users.sponsor_id')
            ->whereColumn('order_details.created_at', '>=', 'sponsor.FastStart_Start')
            ->whereColumn('order_details.created_at', '<='. 'sponsor.FastStart_End')
            ->select('users.username', 'users.email',  'order_details.created_at', 'users.sponsor_id',
                 'sponsor.FastStart_Start', 'sponsor.FastStart_End')
            ->get();

Solution 2 (Alternative)

Use a RAW clause with the ->whereRaw() method:

$referrals = \DB::table('users')->join('order_details', 'order_details.user_id', 'users.id')
            ->join('users as sponsor', 'sponsor.id', '=', 'users.sponsor_id')
            ->whereRaw('order_details.created_at >= sponsor.FastStart_Start')
            ->whereRaw('order_details.created_at <= sponsor.FastStart_End')
            ->select('users.username', 'users.email',  'order_details.created_at', 'users.sponsor_id',
                 'sponsor.FastStart_Start', 'sponsor.FastStart_End')
            ->get();

Both solutions should lead you to the same results, while the treatment at Query building level is slightly different.

Upvotes: 1

Related Questions