Multiple Joins to a single table

Rails
21

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’].

22

Dani D. 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:

class Deck < ActiveRecord::Base
has_many :cards
end

class Card < ActiveRecord::Base
belongs_to :deck # a card is in 1 deck
belongs_to :material # a card is made of 1 material
end

class Material < ActiveRecord::Base
has_many :cards
end

The problem is to find all decks that already contain at least 1 king
and at least 1 ace where both ace and king are made of cardboard,
without using excessive amounts of SQL.

I’m not new to rails so I’m pretty sure I’ve gotten to grips with
has_many and belongs_to. That said, I often make stupid mistakes, so
don’t be afraid to give me ideas.

Thanks,
ben

23

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

24

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

25

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

26

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

27

On Apr 29, 1:33 am, Ben W. [email protected] wrote:

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.

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.

28

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