Rails - Aug 2006 - Sum/Aggregate at database level or in rails controller?

Hi,

Database model of my web app is as follows,

users(id,name)
Taskcategories(id,user_id,name)
Tasks(id,taskcategory_id,name)
TasksData(id,tasks_id,durationInHrs,date)


I want to collect and show following data from the above tables, in the
view,
1. Category wise duration total for a user for a week.
2. Category wise duration total for a user for each of the 7 days of the
week.
3. Total duration for a particular week for all categories of a user.
4. Total duration for a particular day for all categories of a user.

In Following ways I could get desired data,
1. Fire multiple SQL queries from controller which return me the data which
I can directly use in the view without need to write any logic in
controller, for e.g. for category wise duration total of a user for a week,
I can write
   select categories.name, sum(durationInHrs)
   from categories,tasks,tasksdata
   where categories.id = tasks.category_id and tasksdata.tasks_id = tasks.id
   group by categories.name

  similarly I will have write queries for getting category wise data for
every day of the week. and many more such queries.
2. Fire a single query which gets all the data for a specific user and then
write some logic in controller to aggregate data daily/weekly/categorywise
from the result set of one single query.
3. Write some stored procedures at database level which will return
aggragted data.


What I experienced is that with approach 1 overall code gets simpler, but
the number of SQL queries increases drastically and which may later on pose
some performance related issues as for every request I would need to run the
above queries.
In second approach I have to write a lot of code inside my controller the
extarct the data from one query and perform aggregations and transformation
to get what I want.
Stored procedures.. DHH has few words of wisdom,
http://www.loudthinking.com/arc/000516.html

What do you think, which appraoch is better and why?


Regards,
Jatinder
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060809/da57a392/attachment.html