Hello, I''ve got two tables, users and books: create_table :users do |t| t.column :name, :string t.column :rich, :boolean t.column :book_id, :integer end create_table :books do |t| t.column :name, :string end If I have around half-million ''rich'' User''s that read a very small amount of books, what is the best way I can query the database for the list of the names of these books? I can perform User.find(:all, :conditions => [''rich is true''], :include [:books]), but I believe this will return a very large number of users, and then I''d have to loop through each one to get the book names. I''d appreciate any help! Thanks. -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Hope this resolves: User.find(:all, :conditions => [''rich is true''], :select => ''distinct book_name'', :include =>[:books]) On Dec 28, 3:30 pm, Rick Penn <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Hello, > > I''ve got two tables, users and books: > > create_table :users do |t| > t.column :name, :string > t.column :rich, :boolean > t.column :book_id, :integer > end > > create_table :books do |t| > t.column :name, :string > end > > If I have around half-million ''rich'' User''s that read a very small > amount of books, what is the best way I can query the database for the > list of the names of these books? > > I can perform User.find(:all, :conditions => [''rich is true''], :include > [:books]), but I believe this will return a very large number of users, > and then I''d have to loop through each one to get the book names. > > I''d appreciate any help! Thanks. > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Thanks for the reply Kiran. This query still retrieves all of the rich users in the table. The book names are already unique, so the SQL ''distinct'' keyword did not help. Maybe the query has to be done in Books instead? Kiran Soumya wrote:> Hope this resolves: > > User.find(:all, :conditions => [''rich is true''], :select => ''distinct > book_name'', :include =>[:books]) > > > > On Dec 28, 3:30 pm, Rick Penn <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
If you just need the names of books for rich users ... then you need to add user_id column to books table, then give your regular query for books table directly. On Dec 28, 5:52 pm, Rick Penn <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Thanks for the reply Kiran. > > This query still retrieves all of the rich users in the table. The book > names are already unique, so the SQL ''distinct'' keyword did not help. > > Maybe the query has to be done in Books instead? > > Kiran Soumya wrote: > > Hope this resolves: > > > User.find(:all, :conditions => [''rich is true''], :select => ''distinct > > book_name'', :include =>[:books]) > > > On Dec 28, 3:30 pm, Rick Penn <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
I forgot to mention that each user has only one book. Adding user_id column to the books table will dramatically increase it''s size. Sorry for the confusion. -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
This works: Book.find(:all, :select => ''distinct name'', :joins => ''join users on users.book_id = books.id where users.rich is true'') Can this query become prettier? -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---