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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 Woodcroft <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- Thanks & Regards, Dhruva Sagar. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Dhruva Sagar wrote:> Yes, > > class Deck < ActiveRecord::Base > has_many :cards > has_many :cool_cards, :through => :cards > endThanks 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 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
@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...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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/. > > -- > 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group athttp://groups.google.com/group/rubyonrails-talk?hl=en.-- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 Woodcroft <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- Thanks & Regards, Dhruva Sagar. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 Woodcroft <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- Thanks & Regards, Dhruva Sagar. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 Woodcroft <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> 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. > -- > 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- Thanks & Regards, Dhruva Sagar. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 Woodcroft <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> 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. > -- > 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- Thanks & Regards, Dhruva Sagar. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Hope it worked :). On Wed, Apr 28, 2010 at 05:43, Ben Woodcroft <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> 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 > -- > 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- Thanks & Regards, Dhruva Sagar. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
now that -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 thateh? -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 ? > > DaniThanks 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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 Woodcroft <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> 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 > -- > 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- Thanks & Regards, Dhruva Sagar. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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, > benOh, 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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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/. > > -- > 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group athttp://groups.google.com/group/rubyonrails-talk?hl=en.-- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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/. > > -- > 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group athttp://groups.google.com/group/rubyonrails-talk?hl=en.-- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 worksThanks, but as you suggest the performance cost is prohibitive for me. 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Apr 29, 1:33 am, Ben Woodcroft <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.