When I create a relational database in Yii, do I create the foreign key in the appropriate table using a DB management system or query, and then declare it in Yii? OR do I just make the tables and primary keys, and then declare the foreign key in Yii?
If you already have designed your database,including the declaration of foreign keys, primary keys, indexes, etc., Yii will automatically follow your design when you create the model for your tables. If your table A is related to table B, and you created a model for table A, Yii will respect that and create a relation based on your design. In that way, you don’t need to declare anything again in your codes if you simply want to follow your database structure.
Of course there are instances wherein you need to include the relation or foreign keys manually, like when you changed your mind and you want to relate table A to table C instead of table B. But to simply answer your question, just make the tables and declare the keys, then create a model, then verify the created model.
As someone already stated, make sure you’re using InnoDB database engine. In order to check which engine is set for your EER, in MySQL Workbench go to Model->Model Options and then check the Model: MySQL tab.
That should tell you what engine your EER is using. You can then uncheck the “Use Global Settings” option at the bottom of the window and set InnoDB for that model in case isn’t already set. Otherwise you can change your MySQL Workbench global options to use InnoDB in all your EER models (recommended) .
Yup. It is using InnoDB by default. It still does not see the relation. Not sure why but I may have to enter the relations manually. On the other hand, it works fine on a Windows machine but not sure why on Mac , Yii does not see the relation(s).!!!
Yes it is. Even in MySQL Workbench preferences Default Storage Engine: is set to InnoDB. Everything on the model and my schema works just fine. I had to copy the relation function from the Windows Machine and seems it is working over there !!
Let me explain the actual project then you get a better picture of what I have in hand
I have a model(table) called invoice. I have another model(table) called company. I need to be able to show/search/sort companies in invoice gridview and then later on add a dropdown list where normally there is a textbox to filter.
Also I have another model named paymentStatus with two fields and two rows
and we filled the table like this
I also need to filter the invoice gridview by Paymentstatus.
The general idea is to access different fields of different models and show/search/sort by each of them hopefully with a dropdown list.
Let me know if it is not clear then I will add more if you want to know more.
Then, in order to replace the textboxs with dropdownlists, you have to customize your grid columns, like this:
// controller - admin action - before render
$values = CHtml::listData(Model::model()->findAll(), 'id', 'name')
// make sure you send the $values along with your other variables to the view when calling $this->render()
// admin view
'id' => 'this-is-a-nice-grid',
'dataProvider' => $model->search(),
'filter' => $model,
'columns' => array(
'name' => 'related.attribute', //where "related" is the name of the relation in your model and attribute, well, the attribute <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/biggrin.gif' class='bbc_emoticon' alt=':D' />
'value' => '$data->relatedModel->attribute',
'filter' => CHtml::activeDropDownList($model, 'id_of_related_model', $values, array('empty' => 'Show All')),
'class' => 'CButtonColumn',
PS: I’m not using XAMPP on mac, just the apache that comes shipped with the macbook with some custom configuration :B
I have created two tables namely tbl_Test1 and tbl_Test2.
The tbl_Test1 has three fields:
test1id INT(11) PK for tbl_Test1
test2_id INT(11) FK for tbl_test2
name VARCHAR(45) a random field
AND tbl_Test2 has three fields:
test2id INT(11) PK for tbl_test2
fname VARCHAR(45) a random field
lname VARCHAR(45) a random field
The relation is set up hence in modela\Test1.php we have
public function relations()
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
Now the task is to show fields in tbl_Test2 in Test1 gridview with a dropdown list. That is a dropdown list that user can select fname another one which shows lname then Test1 model can filter the gridview based on what user has selected.
I will fill tbl_Test2 to have duplicate fnames and duplicate lname to test the relation.