MVC architecture decision - how to re-factor existing approach into controllers/views and database access

Consider the following scenario:

Page written in classic ASP or PHP, which is rendering a data report (series of HTML tables for simplicity). There is one main database query and then multiple sub queries as the page renders. The report is split into sub panels, which correlate to a sub query. Therefore:

1. Main database query.
2. Loop over result.
3. For each row, execute sub query and render report panel.

How would you architect a similar report using the MVC pattern?

This can be split into two parts:

1. Efficiency at database level of multiple queries, which are dependent on values from an outer query.
2. If all data was processed and prepared in the controller, would this also be deemed inefficient if looping within the controller and then again in a view to render. Could sub controllers be used or sub panels, which encapsulate smaller sections of logic i.e. loading da开发者_C百科ta and rendering report panel.

Appreciate this may look a bit vague but I'm interested in how you would approach the solution.

Using an MVC pattern, the view shouldn't be running any queries. Views should be just to display data that has been passed to it.

Based on what you've described, you could have a report model, that has functions to generate the reports.

The controller would call the functions on the model, and pass the data through to the view. In that case, the controller would loop through to create the subsequent reports, but the loop should be pretty basic, as the real logic has been put in the model.

If you want to use subcontrollers, you should look at this video:

http://www.asp.net/mvc/videos/aspnet-mvc-2-render-action

Using RenderAction can allow you to split report generation into smaller pieces. If you want to make user experience much better (because using RenderAction can make only your code look better), you should create action for generating every subreport and call it with ajax.

My final solution had all data retrieval in a repository and then various bits were aggregated in the controller ready for display in the view.