Migration - 2 Models for same table

I have an application with 3 tables:

- appgroups

  - id

  - name


- apptestgroups

  - id

  - name

  - id_appgroup (foreign key to appgroups on field id)


- applications

  - id

  - name

  - id_apptestgroup (foreign key to apptestgroups on field id)

This means that an application belongs to one apptestgroup and an apptestgroup belongs to an appgroup

I want change the relations/foreign keys so that one application belongs to a appgroup. To do this i need to create a migration to do the following changes:

[list=1]

[*]Change the id_apptestgroup to id_appgroup in table {{applications}}

[*]Update the id_appgroup in table {{applications}} with the values in the id_appgroup in table {{apptestgroups}}

[/list]

I managed to find a way to do the first point.

To do this i need to removed the foreignkey first and then recreate it at the end. No problem

The problem is the second point.

I believe i need to

• Create a model for the table before the id_apptestgroup change to id_appgroup

• Do a findAll with that model

• Update every new field to the id_appgroup from the previous relation

This is the code i’m using:

$applications_before = m110718_051216_change_application_parent_to_appgroup_ApplicationsTable::model()->with('idApptestgroup','idApptestgroup.idAppgroup','idApptestgroup.idAppgroup.idClient')->findAll();

$this->dropForeignKey("FK_apptestgroup", "{{applications}}");

$this->renameColumn("{{applications}}", "id_apptestgroup","id_appgroup");

echo "    > found " . count($applications_before) . " applications\n";

foreach ($applications_before as $app) {

	echo "    > processing application " . $app->name . "\n";

	if($app->idApptestgroup !== null) {

		if($app->idApptestgroup->idAppgroup !== null ) {

			$model = Applications::model()->findByPk($app->id);

			echo "    > saving appgroup id " . $app->idApptestgroup->id_appgroup . "\n";

			$model->id_appgroup=$app->idApptestgroup->id_appgroup;

			$model->save();

		}

	}

}

$this->addForeignKey("FK_appgroup", "{{applications}}", "id_appgroup", "{{appgroups}}", "id","NO ACTION","NO ACTION");

The problem is when i do the assignment it complains the id_appgroup attribute does not exist

Doing a var_dump i noticed that the $model variable is assigned the old attributes and relations

I believe this happens because both models m110718_051216_change_application_parent_to_appgroup_ApplicationsTable and Applications use the same table.

I hope i was clear explaining my problem.

Please ask me if you some doubts.

Doing an sql trace at the mysql this is the sequence of sql queries:

		    7 Query	SET NAMES 'utf8'

		    7 Query	SHOW COLUMNS FROM `tbl_migration`

		    7 Query	SHOW CREATE TABLE `tbl_migration`

		    7 Query	SELECT `version`, `apply_time`

FROM `tbl_migration`

ORDER BY `version` DESC

		    7 Query	SHOW COLUMNS FROM `tbl_applications`

		    7 Query	SHOW CREATE TABLE `tbl_applications`

		    7 Query	SHOW COLUMNS FROM `tbl_apptestgroups`

		    7 Query	SHOW CREATE TABLE `tbl_apptestgroups`

		    7 Query	SHOW COLUMNS FROM `tbl_appgroups`

		    7 Query	SHOW CREATE TABLE `tbl_appgroups`

		    7 Query	SHOW COLUMNS FROM `tbl_clients`

		    7 Query	SHOW CREATE TABLE `tbl_clients`

		    7 Query	SELECT `t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`, `t`.`id_apptestgroup` AS `t0_c2`, `t`.`id_rto` AS `t0_c3`, `t`.`id_rpo` AS `t0_c4`, `t`.`priority` AS `t0_c5`, `t`.`id_user` AS `t0_c6`, `t`.`timestamp` AS `t0_c7`, `idApptestgroup`.`id` AS `t1_c0`, `idApptestgroup`.`id_appgroup` AS `t1_c1`, `idApptestgroup`.`name` AS `t1_c2`, `idApptestgroup`.`id_user` AS `t1_c3`, `idApptestgroup`.`timestamp` AS `t1_c4`, `idAppgroup`.`id` AS `t2_c0`, `idAppgroup`.`id_client` AS `t2_c1`, `idAppgroup`.`name` AS `t2_c2`, `idAppgroup`.`id_user` AS `t2_c3`, `idAppgroup`.`timestamp` AS `t2_c4`, `idClient`.`id` AS `t3_c0`, `idClient`.`name` AS `t3_c1`, `idClient`.`id_user` AS `t3_c2`, `idClient`.`timestamp` AS `t3_c3` FROM `tbl_applications` `t`  LEFT OUTER JOIN `tbl_apptestgroups` `idApptestgroup` ON (`t`.`id_apptestgroup`=`idApptestgroup`.`id`)  LEFT OUTER JOIN `tbl_appgroups` `idAppgroup` ON (`idApptestgroup`.`id_appgroup`=`idAppgroup`.`id`)  LEFT OUTER JOIN `tbl_clients` `idClient` ON (`idAppgroup`.`id_client`=`idClient`.`id`)

		    7 Query	ALTER TABLE `tbl_applications` DROP FOREIGN KEY `FK_apptestgroup`

110718 11:30:31	    7 Query	SHOW CREATE TABLE `tbl_applications`

		    7 Query	ALTER TABLE `tbl_applications` CHANGE `id_apptestgroup` `id_appgroup` int(11) NOT NULL

		    7 Query	SELECT * FROM `tbl_applications` `t` WHERE `t`.`id`=6 LIMIT 1

		    7 Quit	

I believe the problem is that between

ALTER TABLE `tbl_applications` (...)

and

SELECT * FROM `tbl_applications` (...)

the commands

SHOW COLUMNS FROM `tbl_applications`

SHOW CREATE TABLE `tbl_applications`

Should be ran again

Does anyone know how to force the execution of this sql queries ?

After googling around i found the solution at the following links:

Issue 474: Forcing AR metadata to be refreshed

Automatic creation of tables/columns that dont exist using ActiveRecord

How to refresh table metadata

(...)

if($app->idApptestgroup->idAppgroup !== null ) {

	Applications::model()->getDbConnection()->getSchema()->refresh();

	Applications::model()->refreshMetaData();

	$model = Applications::model()->findByPk($app->id);

(...)