# Help: odd results from many-to-many self-referential relationship with attribute in join table **URL:** \ **Category:** Rails\ **Created:** [April 7, 2008, 6:35am UTC](https://www.ruby-forum.com/t/help-odd-results-from-many-to-many-self-referential-relationship-with-attribute-in-join-table/134037 "2008-04-07T06:35:39Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![MaddyTheGoose](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/49beb7/32.png) [@MaddyTheGoose](https://www.ruby-forum.com/u/MaddyTheGoose)\ **Post date:** [April 7, 2008, 6:35am UTC](https://www.ruby-forum.com/t/help-odd-results-from-many-to-many-self-referential-relationship-with-attribute-in-join-table/134037/1 "2008-04-07T06:35:39Z")
I’m having an issue with the data returning from a self-referential many-to-many relationship with an attribute in the join table that’s being used to limit the query. I’ll explain what I’m doing below which will then bring you to the problem concerning the records I’m getting back from the relationship. MIGRATIONS (the two tables involved in the problem) Note that in contained\_gear\_items there are three items related to gic[1] and list\_id 1 and two other items related to gic[1] but belong to list\_id 2 so I would think I wouldn’t see them (considering I have ‘contained\_gear\_items.list\_id" =\> 1’ in the conditions - see below). When looking at .contained\_items the list\_id = 1 constraint seems to have been missed. But if I go directly into the contained\_gear\_items join model I see only the three items, as I should. Any help would be much appreciated. so, data wise, contained\_gear\_item goes like this: id gear\_item\_id contained\_gear\_item\_id list\_id 1 4 9 2 2 4 10 2 5 4 5 1 6 4 6 1 7 4 7 1 #011\_create\_contained\_gear\_items.rb (migration) class CreateContainedGearItems \< ActiveRecord::Migration def self.up create\_table :contained\_gear\_items do |t| t.integer “gear\_item\_id”, :null =\> false t.integer “contained\_gear\_item\_id”, :null =\> false t.integer “list\_id”, :null =\> false t.integer “position” t.timestamps end end ## 001\_create\_gear\_items.rb (migration) class CreateGearItems \< ActiveRecord::Migration def self.up create\_table :gear\_items do |t| t.string “title”, :limit =\> 100, :null =\> false t.boolean “is\_container”, :default =\> false t.timestamps end end MODELS ## gear\_item.rb (model) class GearItem \< ActiveRecord::Base has\_many :contained\_gear\_items has\_many :contained\_items, :through =\> :contained\_gear\_items ## contained\_gear\_item.rb (model) class ContainedGearItem \< ActiveRecord::Base belongs\_to :gear\_item belongs\_to :contained\_item, :class\_name =\> ‘GearItem’, :foreign\_key =\> ‘contained\_gear\_item\_id’ IN THE SCRIPT/CONSOLE > > gic = GearItem.find( > > :all, > > :include =\> “contained\_gear\_items”, > > :conditions =\> {“contained\_gear\_items.list\_id” =\> 1, :is\_container > > =\> true}, > > :order =\> “contained\_gear\_items.position”) > > puts gic[1].contained\_items > > #GearItem:0x3efeb80 > > #GearItem:0x3efeb30 > > #GearItem:0x3efeae0 > > #GearItem:0x3efea90 > > #GearItem:0x3efea40 > > puts gic[1].contained\_gear\_items > > #ContainedGearItem:0x3f2c56c > > #ContainedGearItem:0x3f29a4c > > #ContainedGearItem:0x3f27328 Thanks for any help you can provide… Cheers ---
**Author:** ![MaddyTheGoose](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/49beb7/32.png) [@MaddyTheGoose](https://www.ruby-forum.com/u/MaddyTheGoose)\ **Post date:** [April 7, 2008, 3:02pm UTC](https://www.ruby-forum.com/t/help-odd-results-from-many-to-many-self-referential-relationship-with-attribute-in-join-table/134037/2 "2008-04-07T15:02:59Z")
On 7 Apr 2008, at 05:35, MaddyTheGoose wrote: > I’m having an issue with the data returning from a self-referential > many-to-many relationship with an attribute in the join table that’s > being used to limit the query. I’ll explain what I’m doing below which > will then bring you to the problem concerning the records I’m getting > back from the relationship. Your problem is only with the eager loading: when you load the has many through it does not use the already loaded contained\_items to get the association, and so the conditions you specified at that point do not apply. You probably want to specify the conditions on the association itself, although I’m not entirely sure what you’re trying to do. Fred ---
**Author:** ![MaddyTheGoose](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/49beb7/32.png) [@MaddyTheGoose](https://www.ruby-forum.com/u/MaddyTheGoose)\ **Post date:** [April 7, 2008, 6:00pm UTC](https://www.ruby-forum.com/t/help-odd-results-from-many-to-many-self-referential-relationship-with-attribute-in-join-table/134037/3 "2008-04-07T18:00:45Z")
On 7 Apr 2008, at 16:46, MaddyTheGoose wrote: > =\> :contained\_gear\_items, :conditions =\> “contained\_gear\_items.list\_id > = 1” > > which works as I wanted but shouldn’t the associations only deal with > the relationship but not so much with conditions (leaving the > conditions to the find)? Not necessarily, it can definitely be used to good effect (eg customer has\_many :orders and customer has\_many :outstanding\_orders, :conditions =\> ‘…’) > I guess I’m looking for some best-practices > here. I want to be able to dynamically change the list\_id. I may be > looking at this wrong and perhaps have to change the way I think if > the above is the right way to do it. > > Is there a way to move the condition above into the find? > Essentially what I’m trying to achieve is: > A GearItem can contained 0…n GearItems and the GearItem-\>GearItem > relationship belongs to a list. So list 1 can have GearItem 1 > containing GearItem 2 & 3 but list 2 can have GearItem 2 containing > GearItem 1 & 4, etc… In this case having all the conditions in the association probably isn’t the right thing to do since you’d have to have one association per list and you’d have to figure out what to call them You could do this via an association proxy, eg has\_many :contained\_items, :through =\> :contained\_gear\_items do def from\_list(n) find :all, :conditions =\> [“contained\_gear\_items.list\_id = ?”, n] end end Then you can do stuff like foo.contained\_items.from\_list(23) Fred ---
**Author:** ![MaddyTheGoose](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/49beb7/32.png) [@MaddyTheGoose](https://www.ruby-forum.com/u/MaddyTheGoose)\ **Post date:** [April 7, 2008, 5:47pm UTC](https://www.ruby-forum.com/t/help-odd-results-from-many-to-many-self-referential-relationship-with-attribute-in-join-table/134037/4 "2008-04-07T17:47:16Z")
First off, thanks for the help. I did try adding the condition to the association itself but it didn’t seem like the right place for it although I could be wrong (I am pretty new to rails). I had it like this: class GearItem \< ActiveRecord::Base has\_many :contained\_gear\_items has\_many :contained\_items, :through =\> :contained\_gear\_items, :conditions =\> “contained\_gear\_items.list\_id = 1” which works as I wanted but shouldn’t the associations only deal with the relationship but not so much with conditions (leaving the conditions to the find)? I guess I’m looking for some best-practices here. I want to be able to dynamically change the list\_id. I may be looking at this wrong and perhaps have to change the way I think if the above is the right way to do it. Is there a way to move the condition above into the find? Essentially what I’m trying to achieve is: A GearItem can contained 0…n GearItems and the GearItem-\>GearItem relationship belongs to a list. So list 1 can have GearItem 1 containing GearItem 2 & 3 but list 2 can have GearItem 2 containing GearItem 1 & 4, etc… On Apr 7, 9:02 am, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![MaddyTheGoose](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/49beb7/32.png) [@MaddyTheGoose](https://www.ruby-forum.com/u/MaddyTheGoose)\ **Post date:** [April 7, 2008, 6:16pm UTC](https://www.ruby-forum.com/t/help-odd-results-from-many-to-many-self-referential-relationship-with-attribute-in-join-table/134037/5 "2008-04-07T18:16:06Z")
Love it. Just implemented it and it looks good so far… Thanks again for the help, much appreciated. On Apr 7, 11:59 am, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)