# Write/Display AR query as Grouped Results? **URL:** \ **Category:** Rails\ **Created:** [April 9, 2006, 4:29pm UTC](https://www.ruby-forum.com/t/write-display-ar-query-as-grouped-results/57310 "2006-04-09T16:29:05Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [April 9, 2006, 4:29pm UTC](https://www.ruby-forum.com/t/write-display-ar-query-as-grouped-results/57310/1 "2006-04-09T16:29:05Z")
I’ve got a publications table that contains an author\_id foreign key and a pubrole\_id foreign key. What I want to do is query the DB using AR so that I can get a list of all publications that belong\_to a particular author, and group the results by the pubrole.role\_name (Author, Joint Author, Editor, etc.) so that the results look something like: Author book1 info book2 info etc. Joint Author book1 info book2 info Editor etc. Can anyone suggest how to do this, or point to an example that already exists? Thanks. ---
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [April 9, 2006, 7:50pm UTC](https://www.ruby-forum.com/t/write-display-ar-query-as-grouped-results/57310/2 "2006-04-09T19:50:44Z")
chris wrote: > I’ve got a publications table that contains an author\_id foreign key and > a pubrole\_id foreign key. What I want to do is query the DB using AR so > that I can get a list of all publications that belong\_to a particular > author, and group the results by the pubrole.role\_name (Author, Joint > Author, Editor, etc.) so that the results look something like: > > Author > book1 info > book2 info > etc. > > Joint Author > book1 info > book2 info > > Editor You can construct the sql clause with “find\_by\_sql”: @publications = Publication.find\_by\_sql [“select p.\*, r.role\_name from publications p, pubroles r where p.author\_id = ? and p.pubrole\_id = r.id group by r.role\_name, p.book\_name”, author\_id] …or something like that. 🙂 Jeff ---
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [April 9, 2006, 10:30pm UTC](https://www.ruby-forum.com/t/write-display-ar-query-as-grouped-results/57310/3 "2006-04-09T22:30:34Z")
chris wrote: > I’ve got a publications table that contains an author\_id foreign key and > a pubrole\_id foreign key. What I want to do is query the DB using AR so > that I can get a list of all publications that belong\_to a particular > author, and group the results by the pubrole.role\_name (Author, Joint > Author, Editor, etc.) so that the results look something like: > > Author > book1 info > book2 info > etc. > > Joint Author > book1 info > book2 info > > Editor > > etc. > > Can anyone suggest how to do this, or point to an example that already > exists? > > Thanks. Try using the new group\_by function. It works great. you should be able to do it using something like the following: CONTROLLER: @publications = Publication.find :all, :conditions =\> [‘author\_id = ?’, @author.id] VIEW: \<% @publications.group\_by{|p| p.pubrole.role\_name }.each do |role\_name, publications| %\> ## \<%= role\_name %\> ``` <% for pub in publications %> . . . <% end %> ``` \<% end %\> ---
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [April 10, 2006, 12:17am UTC](https://www.ruby-forum.com/t/write-display-ar-query-as-grouped-results/57310/4 "2006-04-10T00:17:30Z")
Steve Is that new for 1.1? Very cool. Any documentation on it? I looked at [api.rubyonrails.com](http://api.rubyonrails.com) but didnt see it listed there. ---
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [April 10, 2006, 1:55am UTC](https://www.ruby-forum.com/t/write-display-ar-query-as-grouped-results/57310/5 "2006-04-10T01:55:45Z")
Jean-François wrote: > Hi Chris, > > > Is that new for 1.1? Very cool. Any documentation on it? I looked at > > [api.rubyonrails.com](http://api.rubyonrails.com) but didnt see it listed there. > > See : > [http://weblog.rubyonrails.org/articles/2006/03/01/new-in-rails-enumerable-group\_by-and-array-in\_groups\_of](http://weblog.rubyonrails.org/articles/2006/03/01/new-in-rails-enumerable-group_by-and-array-in_groups_of) > > and r3726. > > ``` > -- Jean-François. > > ``` I noticed that too, but I am also curious about where is the actual documetation for it? What module contains groups\_by, and is there a proper RDoc for it? Jeff ---
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [April 10, 2006, 12:59am UTC](https://www.ruby-forum.com/t/write-display-ar-query-as-grouped-results/57310/6 "2006-04-10T00:59:44Z")
Hi Chris, > Is that new for 1.1? Very cool. Any documentation on it? I looked at > [api.rubyonrails.com](http://api.rubyonrails.com) but didnt see it listed there. See : [http://weblog.rubyonrails.org/articles/2006/03/01/new-in-rails-enumerable-group\_by-and-array-in\_groups\_of](http://weblog.rubyonrails.org/articles/2006/03/01/new-in-rails-enumerable-group_by-and-array-in_groups_of) and r3726. ``` -- Jean-François. ``` ---
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [April 10, 2006, 2:48am UTC](https://www.ruby-forum.com/t/write-display-ar-query-as-grouped-results/57310/7 "2006-04-10T02:48:54Z")
Hello Jeff, > I noticed that too, but I am also curious about where is the actual > documetation for it? You’re right, there isn’t 🙂 > What module contains groups\_by, and is there a > proper RDoc for it? It’s an add-on for the module Enumerable so it’s in ActiveSupport, in activesupport-xx/lib/active\_support/core\_ext/enumerable.rb and you will see in the first line of the file : module Enumerable #:nodoc: The news or the source are the documentation. ``` -- Jean-François. ```