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