Multiple Joins to a single table

Hi,

Is it possible to join multiple times to the same table with
ActiveRecord?

For instance:

Yes,

class Deck < ActiveRecord::Base
has_many :cards

has_many :cool_cards, :through => :cards

end

class Card < ActiveRecord::Base
end

Dhruva Sagar wrote:

Yes,

class Deck < ActiveRecord::Base
has_many :cards
has_many :cool_cards, :through => :cards
end

Thanks for the help Dhruva, but doesn't that mean that there is a
cool_cards foreign key in the cards cards table? I don't quite
understand how this helps - what query would you run to get decks that
have aces and kings?

I should also add some details to the schema, in case I wasn't clear.

@decks_with_aces_and_kings = Deck.all(:joins => :cards, :conditions =>
"cards.rank = 'Ace' OR cards.rank = 'King'")

Hi,

I think I misunderstood your email subject that you wanted to do multiple joins from activerecord with a single table, so I quickly pasted the code to do so :).

Sharagoz’s answer looks right.

Sharagoz wrote:

@decks_with_aces_and_kings = Deck.all(:joins => :cards, :conditions =>
"cards.rank = 'Ace' OR cards.rank = 'King'")

Looks good, except it seems my question wasn't clear (even to me
re-reading it). I want decks that have kings and aces, so an OR isn't
what I'm looking for. Any ideas then?

SQL would be something like

Hi,

Can’t your sql be :

select * from decks d
inner join cards c1 on c1.deck_id=d.id

where
c1.rank = ‘Ace’ and
c1.rank = ‘King’;

Dhruva Sagar wrote:

Hi,

Can't your sql be :

select * from decks d
inner join cards c1 on c1.deck_id=d.id
where
c1.rank = 'Ace' and
c1.rank = 'King';

Unfortunately I don't think so. c1 cannot be both an ace and a king at
the same time. I just tested your code on a rails setup with a has_many
and belongs_to and it didn't work.

you should then try :

Deck.all(:joins => ‘inner join cards c1 on c1.deck_id=d.id inner join cards c2 on c2.deck_id=d.id’, :conditions => ["c1.rank = ‘Ace’ AND c2.rank = ‘King’])

Forget the many, through thing that was a misunderstanding.

Sorry it should be :

Deck.all(:joins => ‘inner join cards c1 on c1.deck_id=decks.id inner join cards c2 on c2.deck_id=decks.id’, :conditions => ["c1.rank = ‘Ace’ AND c2.rank = ‘King’])

Dhruva Sagar wrote:

Sorry it should be :

Deck.all(:joins => 'inner join cards c1 on c1.deck_id=decks.id
<http://d.id> inner
join cards c2 on c2.deck_id=decks.id <http://d.id>', :conditions =>
["c1.rank = 'Ace' AND c2.rank = 'King'])

Thanks for persevering - that's what I was after. I didn't realise the
:joins option could take a string.
ben

Hope it worked :).

now that

Dhruva Sagar wrote:

Hope it worked :).

Yes, it did, and now I have named scopes working nicely, like
named_scope :with_card(rank) in the Deck class, and I can even chain
them together.

Bob Miller wrote:

now that

eh?

So one problem I've run into is that I cannot join beyond the table that
has been joined twice, without excessive amounts of SQL. For instance,
if there is a materials table (cards are made out of some material)

Question. wouldn't the following do the work ?

Card.find(:all, :conditions => "rank='Ace' or rank='King'" ).each do

card>

decks << card

or am I missing here something ?

Dani

Dani Dani wrote:

Question. wouldn't the following do the work ?

Card.find(:all, :conditions => "rank='Ace' or rank='King'" ).each do
>card>
decks << card

or am I missing here something ?

Dani

Thanks for the response - appreciated.

Well, I'm looking for a single query to be sent to the database. But
apart from that, as far as I see your solution adds the king and ace
cards to the decks, actually modifying the database, which I don't want.

I simply want to query for all decks that already contain at least 1
king and at least 1 ace, without using excessive amounts of SQL.

Thanks,
ben

Hi Ben,

m_id = Materials.first(:conditions => “name == ‘cardboard’”)

Deck.all(:joins => ‘inner join cards on cards.deck_id = cards.id’, :conditions => ["(cards.name = ‘Ace’ OR cards.name = ‘King’) AND cards.material_id=?", m_id])

How about that ?

Ben Woodcroft wrote:

Well, I'm looking for a single query to be sent to the database. But
apart from that, as far as I see your solution adds the king and ace
cards to the decks, actually modifying the database, which I don't want.

I simply want to query for all decks that already contain at least 1
king and at least 1 ace, without using excessive amounts of SQL.

Thanks,
ben

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')

???.
Dani

Dhruva Sagar wrote:

Hi Ben,

m_id = Materials.first(:conditions => "name == 'cardboard'")

Deck.all(:joins => 'inner join cards on cards.deck_id = cards.id',
:conditions => ["(cards.name = 'Ace' OR cards.name = 'King') AND
cards.material_id=?", m_id])

How about that ?

I don't think that would work since it would get all decks that have
kings OR aces, where I want both to be there.

But apart from that in my real-world example there is actually 30,000 or
so different materials that are acceptable, so loading all of those out
of the database into ruby-land would take longer than iterating through
each deck individually using