Zane Claes Zane Claes - 1 month ago 7
MySQL Question

Extending an ActiveRecord Polymorphic Association with JOIN statements?

My tables are set up such that

Child
has a
1:1
relationship with
Parent
.

They share a primary key (
id
):


  • Parent
    has
    id
    and
    type
    and
    name

  • Child
    has
    id
    and
    health



The
Child
is a polymorphic inheritance of the
Parent
. My goal is that
Child.find(1)
should return a
Child
object which responds to both
name
and
health
. The SQL statement would hypothetically look something like this:

SELECT parents.id, parents.name, childs.health FROM parents
LEFT JOIN childs ON childs.id = Parents.id
WHERE parents.id = 1 AND parents.type = 'Child' LIMIT 1


Thus I've attempted to use Polymorphic Inheritance in ActiveRecord:

class Parent < ApplicationRecord
class Child < Parent


When I attempt to execute
Child.find(1)
, I see:

SELECT `parents`.* FROM `parents` WHERE `parents`.`type` IN ('Child') AND `parents`.`ID` = 1 LIMIT 1
=> #<Child id: 1, type: "Child", name: "Hello">


Notably, there's no
JOIN
on the
child
table, yet I receive a
Child
object back. This leads to the unexpected behavior that the
Child
object does not respond to
health
. Curiously, if I add an explicit table association to the
Child
class (
self.table_name = "childs"
), then the query pattern changes to:

> c = Child.find(1)
Obtainable Load (0.3ms) SELECT `childs`.* FROM `childs` WHERE `childs`.`ID` = 2 LIMIT 1


Now I can access the
health
, but not the
type
or
name
.

How can I correctly create this JOIN association such that an attempt to load a
Child
object successfully JOINs the data from the parent?




Edit: these tables were created outside of an ActiveRecord migration (they are also accessed by other, pre-existing, non-Ruby applications) so I have no ability to change their schema. I can think of some fancy metaprogramming approaches, like responding to
method_missing
, that might let me lazy-load the missing attributes through a join... but I'm afraid I'll end up having to re-implement a bunch of ActiveRecord, like
delete
,
create
, etc. (which will lead to bugs). So I'm looking for some native/clean(ish) way to accomplish this.

Answer

This is not a typical Rails polymorphic association as described here: http://guides.rubyonrails.org/association_basics.html#polymorphic-associations

So, in this case, when tables were created earlier by some other app, I suggest that you do something like this:

class Child < ApplicationRecord
  self.table_name = "childs"
  belongs_to :parent, foreign_key: :id, dependent: :destroy
  delegate :name, :type, to: :parent
  delegate :name=, to: :parent, allow_nil: true

  after_initialize do
    self.build_parent(type: "Child") if parent.nil?
  end
end

class Parent < ApplicationRecord
  self.inheritance_column = 'foo' # otherwise, type column will be used for STI
  has_one :child, foreign_key: :id
  delegate :health, to: :child
end

and now you can access the health, type and name:

> c = Child.joins(:parent).find(1)
  Child Load (0.2ms)  SELECT  "childs".* FROM "childs" INNER JOIN "parents" ON "parents"."id" = "childs"."id" WHERE "childs"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
=> #<Child id: 1, health: "Good", created_at: "2016-10-27 21:42:55", updated_at: "2016-10-27 21:44:08">
irb(main):002:0> c.health
=> "Good"
irb(main):003:0> c.type
  Parent Load (0.1ms)  SELECT  "parents".* FROM "parents" WHERE "parents"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
=> "Child"
irb(main):004:0> c.name
=> "Hello"

and similar is for the parent:

p = Parent.joins(:child).find(1)
  Parent Load (0.1ms)  SELECT  "parents".* FROM "parents" INNER JOIN "childs" ON "childs"."id" = "parents"."id" WHERE "parents"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
=> #<Parent id: 1, type: nil, name: "Hello", created_at: "2016-10-27 21:40:35", updated_at: "2016-10-27 21:40:35">
irb(main):003:0> p.name
=> "Hello"
irb(main):004:0> p.health
  Child Load (0.1ms)  SELECT  "childs".* FROM "childs" WHERE "childs"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
=> "Good"

If you prefer, you can specify LEFT JOIN like this:

irb(main):003:0> p = Parent.joins("LEFT JOIN childs ON (childs.id = parents.id)").select("parents.id, parents.name, childs.health").find(1)
  Parent Load (0.2ms)  SELECT  parents.id, parents.name, childs.health FROM "parents" LEFT JOIN childs ON (childs.id = parents.id) WHERE "parents"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
=> #<Parent id: 1, name: "Hello">
irb(main):004:0> p.name
=> "Hello"
irb(main):005:0> p.health
  Child Load (0.1ms)  SELECT  "childs".* FROM "childs" WHERE "childs"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
=> "Good"