Add computable column to multi-table select clause with eager_load in Ruby on Rails Activerecord

Question

I have a query with a lot of joins and I'm eager_loading some of associations at the time. And I need to compute some value as attribute of one of models.

So, I'm trying this code:

ServiceObject
  .joins([{service_days: :ou}, :address])
  .eager_load(:address, :service_days)
  .where(ous: {id: OU.where(sector_code: 5)})
  .select('SDO_CONTAINS(ous.service_area_shape, SDO_GEOMETRY(2001, 8307, sdo_point_type(addresses.lat, addresses.lng, NULL), NULL, NULL) ) AS in_zone')

Where SQL function call in select operates data from associated addresses and ous tables.

I'm getting next SQL (so my in_zone column getting calculated and returned as first column before other columns for all eager_loaded models):

SELECT SDO_CONTAINS(ous.service_area_shape, SDO_GEOMETRY(2001, 8307, sdo_point_type(addresses.lat, addresses.lng, NULL), NULL, NULL) ) AS in_zone, "SERVICE_OBJECTS"."ID" AS t0_r0, "SERVICE_OBJECTS"."TYPE" AS t0_r1, <omitted for brevity> AS t2_r36 FROM "SERVICE_OBJECTS" INNER JOIN "SERVICE_DAYS" ON "SERVICE_DAYS"."SERVICE_OBJECT_ID" = "SERVICE_OBJECTS"."ID" INNER JOIN "OUS" ON "OUS"."ID" = "SERVICE_DAYS"."OU_ID" INNER JOIN "ADDRESSES" ON "ADDRESSES"."ID" = "SERVICE_OBJECTS"."ADDRESS_ID" WHERE "OUS"."ID" IN (SELECT "OUS"."ID" FROM "OUS" WHERE "OUS"."SECTOR_CODE" = :a1)  [["sector_code", "5"]]

But it seems like that in_zone isn't accessible from either model used in query.

I need to have calculated in_zone as attribute of ServiceObject model object, how I can accomplish that?

Ruby on Rails 4.2.6, Ruby 2.3.0, oracle_enhanced adapter 1.6.7, Oracle 12.1

Answer 1

I have successfully replicated your issue and it turns out that this is a known issue in Rails. The problem is that when using eager_load, Rails maps the columns of all eager-loaded tables into table and column aliases in the form of t0_r0, t0_r1, etc... (you can see these in the SQL that you pasted in the question). And while doing that, it simply ignores the custom columns in the select, probably because it cannot determine which eager-loaded table it should attribute the custom column to. It is sad that this issue is open for more than 2 years now...

Nevertheless I think I found a workaround. It seems that if you don't eager load the tables but manually join them (with joins), you can as well include them (with includes) and the custom columns will be returned as there will be no column aliasing taking place. The point is that you must not use associations in the joins clauses but you have to specify the joins yourself. Also note that you must specify all columns from the main table in the select manually too (see the service_objects.* in the select).

Try the following approach:

ServiceObject
  .joins('INNER JOIN "SERVICE_DAYS" ON "SERVICE_DAYS"."SERVICE_OBJECT_ID" = "SERVICE_OBJECTS"."ID"')
  .joins('INNER JOIN "OUS" ON "OUS"."ID" = "SERVICE_DAYS"."OU_ID"')
  .joins('INNER JOIN "ADDRESSES" ON "ADDRESSES"."ID" = "SERVICE_OBJECTS"."ADDRESS_ID"')
  .includes(:service_days, :address)
  .where(ous: {id: OU.where(sector_code: 5)})
  .select('service_objects.*, SDO_CONTAINS(ous.service_area_shape, SDO_GEOMETRY(2001, 8307, sdo_point_type(addresses.lat, addresses.lng, NULL), NULL, NULL) ) AS in_zone')

The computation in the select should still work as the related tables are joined together but there should be no column aliasing present.

Of course this approach means that you'll get three queries instead of just one but unless you return a huge amount of records, the following two queries run by the includes clause should be very fast as they simply load the relevant records using foreign keys.

Answer 2

That monkey patch helped @Envek:

module ActiveRecord
  Base.send :attr_accessor, :_row_

  module Associations
    class JoinDependency
      JoinBase && class JoinPart
        def instantiate_with_row(row, *args)
          instantiate_without_row(row, *args).tap { |i| i._row_ = row }
        end; alias_method_chain :instantiate, :row
      end
    end
  end
end

then it is possible to do:

ServiceObject
  .joins([{service_days: :ou}, :address])
  .eager_load(:address, :service_days)
  .where(ous: {id: OU.where(sector_code: 5)})
  .select('SDO_CONTAINS(ous.service_area_shape, SDO_GEOMETRY(2001, 8307, sdo_point_type(addresses.lat, addresses.lng, NULL), NULL, NULL) ) AS in_zone')
  .first
  ._row_['in_zone']