Managing Mysql Fk Constrained Delete Fail Situation In Yii

Yii 1.1 General Discussion
1

Hi all,

Context: I’m an experienced (mature) IT ex-management professional now attempting to re-invent myself by learning & upgrading my technical skills by developing a proof of concept business application with Yii

I’m trying to manage MySQL FK constrained delete situation in Yii (CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row:…)

I’m trying to solve this by using a Rules method triggered by a ‘delete’ scenario, which is probably not the best or most elegant but I’m at the stage where I just want something that works!..

I’m maintaining a count of child records(r007_patient_ct) in the parent (r007_project_centre) and in the r007ProjectCentreModel have the following in its Rules:

…array(‘r007_patient_ct’,‘compare’,‘compareValue’=>‘0’,‘operator’=>’!=’,‘on’=>‘delete’,‘message’=>‘Unable to delete Project Centre - it has Patients’)

In the R007ProjectCentreConroller, I’ve tried to set the ‘delete’ scenario but in the ActionDelete function but despite all sorts of attempts with various syntax versions, have failed

public function actionDelete($id) {

	if (Yii::app()->getRequest()->getIsPostRequest()) {


	    //how do I set up a delete scenario to be triggered in the model e.g.I thought   ??$this>setScenario('delete');?


		$this->loadModel($id, 'R007ProjectCentre')->delete();


		if (!Yii::app()->getRequest()->getIsAjaxRequest())


			$this->redirect(array('admin'));


	} else


		throw new CHttpException(400, Yii::t('app', 'Your request is invalid.'));


}

Sample error: ‘R007ProjectCentreController and its behaviors do not have a method or closure named “delete”.’

The answer is probably simple & obvious but escapes me, so I’m asking for another brain & pair of eyes on it.

Also, I’m not wedded to this solution! - I’d happily change to a simpler, better way as I’ll be needing this type of functionality elsewhere in the app…

Help! ideas / solutions most welcome

Brgds B

2

Hi

I share some code where I actually delete the related records. I use a transaction, and the code below only starts a transaction if there is not already one ongoing.

The code could be improved by testing ‘$result’ regularly, but in the normal case ‘$result’ is true.

If you prefer to not delete the record if there are related records (as seems to be your case), I would suggest making your test in the delete() method an throw a specific exception there which you catch in your action.

You could also create an alternate method that you’ld call ‘safeDelete()’ which does that.


    /**

     * Delete this record and uniquely related records.

     *

     * (non-PHPdoc)

     * @see CActiveRecord::delete()

     */

    public function delete() {

        $result=true;

        $transaction=null;

        if($this->getDbConnection()->currentTransaction===null) {

            $transaction=$this->getDbConnection()->beginTransaction();

        }


    // First we delete all records that refer to this record.


        // Example where items of a ONE_MANY relation are deleted.

        foreach($this->entityPropertyLinks as /* @var $entityPropertyLink EntityPropertyLink */ $entityPropertyLink) {

            $result&=$entityPropertyLink->delete();

        }

    

        $result&=Alarm::model()->resetScope(false)->deleteAllByAttributes(array('entity_id'=>$this->entity_id));




        foreach($this->devices as /* @var $device Devices */ $device) {

            $result&=$device->deleteRelated();

        }

        // Exemple where related records are deleted by attributes

        $result&=AlertHistory::model()->resetScope(false)->deleteAllByAttributes(array('entity_id'=>$this->entity_id));




        $planning_id=$this->planning_id; // Store id of planning that we refer to.




        // Delete the current record (all records referring to this record have been deleted)

        $result&=parent::delete();




    // Next lines deletes entries that this record depended on.




        // Delete related plannings

        if(!empty($planning_id)) {

            $entities=Entity::model()->resetScope()->findAllByAttributes(array('planning_id'=>$planning_id));

            if (count($entities)==0) {  // No other entities depend on the planning

                $result&=Planning::model()->resetScope()->findByPk($planning_id)->delete();

            }

        }


        if($transaction!==null) {

            // Commit or rollback the transaction depending on success.

            if($result) {

                $transaction->commit();

            } else {

                $transaction->rollback();

            }

        }

        return $result;

    }

So you could do something similar and throw an exception when you notice that something is not ok - the Exception’s message would explain the reason.