CODEWITHSUNDEEP
ruby-on-railsactiverecordpolymorphic-associations
Thomas
Thomas

Reputation: 303

How do I query a database with polymorphic association?

Let's say, on a rails app (with postgresql), I have this database with a polymorphic association between work_steps and sub_assemblies / parts: enter image description here

Part.rb

belongs_to :sub_assembly, optional: true
has_many :work_steps, as: :component
belongs_to :site
belongs_to :car

Sub_assembly.rb

has_many :work_steps, as: :component
has_many :parts

work_step.rb

  belongs_to :component, polymorphic: true

Now, I want to query my database: I want a list of all the work_steps filtered with a specific array of sites and a specific array of cars. For instance, all the work_steps linked to parts that belongs to Site A and Car X & Z.

Because of the polymorphic association with the sub_assemblies and this same table which is linked to parts table, I cannot figure out how to do it.

At the end, I need an ActiveRecord Relation. How would you do it simply? I tried a lot of things but without success.

Any one to help me?

Thank you in advance.

Upvotes: 1

Views: 432

Answers (1)

TedTran2019
TedTran2019

Reputation: 1037

Okay, so work_step is what you want to be able to be used by multiple models.

So inside the migration for CreateWorkSteps: 

t.references :component, polymorphic: true

This basically does

t.integer :component_id # Refers to id (of sub-assembly or part)
t.string :component_type # Refers to type of object (sub-assembly or part)
add_index :work_steps, [:component_type, component_id]

Now in your model:

work_step.rb

belongs_to :component, polymorphic: true

sub_assembly.rb

has_many :work_steps, as: :component
has_many :parts

part.rb

has_many :work_steps, as: :component
belongs_to :site
belongs_to :car
belongs_to :sub_assembly, optional: true

Okay, so you wish to query your database and obtain

  • work_steps
  • linked to parts
  • belongs to site A and Car X and Z

So, here the list of steps you should do in ActiveRecord (I'm not going to give a single-query answer as that's too much effort. Also, the step order is in logical order, but remember that ActiveRecord queries use lazy evaluation and SQL queries are evaluated in a certain order.)

  1. #Join parts w/ site and cars
  2. Use #Where siteName is in [A], carName is in [X, Z]
  3. Now, you LEFT join with sub_assembly (So even if the part has no sub_assembly_id, it isn't removed).
  4. Now this is the annoying step. For your first join, you'll want to join parts_id with component_id AND component_type == "Part" (Remember to prefix your tables when joining, e.g work_steps.component_id and do this from the beginning.)
  5. For your second join, you'll want to join sub_assembly_id with component_id AND component_type == "Sub_assembly"
  6. Now you have a HUGE ActiveRecord object, select only what makes a work_step.

Good luck! I won't hold your hand with a pre-written answer, through...because I'm too lazy to boot into Rails, make migrations, make models, seed the models, then write the query myself. ;)

Oh ya, and I ended up giving a single query answer anyway.

Upvotes: 1

Related Questions