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,
- Category wise duration total for a user for a week.
- Category wise duration total for a user for each of the 7 days of the
week. - Total duration for a particular week for all categories of a user.
- Total duration for a particular day for all categories of a user.
In Following ways I could get desired data,
- 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