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