Class table inheritance in Yii framework using MySQL views

384 views Asked by At

I would like to implement class (multi) table inheritance in Yii but i found it very difficult, so i planned to use the approach of MySQL view.

Here is an example of my tables and classes:

CmsAd is a table that inherits all the fields included in CmsContent table:

CREATE TABLE `CmsAd` (
  `Id_CmsAd` varchar(32) NOT NULL,
  `Image` varchar(300) DEFAULT NULL,
  `Id_PrdClass` varchar(32) DEFAULT NULL,
  `Id_PrdCatalog` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`Id_CmsAd`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `CmsContent` (
  `Id_CmsContent` varchar(32) NOT NULL,
  `ModifiedBy` varchar(32) DEFAULT NULL,
  `ModifiedOn` datetime DEFAULT NULL,
  `CreatedBy` varchar(32) DEFAULT NULL,
  `CreatedOn` datetime DEFAULT NULL,
  `Status` varchar(99) DEFAULT NULL,
  `Subject` varchar(350) DEFAULT NULL,
  `Text` longtext,
  `KeyWord` varchar(300) DEFAULT NULL,
  `Code` varchar(340) DEFAULT NULL,
  PRIMARY KEY (`Id_CmsContent`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CmsAd_View is a MySQL view that contains the join of those two tables :

CREATE VIEW `CmsAd_View` AS SELECT `CmsAd`.*, `CmsContent`.* FROM `CmsAd` LEFT JOIN `CmsContent` ON `CmsContent`.`Id_CmsContent` = `CmsAd`.`Id_CmsAd`;

Here are the models of those tables :

class CmsAd extends CActiveRecord {

    public function tableName() {
        return 'CmsAd_View';
    }
}


class CmsContent extends CActiveRecord {

    public function tableName() {
        return 'CmsContent';
    }
}

Notice that the table name of CmsAd is the view CmsAd_View.

Now I would like implement the CRUD the CmsAd. It's ok with find() method because it retrieves from the view CmsAd_View.

My Problem is with insert() and update() methods where we have to insert and update both tables CmsAd and CmsContent.

Is there any one who tried to implement the view approach of table inheritance in Yii ?

0

There are 0 answers