CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
quicklikerabbit
quicklikerabbit

Reputation: 3556

Return name in ActiveRecord relation along with foreign key id

I have a Sub-Component model which can belong to other sub-components. My Model looks like this:

class SubComponent < ApplicationRecord
  belongs_to :parent, class_name: "SubComponent", foreign_key: "parent_sub_component_id", optional: true
  has_many :child_sub_components, class_name: "SubComponent", foreign_key: "parent_sub_component_id"

  validates_presence_of :name
end

This model is fairly simple, it has a name field and a parent_sub_component_id which as the name suggests is an id of another SubComponent.

I'd like to generate a query that returns all of the SubComponents (with their id, name, and parent_sub_component_id) but also includes the actual name of it's parent_sub_component.

This seems like it should be pretty simple but for the life of me I can't figure out how to do it. I'd like for this query to be done in the database rather than doing an each loop in Ruby or something like that.

EDIT:

I'd like for the output to look something like this:

#<ActiveRecord::Relation [#<SubComponent id: 1, name: "Parent Sub", parent_sub_component_id: nil, parent_sub_component_name: nil created_at: "2017-07-07 00:29:37", updated_at: "2017-07-07 00:29:37">, #<SubComponent id: 2, name: "Child Sub", parent_sub_component_id: 1, parent_sub_component_name: "Parent Sub" created_at: "2017-07-07 00:29:37", updated_at: "2017-07-07 00:29:37">]>

Upvotes: 1

Views: 471

Answers (2)

Simple Lime
Simple Lime

Reputation: 11035

This is untested, but should get you started in the right direction, you can do this in Arel by doing something like

def self.execute_query
  parent_table = Arel::Table.new(:sub_component).alias
  child_table = Arel::Table.new(:sub_component)

child_table.join(parent_table, Arel::Nodes::OuterJoin).on(child_table[:parent_sub_component_id].eq(parent_table[:id]).project(child_table[:id], child_table[:name], parent_table[:id], parent_table[:name])
end

This results in a query like

SELECT "sub_component"."id", "sub_component"."name", "sub_component_2"."id", "sub_component_2"."name" FROM "sub_component" LEFT OUTER JOIN "sub_component" "sub_component_2" ON "sub_component"."parent_sub_component_id" = "sub_component_2"."id"

this is just off the top of my head by looking at Rails/Arel and probably needs a some work, but the query looks about what I would expect and this should get you going.

Upvotes: 0

Mate Solymosi
Mate Solymosi

Reputation: 5967

You can do this efficiently using an each loop if you use includes:

SubComponent.all.includes(:parent).each do |comp|
  comp.parent.name  # this gives you the name of the parent
end

What includes does is it pre-fetches the specified association. That is, ActiveRecord will query all subcomponents, and then in a single query also pull down all the parents of those subcomponents. When you subsequently access comp.parent in the loop, the associated parent will already be loaded, so this will not result in a so-called N+1 query.

The queries that AR will generate for you automatically will look something like this:

SELECT `subcomponents`.* FROM `subcomponents`
SELECT `subcomponents`.* FROM `subcomponents` WHERE `subcomponents`.`id` IN (1, 3, 9, 14)

If you need to use the name of the parent in a where condition, includes will not work and you will have to use joins instead to actually generate an SQL JOIN.

Upvotes: 1

Related Questions