Recreating this custom query using has_many

Question

I'm hoping this will be an easy one :) I've been stuffing around for hours playing with the has_many options trying to emulate this:

has_many :pages, :finder_sql => %q(SELECT * FROM `pages` LEFT OUTER JOIN `component_instances` ON `component_instances`.instance_id = `pages`.id AND `component_instances`.instance_type = 'Page' WHERE `component_instances`.parent_id = #{id})

It's basically a polymorphic join so there is the component_instances table that acts as a central structure and has different types of things hanging off of it. It's a nested set (not that that matters in this case).

The problem seems to be that has_many doesn't allow me to manipulate the join conditions. And I can't nullify the foreign key join condition that's automatically made.

The above code works but I want to use scopes on the results, and that's not possible with a custom query.

Any help would be greatly appreciated :)

Cheers,

Brendon

Answer 1

You can do this with the :through option.

has_many :pages, :through => :component_instances, :source => :parent, :source_type => 'Page'

Answer 2

Thanks for the lead Michael, in the end this worked:

  has_one :page_set, :foreign_key => :parent_id
  belongs_to :instance, :polymorphic => true, :dependent => :destroy
  has_many :pages, :through => :page_set, :source => :instance, :source_type => 'Page', :extend => LearningCaveFilterExtension

but it's a little bit sketchy as the :page_set method actually returns something completely wrong. Ideally it should return self but I needed to put :parent_id as the foreign key so that the SQL generated from the has_many pages declaration was correct (using :id would be the correct way but then that screws up the :pages method. :) My mind hasn't quite gotten around what's going on here, but at least it works and scoping works too :)

Thanks for the help, and if you have any explanations as to why that works, please let me know :)

Answer 3

Lol, sleeping on it gave me the answer:

class PageSet < ActiveRecord::Base

  unloadable

  set_table_name "component_instances"

  has_many :children, :foreign_key => :parent_id, :class_name => 'PageSet'
  belongs_to :instance, :polymorphic => true, :dependent => :destroy
  has_many :pages, :through => :children, :source => :instance, :source_type => 'Page'

end

The entities with the link through parent_id are rightly children, I was just referring to them in the wrong way, but AR didn't raise any errors :)