[Rails] Multiple Joins to a single table

[Ben Woodcroft]

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
--
Posted via http://www.ruby-forum.com/.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonra...@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-ta...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

[Dhruva Sagar]

Yes,

class Deck < ActiveRecord::Base
 has_many :cards

 has_many :cool_cards, :through => :cards

end

class Card < ActiveRecord::Base
end

--

Thanks & Regards,
Dhruva Sagar.

[Ben Woodcroft]

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

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,

[Sharagoz]

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

On Apr 27, 2:42 pm, Ben Woodcroft <li...@ruby-forum.com> wrote:
> 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.
> ------------------------------------
> 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
> --

> Posted viahttp://www.ruby-forum.com/.

[Dhruva Sagar]

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.

--

Thanks & Regards,
Dhruva Sagar.

[Ben Woodcroft]

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';
----------------------------
--
Posted via http://www.ruby-forum.com/.

[Dhruva Sagar]

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

--

Thanks & Regards,
Dhruva Sagar.

[Ben Woodcroft]

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.

[Dhruva Sagar]

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.

--

Thanks & Regards,
Dhruva Sagar.

[Dhruva Sagar]

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

On Tue, Apr 27, 2010 at 19:14, Ben Woodcroft <li...@ruby-forum.com> wrote:

--

Thanks & Regards,
Dhruva Sagar.

[Ben Woodcroft]

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

[Dhruva Sagar]

Hope it worked :).

--

Thanks & Regards,
Dhruva Sagar.

[Bob Miller]

now that

[Ben Woodcroft]

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?

[Ben Woodcroft]

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

[Dani Dani]

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

[Ben Woodcroft]

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

[Dhruva Sagar]

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 ?

--

Thanks & Regards,
Dhruva Sagar.

[Dani Dani]

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

[Ben Woodcroft]

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

-------
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 Woodcroft]

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

[Sharagoz]

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

On Apr 27, 3:10 pm, Ben Woodcroft <li...@ruby-forum.com> wrote:
> 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';
> ----------------------------
> --

> Posted viahttp://www.ruby-forum.com/.

[Ben Woodcroft]

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
--
Posted via http://www.ruby-forum.com/.

[Sharagoz]

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

On Apr 28, 10:02 am, Ben Woodcroft <li...@ruby-forum.com> 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'].
>
> 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
> --

> Posted viahttp://www.ruby-forum.com/.

[Marnen Laibow-Koser]

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
mar...@marnen.org
--
Posted via http://www.ruby-forum.com/.

[Ben Woodcroft]

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

[Ben Woodcroft]

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

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

[Sharagoz]

On Apr 29, 1:33 am, Ben Woodcroft <li...@ruby-forum.com> 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.