Access multiple models via SQL query and display as CGridViewList

Yii 1.1 General Discussion
1

Currently, there are 6 tables in a database as follows:

  1. cc

  2. rre

  3. en

  4. ct

  5. sm

  6. rrs

The models for each table is created.

There is a SQL query that involve the 6 tables and will output a result called R.

May I know how to use CGridView to display R?

(or How can I put this SQL query into Yii and pass it to zii.widgets.grid.CGridView to display them?)

(It would be great if someone is kind enough to draft the code)

assuming that this piece of code I will put in a "view" page.

and

the SQL query is basically "Select" statements.

Please kindly advise and thanks in advance.

Just for your info, the SQL query is:

select cc.id, rre.status, en.status, ct.status, sm.status, rrs.status

from cc, rre, en, ct, sm, rrs

where cc.id = 1 AND rre.id = cc.id AND en.id = cc.id AND ct.id = cc.id AND sm.id = cc.id AND rrs.id = cc.id

2

Currently, the only method that I can think of is using setting relationship between tables inside the model file and set the changes to view file to display the other 5 tables "selected" columns.

I would like to know if there is work-around or methods to pass a SQL query into the yii:app()->db->createCommand() and then put into a variable M, then put M into the dataProvider of the zii.widgets.grid.CGridView?

Thanks in advance.

3

You can do all in a standard way, using relations.

This is the most spreaded and confortable way to display many models.

If you strictly need to use your custom sql, you can access the database with direct access by using CSqlDataProvider.

Anyway I strongly advice you to use models and relations, as yii is desined for it.

4

Thanks Zaccaria for your suggestion.

may I know if it is possible for you to help me draft out the code for such scenario? (I have done a research online and notice that many cases / scenario tackle at most 2 tables. I was wondering how can I tackle 5 tables in this scenario based on the sixth table. Thanks in advanced for the code draft.)

5

I know this is an old topic but I came across it looking for another answer.

Surely the more efficient way to handle this is within the database. ie: define a view and then create a model on that view (or a function in your current model).