Hi, I tried to make query similar to Pratik''s from his post: http://m.onkey.org/2007/11/1/find-users-with-at-least-n-items User.find :all, :joins => "INNER JOIN items ON items.user_id users.id", :select => "users.*, count(items.id) items_count", :group => "items.user_id HAVING items_count > 5" I use postgresql and it keeps giving me errors while trying to make such a query. I changed "count(items.id) items_count" to "count (items.id) as items_count" and the missing thing is users.id (it must be in group by). When I put users.id into group by it says that I must put users.login in group by. So I end up with query: User.find :all, :joins => "INNER JOIN items ON items.user_id users.id", :select => "users.*, count(items.id) items_count", :group => "users.id, users.login, users.email, (.... listing all user columns ....), items.user_id HAVING items_count > 5" Listing all users columns is not best option... maybe someone more familiar with SQL and postgresql could help me with this? --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
> User.find :all, :joins => "INNER JOIN items ON items.user_id > users.id", :select => "users.*, count(items.id) items_count", :group > => "items.user_id HAVING items_count > 5" >Try: User.find :all, :joins => "INNER JOIN items ON items.user_id = users.id", :select => "users.*, count(*) items_count", :group => "items.user_id HAVING items_count > 5" But it might fail, because I think PostgreSQL expects you to list all selected fields in the :group option. First try selecting the user.name only and add it to the :group option. -- 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 -~----------~----~----~----~------~----~------~--~---
On Feb 7, 12:42 pm, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > User.find :all, :joins => "INNER JOIN items ON items.user_id > > users.id", :select => "users.*, count(items.id) items_count", :group > > => "items.user_id HAVING items_count > 5" > > Try: > > User.find :all, > :joins => "INNER JOIN items ON items.user_id = users.id", > :select => "users.*, count(*) items_count", > :group => "items.user_id HAVING items_count > 5" > > But it might fail, because I think PostgreSQL expects you to list all > selected fields in the :group option. First try selecting the user.name > only and add it to the :group option.Yes... unfortunately count(items.id) is not a problem. I tried selecting only one field from users and of course it works. But most of the time I want to select more than one field. The "hackery" workaround is to get all the columns: columns = User.column_names.map { |n| "users.#{n}" }.join(",") and insert such string to :group. But... .it''s not prettiest option ;-) And it''s additional query....> -- > 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---