Hi all,
First let me say congratulation to the developers of this great framework!
I’m a new yii user and I’m starting to know how it works. I’ve study both the official guide and the blog tutorial. I’ve also done some simple projects.
Now, in my current project I’ve reached a stop since I don’t know how to do something. I could do it manually in another way, but I would like to use the features available in this framework. I’ve searched a lot both on this forum and on google but no one seems to have this exact problem.
Let’s say I’ve a MySQL table with a lot of rows. I need to extract a summarize of this data and show it to user. Since this summary will be used also to do other calculations together with other data inside the database I’ve decided to create a view. A data sample of this view is the following:
View: v_totals
chid grid stid lbl total
...
1 1 2 'Dex' 11
1 1 2 'Dex' -2
1 1 3 'Anemp' 10
.....
I would like to use this view linked to another primary table using the foreign key column ‘chid’ so I try to do this in the model using the following relation:
class Ch extends CActiveRecord {
...
public function relations()
{
return array(
...
'v_totals' => array(self::HAS_MANY, 'VTotals', 'chid'),
);
}
...
}
But anytime I try to do:
$ch = Ch::model()->findByPk($id);
$totals = $ch->v_totals;
the framework give me an exception in a foreach and after a quick search I’ve found it is due to the view has not a primary key defined.
Trying to solve that problem I’ve done a lot of search and the simpler solution seems to be something like this:
class VTotals extend CActiveRecord {
...
public function primaryKey () {
return 'chid';
}
...
}
The problem is that I can’t use that solution because in my view there isn’t, by definition, a concept of “primary key” since there is not any column that can uniquely identify a row.
I’ve though a lot about this and I’m pretty sure that by db point of view create that view is the correct way to proceed (in other words I don’t to change the db structure, and it will be complex to explain here why).
I’ve found these solutions:
[list=1]
[*]MySQL side: Adding a column to the view that will contain the row number. In this way I would have a unique column that I could use as a primary key.
[*]PHP Side: Do the same of solution #1 but via PHP. I mean, modify the view model class to inject sql code to have a new column that will contain the row number and use this virtual column as the primary key.
[*]PHP Side: Modify the view model class to create a new virtual column that has an incremental value (using a class static variable as a counter) and use this virtual column as the primary key.
[/list]
The first solution is not available since MySql has a lot of limitation when you create a view (e.g. you can’t do subqueries, nor you can use variables [and I would need that to have the row number], …)
The second and the third solution are my best choice at the moment, but this means to work at low level in the framework, possibly working only extending CActiveRecord class and without altering the framework itself.
I’ve tried to understand how I could do this, tracing how the function is called but it was getting really tangled!
So, my questions are (if some of you would be so kind to help me):
[list=1]
[*]First, what do you think about the solution I suggested? Are they valid or are too complex and do they exist other simpler?
[*]Any idea about how to implement this without the needs of changing the framework at low level (extending class is a good solution for me)
[/list]
Any help will be really appreciated!
Thanks,
Erik