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

Question

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?

Are you sure these fields are actually missing ? It looks like they're just not printed in your console/debugger — Baldrick
– Baldrick, Commented Jul 24, 2015 at 15:45
No when I do the record query and ask for start_hour it runs an sql query and then shows me the correct value. I want all the values to be already be available after running the record query. Because if I loop over my table of 10000 drivers one by one to get the start hour I am doomed. — Shubham
– Shubham, Commented Jul 24, 2015 at 15:47

Baldrick · Accepted Answer · 2015-07-24 17:26:14Z

1

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')

edited Jul 24, 2015 at 17:26

answered Jul 24, 2015 at 16:26

Baldrick

24.4k6 gold badges76 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Baldrick Over a year ago

See andrykonchin's answer for the select

andrykonchin · Accepted Answer · 2015-07-24 16:35:44Z

1

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).

edited Jul 24, 2015 at 16:35

answered Jul 24, 2015 at 16:24

andrykonchin

2,5923 gold badges18 silver badges29 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related