I have this database schema with tables such as project (tombstone), risks (project risks), issues (project issues), deliverables (project deliverables) etc… I must build a report that will show the project tombstone data as well as its risks, deliverables, issues… A kind of project snapshot report (all in one).
I am considering three approaches to retrieve data.
- one select statement in a controller
$sql =select …
- a db view then use activeRecord
create view as select …
In a controller a series of
project->model()->find(project_id = 1);
risk-.model()->find(project_id = 1);
deliverable-.model-.find(project_id = 1); …
which one do you prefer? Performance?
Project can have many risks, right?
It’s a bad idea to put sql statements in controller. If you need raw sql, better put it into model.
Usually I prefer #3 because of many reasons (minimizing data amount being transferred from DB to PDO, separate caches and so on)
So i should create a "big report" model even though there is not an actual table in the database?
My thinking was that if there is not a table in the db, then put all sql in the controller.
i am wrong?
Not necessarily. You can just add some method to Project model, for example getReport and then use it like $items = Project::model()->report. Or return DataProvider. Or something else.
My point is, sql in controllers is bad, it ruins MVC concept.
Actually you can have models without having corresponding db table