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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs