Added cross tabulation method to ActiveRecord::Base

Let me try that again - and if this doesn't work then please just read the diff as this "comment" is straight from there :)

A cross tabulation compares one variable against the tabulation of another variable. This is often useful when you need to aggregate data in categories.

For example, you may want to find the sum of sales for each department in a supermarket. Given the schema:

Departments: id, name Sales: id, department_id, amount

and the example data:

Departments: +------------------+ | id | name | |----+-------------+ | 1 | Vegetables | | 2 | Deli | | 3 | Frozen | +------------------+

Sales: +-----------------------------+ | id | department_id | amount | |----+---------------+--------+ | 1 | 1 | 11 | | 1 | 1 | 111 | | 2 | 2 | 22 | | 2 | 2 | 222 | | 3 | 3 | 3 | +-----------------------------+

You would want the output: +----------------------------+ | Vegetables | Deli | Frozen | |------------+------+--------+ | 122 | 244 | 3 | +----------------------------+

Which could be achieved using the method call:

Department.cross('name', :aggregator => 'sum', aggregator_column => 'amount', :joins => 'INNER JOIN sales ON sales.department_id = departments.id')

I'm not entirely sure what you gain over doing

Department.sum :amount, :joins => :sales, :group => 'departments.name'

Sorry, maybe I should swap the example in the doc for the method. One of the test cases is probably a better example...

@@@ruby def test_cross_with_joins_and_group_and_aggregator

# "Show me the number of comments an author receives based on post type"
cross = Comment.cross('posts.type', :joins => 'INNER JOIN posts ON comments.post_id = posts.id', :group => 'posts.author_id', :aggregator_column => 'comments.id')
assert_equal(
  [{"Post"=>"6", "SpecialPost"=>"1", "StiPost"=>"2", "author_id"=>"1"},
   {"Post"=>"1", "SpecialPost"=>"0", "StiPost"=>"0", "author_id"=>"2"}],
  cross)

end

As you can see, we've grouped by author, but then further separated the post count by the type of post.

I hope this helps.

You might find that discussing this on the core list is the best way to achieve a consensus. Personally I don't think this belongs in core (although some may find it useful as a plugin) but that's just may opinion.

Yes, I'm still in two minds as to whether it should be included as core Rails.

I'll ping the list, but I think I'll built out a plugin anyway as there's a lot more you could do with a feature like this that probably excludes it from core activerecord functionality.

On second thoughts I've decided not to contact the core group. I think a plugin will be a better fit.

Automatic cleanup of spam.

gucci watches