Rails handles deeply nested sql


Here’s a quick summary of the rails code Greg mentioned me sharing at the RubyHam Code Day. For an insurance project I’ve defined the following relationships:

class BmicQuote < ActiveRecord::Base
  belongs_to :agent
  has_many :quote_items, :order => :position
  has_many :insurances, :as => :insurable
  has_many :insureds, :through => :insurances
class QuoteItem < ActiveRecord::Base
  belongs_to :bmic_quote
  acts_as_list :scope => :bmic_quote_id
  has_many :item_perils
  has_many :quote_item_liens, :order => :position
  has_many :liens, :through => :policy_item_liens
class ItemPeril < ActiveRecord::Base
  belongs_to :quote_item
class Insurance < ActiveRecord::Base
  belongs_to :insured
  belongs_to :insurable, :polymorphic => true
class Insured < ActiveRecord::Base
  has_many :insurances, :as => :insurable
  has_many :bmic_quotes, :through => :insurances
class Agent < ActiveRecord::Base
  has_many :bmic_quotes
class Lien < ActiveRecord::Base
  belongs_to :lienholder
  has_many :quote_items, :through => :quote_item_liens
  has_many :quote_item_liens, :dependent => :destroy
class Lienholder < ActiveRecord::Base
  has_many :liens
  has_one :address, :as => :addressable
class Address < ActiveRecord::Base
  belongs_to :addressable, :polymorphic => true

From these relationships, I wanted to generate a json object containing almost the whole object tree but wanted to accomplish this with one rails sql call. Possible, absolutely!! A blog post got me started, but did not quite go to the depths I needed. After a little tinkering, the following rails code worked:

                   :include => [ {'insurances' => { 'insured' => 'address' } },
                                 {'agent' => 'address' },
                                 {'policy_items' => ['item_perils',
                                 { 'liens' => { 'lienholder' => 'address' } } ] } ])

generating all this sql:

SELECT bmic_quotes.`id` AS t0_r0, bmic_quotes.`agent_id` AS t0_r1, bmic_quotes.`policy_number` AS t0_r2, bmic_quotes.`type_of_policy` AS t0_r3, bmic_quotes.`starts_on` AS t0_r4, bmic_quotes.`expires_on` AS t0_r5, bmic_quotes.`term` AS t0_r6, bmic_quotes.`rewrite_of` AS t0_r7, bmic_quotes.`city_id` AS t0_r8, bmic_quotes.`county_id` AS t0_r9, bmic_quotes.`description_for_dec` AS t0_r10, bmic_quotes.`deductible` AS t0_r11, bmic_quotes.`bill_to` AS t0_r12, bmic_quotes.`dec_page` AS t0_r13, bmic_quotes.`created_at` AS t0_r14, bmic_quotes.`processed_at` AS t0_r15, bmic_quotes.`updated_at` AS t0_r16, bmic_quotes.`amount_paid` AS t0_r17, bmic_quotes.`location_code` AS t0_r18, bmic_quotes.`occupancy_code` AS t0_r19, bmic_quotes.`protection_class` AS t0_r20, bmic_quotes.`number_of_families` AS t0_r21, bmic_quotes.`converted_to_policy_at` AS t0_r22, bmic_quotes.`forms_code` AS t0_r23, bmic_quotes.`address_id` AS t0_r24, bmic_quotes.`policy_id` AS t0_r25, insurances.`id` AS t1_r0, insurances.`insured_id` AS t1_r1, insurances.`insurable_type` AS t1_r2, insurances.`insurable_id` AS t1_r3, insureds.`id` AS t2_r0, insureds.`first_name` AS t2_r1, insureds.`last_name` AS t2_r2, insureds.`name` AS t2_r3, insureds.`email` AS t2_r4, insureds.`dba_name` AS t2_r5, insureds.`version` AS t2_r6, insureds.`middle_name` AS t2_r7, addresses.`id` AS t3_r0, addresses.`line1` AS t3_r1, addresses.`line2` AS t3_r2, addresses.`city` AS t3_r3, addresses.`state` AS t3_r4, addresses.`zip_code` AS t3_r5, addresses.`addressable_id` AS t3_r6, addresses.`addressable_type` AS t3_r7, addresses.`version` AS t3_r8, agents.`id` AS t4_r0, agents.`first_name` AS t4_r1, agents.`last_name` AS t4_r2, agents.`name` AS t4_r3, agents.`email` AS t4_r4, agents.`phone1` AS t4_r5, agents.`phone2` AS t4_r6, agents.`fax` AS t4_r7, agents.`agent_number` AS t4_r8, agents.`address_id` AS t4_r9, addresses_agents.`id` AS t5_r0, addresses_agents.`line1` AS t5_r1, addresses_agents.`line2` AS t5_r2, addresses_agents.`city` AS t5_r3, addresses_agents.`state` AS t5_r4, addresses_agents.`zip_code` AS t5_r5, addresses_agents.`addressable_id` AS t5_r6, addresses_agents.`addressable_type` AS t5_r7, addresses_agents.`version` AS t5_r8, quote_items.`id` AS t6_r0, quote_items.`bmic_quote_id` AS t6_r1, quote_items.`protection_class` AS t6_r2, quote_items.`occupancy_code` AS t6_r3, quote_items.`item_type` AS t6_r4, quote_items.`construction` AS t6_r5, quote_items.`coverage` AS t6_r6, quote_items.`address_line1` AS t6_r7, quote_items.`address_line2` AS t6_r8, quote_items.`city` AS t6_r9, quote_items.`state` AS t6_r10, quote_items.`zip_code` AS t6_r11, quote_items.`sequence` AS t6_r12, quote_items.`item_code` AS t6_r13, quote_items.`updated_at` AS t6_r14, quote_items.`position` AS t6_r15, quote_items.`number_of_stories` AS t6_r16, quote_items.`description` AS t6_r17, quote_items.`additional_details` AS t6_r18, item_perils.`id` AS t7_r0, item_perils.`peril` AS t7_r1, item_perils.`premium` AS t7_r2, item_perils.`alternate_coverage` AS t7_r3, item_perils.`perilable_id` AS t7_r4, item_perils.`perilable_type` AS t7_r5, liens.`id` AS t8_r0, liens.`lienholder_id` AS t8_r1, liens.`lien_number` AS t8_r2, lienholders.`id` AS t9_r0, lienholders.`name` AS t9_r1, lienholders.`address_id` AS t9_r2, lienholders.`legacy_identifier` AS t9_r3, addresses_lienholders.`id` AS t10_r0, addresses_lienholders.`line1` AS t10_r1, addresses_lienholders.`line2` AS t10_r2, addresses_lienholders.`city` AS t10_r3, addresses_lienholders.`state` AS t10_r4, addresses_lienholders.`zip_code` AS t10_r5, addresses_lienholders.`addressable_id` AS t10_r6, addresses_lienholders.`addressable_type` AS t10_r7, addresses_lienholders.`version` AS t10_r8 FROM bmic_quotes LEFT OUTER JOIN insurances ON insurances.insurable_id = bmic_quotes.id AND insurances.insurable_type = ‘BmicQuote’ LEFT OUTER JOIN insureds ON insureds.id = insurances.insured_id LEFT OUTER JOIN addresses ON addresses.addressable_id = insureds.id AND addresses.addressable_type = ‘Insured’ LEFT OUTER JOIN agents ON agents.id = bmic_quotes.agent_id LEFT OUTER JOIN addresses addresses_agents ON addresses_agents.addressable_id = agents.id AND addresses_agents.addressable_type = ‘Agent’ LEFT OUTER JOIN quote_items ON quote_items.bmic_quote_id = bmic_quotes.id LEFT OUTER JOIN item_perils ON item_perils.perilable_id = quote_items.id AND item_perils.perilable_type = ‘QuoteItem’ LEFT OUTER JOIN quote_item_liens ON (quote_item_liens.quote_item_id = quote_items.id) LEFT OUTER JOIN liens ON (liens.id = quote_item_liens.lien_id) LEFT OUTER JOIN lienholders ON lienholders.id = liens.lienholder_id LEFT OUTER JOIN addresses addresses_lienholders ON addresses_lienholders.addressable_id = lienholders.id AND addresses_lienholders.addressable_type = ‘Lienholder’ WHERE (bmic_quotes.id = 12)

That’s right, 11 joins and a whole lot of sql from only one long line of rails code. The great thing is someone probably knows an even better way to handle this using rails functionality.


No Responses Yet to “Rails handles deeply nested sql”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: