mardi 27 octobre 2020

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": "luisa.grimes@example.net"
      +"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": "gibson.ibrahim@example.com"
      +"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": "cwuckert@example.net"
      +"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 <= ?


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire