Hi,
Is it possible to join multiple times to the same table with ActiveRecord?
For instance:
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