CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Shubham
Shubham

Reputation: 362

Raw sql query and query writting using active record query are giving different results

I am running two queries, in the first I run the raw sql query:

SELECT  drivers.*, shift_timing.* FROM "drivers" 
        LEFT JOIN(SELECT DISTINCT ON (driver_id) driver_id, 
        start_hour, start_minutes, end_hour, end_minutes FROM 
        shift_timings ORDER BY driver_id) AS shift_timing ON 
        drivers.id = shift_timing.driver_id WHERE 
        "drivers"."deleted_at" IS NULL  ORDER BY "drivers"."id" ASC LIMIT 1

which gives me the following output:

{"id"=>"1", "driver_name"=>"Narendra Kumar Soni", ...,
 "last_order_id"=>"31550", "last_shift_id"=>"74483", "driver_id"=>"1", 
 "start_hour"=>"7", "start_minutes"=>"0", "end_hour"=>"19", 
 "end_minutes"=>"0"}

but when I run the following query which translates exactly to raw sql query:

Driver.joins('LEFT JOIN(SELECT DISTINCT ON (driver_id) driver_id, 
              start_hour, start_minutes, end_hour, end_minutes FROM 
              shift_timings ORDER BY driver_id) 
              AS shift_timing ON drivers.id = 
              shift_timing.driver_id')
             .select('drivers.*, shift_timing.*').first

I get the following result:

#<Driver id: 1, driver_name: "Narendra Kumar Soni", ...,
, last_order_id: 31550, last_shift_id: 74483>

Basically the start_hour, start_minutes, end_hour, end_minutes columns are missing from the active record query result and the columns that are missing are actually columns of the shift timing table. Can someone please explain why is this happening and how can this be fixed?

Upvotes: 0

Views: 124

Answers (2)

Baldrick
Baldrick

Reputation: 24340

Either remove the select clause to get full ActiveRecord objects and use includes(:shift_timings) instead, or use pluck to get only the fields you want as an array:

Driver.joins('LEFT JOIN ...') # As in the question
      .pluck('drivers.driver_name, shift_timings.start_hour, shift_timings.start_minutes')

Upvotes: 1

andrykonchin
andrykonchin

Reputation: 2517

Fiels from joined tables usually can be accessed with ['name'] syntax, so it will look like driver['start_hour'].

But I think the proper way to solve the issue is just include shift_timings table like

Driver.includes(:shift_timings).

Upvotes: 1

Related Questions