Add computable column to multi-table select clause with eager_load

14 views

Skip to first unread message

Andrey Novikov

unread,

Jun 10, 2016, 9:54:12 AM6/10/16

to Ruby on Rails: Talk

Hi all!

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? Is it possible?