Write/Display AR query as Grouped Results?


#1

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.


#2

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

Jeff


#3

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


#4

Steve

Is that new for 1.1? Very cool. Any documentation on it? I looked at
api.rubyonrails.com but didnt see it listed there.


#5

Jean-François wrote:

Hi Chris,

Is that new for 1.1? Very cool. Any documentation on it? I looked at
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

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


#6

Hi Chris,

Is that new for 1.1? Very cool. Any documentation on it? I looked at
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

and r3726.

-- Jean-François.

#7

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 :slight_smile:

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.