Multiple Joins to a single table

rubyonrails-talk
21

Dani Dani wrote:

Oh, does it mean deck is a table ? if so, I think the following association will help you further:

class Card < ActiveRecord::Base has_many :coolcards has_many :deck, :through => :manifests end

class CoolCards < ActiveRecord::Base belongs_to :card belongs_to :deck end

class Deck < ActiveRecord::Base has_many :coolcards has_many :card, :through => :coolcard end

now you check the match between the: whether (Deck.coolcard_id == Card.coolcard_id) and then whether (Deck.rank == 'Ace' or Deck.rank == 'King')

Unfortunately I don't quite understand what you are getting at here. Yes deck is a table. This is the full schema, if that helps:

22

Since you are going to join the same table twice, you might as well write the whole SQL manually. ActiveRecord doesnt allow you to do something like :joins => [:cards as 'c1', :cards as 'c2'].

23

Sharagoz wrote:

Since you are going to join the same table twice, you might as well write the whole SQL manually. ActiveRecord doesnt allow you to do something like :joins => [:cards as 'c1', :cards as 'c2'].

ok. That's not a very satisfying answer but it is good to be confident that I'm not missing something obvious.

Obviously I'm interested if anyone can improve on Sharagoz's comment..

Thanks, ben

24

If you just want to use activerecord without writing inner joins manually you can do @decks_with_aces = Deck.all(:joins => :cards, :conditions => "cards.rank = ''Ace") @decks_with_kings = Deck.all(:joins => :cards, :conditions => "cards.rank = ''King") @decks_with_aces_and_kings = @decks_with_aces & @decks_with_kings

That basically puts all decks with kings in an array, the same for aces, and then uses the arrays & method to find elements common in both. It's not perfect performance wise, but it works

25

Sharagoz wrote:

Since you are going to join the same table twice, you might as well write the whole SQL manually. ActiveRecord doesnt allow you to do something like :joins => [:cards as 'c1', :cards as 'c2'].

Except that it does, since :joins can take a string.

Best,

26

Marnen Laibow-Koser wrote:

Sharagoz wrote:

Since you are going to join the same table twice, you might as well write the whole SQL manually. ActiveRecord doesnt allow you to do something like :joins => [:cards as 'c1', :cards as 'c2'].

Except that it does, since :joins can take a string.

I'm unsure whether Sharagoz was referring to was writing a find_by_sql statement or merely the 'whole' of the :joins string. Unless there is something I'm missing, I don't think writing out a :joins SQL string is the solution here.

Thanks, ben

27

Sharagoz wrote:

If you just want to use activerecord without writing inner joins manually you can do @decks_with_aces = Deck.all(:joins => :cards, :conditions => "cards.rank = ''Ace") @decks_with_kings = Deck.all(:joins => :cards, :conditions => "cards.rank = ''King") @decks_with_aces_and_kings = @decks_with_aces & @decks_with_kings

That basically puts all decks with kings in an array, the same for aces, and then uses the arrays & method to find elements common in both. It's not perfect performance wise, but it works

Thanks, but as you suggest the performance cost is prohibitive for me. ben

28

Yes, I ment writing the :joins part of the SQL manually. I think that's the closest you're going to get. If activerecord had supported what you're looking for I believe someone would have alerted us to this by now.