Multiple Joins to a single table

Rails
1

Hi,

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

For instance:

class Deck < ActiveRecord::Base
has_many :cards
end

class Card < ActiveRecord::Base
end

And then I run a query for decks that contain aces and kings? Excluding
find_by_sql queries, that is.

Thanks in advance,
ben

2

Yes,

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

class Card < ActiveRecord::Base
end

On Tue, Apr 27, 2010 at 15:50, Ben W. [email protected]
wrote:

Posted via http://www.ruby-forum.com/.

–

Thanks & Regards,
Dhruva S…

3

Dhruva S. 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.

class Deck < ActiveRecord::Base
has_many :cards
end

class Card < ActiveRecord::Base
belongs_to :deck

has a “name” attribute/column, which is king/ace/jack/etc.

end

Thanks,
ben

4

@decks_with_aces_and_kings = Deck.all(:joins => :cards, :conditions =>
“cards.rank = ‘Ace’ OR cards.rank = ‘King’”)

5

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.

On Tue, Apr 27, 2010 at 18:12, Ben W. [email protected]
wrote:

understand how this helps - what query would you run to get decks that

has a “name” attribute/column, which is king/ace/jack/etc.

“Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected][email protected]
.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

–

Thanks & Regards,
Dhruva S…

6

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

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

7

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’;

On Tue, Apr 27, 2010 at 18:40, Ben W. [email protected]
wrote:

select * from decks d
You received this message because you are subscribed to the Google G.
“Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected][email protected]
.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

–

Thanks & Regards,
Dhruva S…

8

Dhruva S. 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.

9

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.

On Tue, Apr 27, 2010 at 19:14, Ben W. [email protected]
wrote:

To unsubscribe from this group, send email to
[email protected][email protected]
.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

–

Thanks & Regards,
Dhruva S…

10

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

On Tue, Apr 27, 2010 at 19:14, Ben W. [email protected]
wrote:

To unsubscribe from this group, send email to
[email protected][email protected]
.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

–

Thanks & Regards,
Dhruva S…

11

Dhruva S. 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

12

now that

13

Hope it worked :).

On Wed, Apr 28, 2010 at 05:43, Ben W. [email protected]
wrote:

ben
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

–

Thanks & Regards,
Dhruva S…

14

Dhruva S. 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?

15

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

16

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

17

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)

Material < ActiveRecord::Base
has_many :cards
end

Then how can I query for decks that have aces and kings where they are
both made of cardboard. Since we have named each of the cards by name
(i.e. c1 and c2), I cannot refer further through the relations in the
database.

So the query would become:

Deck.all(
:joins =>
'inner join cards c1 on c1.deck_id=decks.id
inner join cards c2 on c2.deck_id=decks.id
inner join materials m on cards.material_id=c1.id
:conditions => ["c1.rank = ‘Ace’ AND c2.rank = ‘King’ and m.name =
‘cardboard’])

Because I can’t use rails-type associations to define the relations, it
can rapidly get out of hand. Right now I need to join across 4 relations
after the parallel join. Any ideas other than getting over it and
writing the SQL myself?

Thanks,
ben

18

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 ?

On Wed, Apr 28, 2010 at 12:18, Ben W. [email protected]
wrote:

ben
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

–

Thanks & Regards,
Dhruva S…

19

Ben W. 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

20

Dhruva S. 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

Deck.all.select{|d|
Card.ace.cardboard.count > 0 and Card.king.cardboard.count > 0
}

where ace and cardboard are simple named_scopes using joins and
conditions.

Thanks for the continued assistance,
ben