Database model of my web app is as follows,
I want to collect and show following data from the above tables, in the
- 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
- 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
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
I can write
select categories.name, sum(durationInHrs)
where categories.id = tasks.category_id and tasksdata.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
write some logic in controller to aggregate data
from the result set of one single query.
3. Write some stored procedures at database level which will return
What I experienced is that with approach 1 overall code gets simpler,
the number of SQL queries increases drastically and which may later on
some performance related issues as for every request I would need to run
In second approach I have to write a lot of code inside my controller
extarct the data from one query and perform aggregations and
to get what I want.
Stored procedures… DHH has few words of wisdom,
What do you think, which appraoch is better and why?