Generating report with MySQL and Rails - how?

Question

Here is my data model from my application:

id :integer(4) not null, primary key spam :boolean(1) not null duplicate :boolean(1) not null ignore :boolean(1) not null brand_id :integer(4) not null attitude :string not null posted_at :datetime not null

Attitude could have 3 states: negative, positive, neutral.

I want to generate resultset in table, this way, for each day between start and end date:

date       | total | positive | neutral | negative
2009-10-10 |   12  |     4    |    7    |     1
(...)
2009-10-30 |   5   |     2    |    1    |     1

And ignore all records which have:

duplicate = true ignore = true spam = true

How it's could be done?

Answer 1

Data.all  :select     => "DATE(posted_at) AS date,
                          COUNT(*) AS total,
                          SUM(attitude = 'positive')  AS positive,
                          SUM(attitude = 'neutral')   AS neutral,
                          SUM(attitude = 'negative')  AS negative",
          :conditions => [  "duplicate <> ? AND ignore <> ? AND spam <> ?",
                            true, true, true ],
          :group      => :date,
          :order      => :date

That should give you the data you want; I leave it as an exercise for the reader to render it into a table. (SQL gurus: please feel free to check my work.)

Answer 2

Have a look at Jasper Reports. I use it for all my applications. Its easy to use and setup. Its also easier to manager sub reports etc without polluting your application code base.