CODEWITHSUNDEEP
rubypostgresqlactiverecord
SimonMayer
SimonMayer

Reputation: 4916

Retrieving an object from two joinable tables in Active Record

I have a situation where I have a SQL table which pretty much 'parents' others. It is easiest to imagine in the example below where there are different types of clients (which need different fields of data to be stored) that all have a unique client ID.

CREATE TABLE clients (
  id serial NOT NULL,
  internal_ref character varying,
  address character varying,
  telephone_no character varying,
  CONSTRAINT pkey_clients PRIMARY KEY (id),
  CONSTRAINT uniq_clients_internal_ref UNIQUE (internal_ref)
)

CREATE TABLE partnerships (
  id serial NOT NULL,
  clients_id integer,
  name character varying,
  CONSTRAINT pkey_partnerships PRIMARY KEY (id),
  CONSTRAINT fkey_partnerships_clients_id FOREIGN KEY (clients_id)
    REFERENCES clients(id) MATCH FULL
      ON UPDATE NO ACTION
      ON DELETE NO ACTION
)

CREATE TABLE individuals (
  id serial NOT NULL,
  clients_id integer,
  forename character varying,
  surname character varying,
  CONSTRAINT pkey_individuals PRIMARY KEY (id),
  CONSTRAINT fkey_individuals_clients_id FOREIGN KEY (clients_id)
    REFERENCES clients(id) MATCH FULL
      ON UPDATE NO ACTION
      ON DELETE NO ACTION
)

I'm new to Active Record and not sure how (or if) I can elegantly implement it, so that I can find a business and I get the joined client data.

Currently I am trying:

class Client < ActiveRecord::Base
end
class Partnership < ActiveRecord::Base
  has_one :clients
end

output = Partnership.find(:all) # I only have one row in both tables, which is also joinable
p output # => [#<Partnership id: 1, name: "PART1", clients_id: 1>]

I need output to also have the joinable data, such as internal_ref, address etc. How can I do that?

Upvotes: 1

Views: 149

Answers (1)

Krule
Krule

Reputation: 6476

Easy. Your "child" models belongs_to "parent" model which can has_one or has_many "children"

Models:

# app/models/client.rb
class Client < ActiveRecord::Base
  has_one :partnership
  has_one :individual
end

# app/models/pertnership.rb
class Partnership < ActiveRecord::Base
  belongs_to :client
end

# app/models/individual.rb
class Individual < ActiveRecord::Base
  belongs_to :client
end

Controller:

@output = Partnerships.all

If you don't want to make additional query each time you try to access the Client you use eager loading:

@output = Partnerships.includes(:client).all

Or eager loading with both client and individual:

@output = Partnerships.includes(:client => :individual ).all

Then, in your view, you can easily access referenced client or even individual referenced in client.

<% @output.each do |partnership| %>
  <%= partneship.client.internal_ref %>
  <%= partneship.client.address %>
  <%= partneship.client.individual.forname %>
<% end %>

Upvotes: 3

Related Questions