rails how to add in :include for a multiple index key association

Question

I have started off by following this guide here Rails - Multiple Index Key Association

I have tried to add in :include to speed up the association but im getitng this error:

ActiveRecord::StatementInvalid in ItemsController#show

SQLite3::SQLException: no such column: links.item_id: SELECT "links".* FROM "links" WHERE ("links".item_id = 19)

here's what i have:

item.rb

 class Item < ActiveRecord::Base
    has_many :links, :dependent => :destroy, :uniq => true
    belongs_to :category
    belongs_to :user

    is_sluggable :name

     def links
      Link.by_item(self)
     end

link.rb

class Link < ActiveRecord::Base
  belongs_to :item1, :class_name => 'Item', :foreign_key => :item1_id
  belongs_to :item2, :class_name => 'Item', :foreign_key => :item2_id
  belongs_to :user
  validates_presence_of :item1_id
  validates_presence_of :item2_id
  validates_uniqueness_of :item1_id, :scope => :item2_id, :message => "This combination already exists!"

  def self.by_item(item)
    where("item1_id = :item_id OR item2_id = :item_id", :item_id => item.id)
  end
end

items_controller.rb

  def show
    @item = Item.find_using_slug(params[:id], :include => [:category, :user, :links])

It works okay without :links inside :include. But otherwise I get the error.

From what I understand, the item_id is stored in the links table as item1_id or item2_id, which is why it cannot be found. Is there a workaround for this because I will be heavily referencing the links records. I am not so good with the SQL stuff.

Also unsure what's the best way to set up an Index

Willing to try out any advice. Thanks

randomguy · Answer 1 · 2011-08-01T22:16:02.860

0

The problem lies with the has_many :links association setup in Item. By default, this gets converted to a SQL query which looks for all links which have an item_id of the current Item. To override this default behavior, specify the find SQL yourself like this:

has_many :links, :dependent => :destroy, :uniq => true, :finder_sql => 'SELECT DISTINCT(*) FROM links WHERE item1_id = #{id} OR item2_id = #{id}'

edited Aug 01 '11 at 22:16

answered Aug 01 '11 at 14:13

randomguy

12,042
16
71
101

links table has a item1_id and item2_id because it is used for a link between two items from the item table – Julien Aug 01 '11 at 14:20
Ah, now I see it. You are trying to pair up (link) two items together and do it only once for each pair. I'm not sure if you can wire up associations like that. The has_many :links association goes to look for all links that have an item_id value of the current Item's primary key (id). You could at least do has_one :link_1 and has_one :link_2. – randomguy Aug 01 '11 at 14:48
Oh, I think you can define how the associations are fetched with has_many. You could define a custom SQL query in there. Let me check the docs. – randomguy Aug 01 '11 at 14:50
Eh, well obviously since Rails doesn't eagerly fetch associations by default. Try this in your console to understand: item = Item.first and then item.links. The first statement should work and the second one break. Add the above custom find SQL and it should work. – randomguy Aug 01 '11 at 15:52
actually both statements work. I tried adding in your modified SQL statement, it gives me NameError in ItemsController#show undefined local variable or method `id' for # – Julien Aug 01 '11 at 16:48
Ah, my bad.. it should be within single-quotes. I've corrected it. – randomguy Aug 01 '11 at 22:15
i been trying, but putting that line into item.rb doesn't seem to do any difference – Julien Aug 02 '11 at 16:13
yeah i think it is really impossible for me to use :includes straight with a custom finder sql in my other model. i think i might try a different route instead :( – Julien Aug 03 '11 at 16:17

rails how to add in :include for a multiple index key association

1 Answers1