Grouping Activerecord query by a child attribute

Question

Is it possible to use an attribute of a child to group a query?

Post.find(:all, :include => [ :authors, :comments ], :group=>'authors.city') does not work.

However, I am able to use author.city as part of the conditions.

Answer 1

If that's what you're using, then the syntax is wrong for the :group argument, it should be:

Post.find(:all, :include => [ :author, :comments ], :group=>'authors.city')

Make sure your :author and :comments associations are correct. If 'authors' is the actual table name, then you'll need a 'has_one :author' association in you Post model, and an Author model.

Associations need to be correct, too:

 class Post < AR:Base
   belongs_to :author
   has_many :comments
 end

 class Author < AR:Base
   has_many :posts
 end

 class Comment < AR:Base
   belongs_to :post
 end

And the db schema:

 posts
   id
   author_id
 authors
   id
 comments
   id
   post_id

This will let the query run correctly, however, now I'm getting an error with the results... the :group clause doesn't seem to be applied when :include is used.

Answer 2

Have a look at the query that is generated in your log file - you can often paste the query into your favourite MySQL tool to get a more detailed error.

You might actually need to provide an aggregate function to get the database to group correctly (this happens in MySQL rather than a syntax error sometimes).

Answer 3

Should the author include be pluralized?

Post.find(:all, :include => [ :authors, :comments ], :group=>'authors.city')

Answer 4

The solution is to force the necessary join so that ActiveRecord can resolve "authors.city":

Post.find(:all, :include => [ :author, :comments ], :joins=>"INNER JOIN authors ON posts.author_id=authors.id", :group=>'authors.city')