query builder returns incorrect datetime compare query results

I have this query:

return DB::table('electricity_meters')
->where('electricity_meters.id', 16)
->leftJoin('service_instances', 'service_instances.electricity_meter_id', '=', 'electricity_meters.id')
->leftJoin('readings', 'electricity_meters.kwh_param_id', '=', 'readings.parameter_id')
->where('readings.taken_at', '>', 'service_instances.active_to')
->select([
    'readings.taken_at',
    'service_instances.active_to',
])
->get();

Pay attention to 'readings.taken_at', '>', 'service_instances.active_to', but somehow results are…

[
    {
        taken_at: "2021-02-16 23:59:59",
        active_to: "2021-03-28"
    },
    ...
]

what am I missing here?

Answer

I think you want to compare between two columns, if that, you should use whereColumn:

return DB::table('electricity_meters')
->where('electricity_meters.id', 16)
->leftJoin('service_instances', 'service_instances.electricity_meter_id', '=', 'electricity_meters.id')
->leftJoin('readings', 'electricity_meters.kwh_param_id', '=', 'readings.parameter_id')
->whereColumn('readings.taken_at', '>', 'service_instances.active_to')
->select([
    'readings.taken_at',
    'service_instances.active_to',
])
->get();

Leave a Reply

Your email address will not be published. Required fields are marked *