What Would Be More Efficient Between These 3 Options?

Yii 1.1 General Discussion
1

Hi,

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.

  1. one select statement in a controller

$sql =select …

  1. a db view then use activeRecord

create view as select …

ReportView->model()->findAll(…

or

  1. In a controller a series of

    project->model()->find(project_id = 1);

    risk-.model()->find(project_id = 1);

    deliverable-.model-.find(project_id = 1); …

    etc…

which one do you prefer? Performance?

2

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)

3

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?

4

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 :)