1

How to delete the table record in magento using the foreign key. I have a two table.

question(table 1)
--------
question_id
question

answer(table 2)
------
answer_id
question_id
answer

When I delete the question that corresponding answer also need to delete. I did the the question(table 1) record delete using the below method.

 $model->load($id)
 $model->setId($model->getQuestionId())->delete()

There have any way to delete the answer(table 2) record in the above method

3
  • 2
    set forgin key for question_id in anwser table with on delete casecade Jun 25, 2015 at 10:30
  • thanks, but i like to work with the query, can you suggest your answer
    – senthil
    Jun 25, 2015 at 10:41
  • The simplest and cleanest solution is by using foreign key constraints directly from MySQL, unless you're using a Database Engine that does not support it. Jun 25, 2015 at 11:21

3 Answers 3

1

Try this:

$model->load($id);
$model->delete();

//delete answer
$answer_cllection = Mage::getModel('answer/model')->getCollection()->addFieldToFilter('question_id', $id);
foreach($answer_collection as $answer){
    $answer->delete();
}

Assumption

answer/model need to be replaced with your answer model name to call your answer model and $id is question id.

2
  • it will work, if the question have multiple answer ?
    – senthil
    Jun 25, 2015 at 10:40
  • Yes, Check my updated answer. Jun 25, 2015 at 10:51
0

Following is the installer script to create table with foreign Key.

<?php

$installer = $this;
$installer->startSetup();
$questionTable = $installer->getConnection()
        ->newTable($installer->getTable('mymodule/question'))
        ->addColumn('question_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
            'identity'  => true,
            'unsigned'  => true,
            'nullable'  => true,
            'primary'   => true
            ), 'Question ID')
        ->addColumn('question', Varien_Db_Ddl_Table::TYPE_VARCHAR, null, array(), 'Question')
        ->setComment('Question Table');
$installer->getConnection()->createTable($questionTable);

$answerTable = $installer->getConnection()
        ->newTable($installer->getTable('mymodule/answer'))
        ->addColumn('answer_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
            'identity'  => true,
            'unsigned'  => true,
            'nullable'  => true,
            'primary'   => true
            ), 'Answer ID')
        ->addColumn('question_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
            'nullable'  => true,
            'default'   => null
            ), 'Question ID')
        ->addColumn('answer', Varien_Db_Ddl_Table::TYPE_VARCHAR, null, array(), 'Answer')
        ->addForeignKey(
            $installer->getFkName('mymodule/answer', 'question_id', 'mymodule/answer', 'answer_id') ,
            'question_id',
            $installer->getTable('mymodule/answer'),
            'question_id',
            Varien_Db_Ddl_Table::ACTION_CASCADE,
            Varien_Db_Ddl_Table::ACTION_CASCADE
            )
        ->setComment('Answer Table');
$installer->getConnection()->createTable($anwserTable);

?>

Here

->addForeignKey(
                $installer->getFkName('mymodule/answer', 'question_id', 'mymodule/answer', 'answer_id') , // It will create unique Foreign key name
                'question_id', // Question table primary key
                $installer->getTable('mymodule/answer'), // Current table name
                'question_id', // Foreign key for question table
                Varien_Db_Ddl_Table::ACTION_CASCADE,
                Varien_Db_Ddl_Table::ACTION_CASCADE
                )
0

You can do this using _beforeDelete() function at model resource class of first model.

protected function _beforeDelete(Mage_Core_Model_Abstract $object)
    {
        $condition = array(
            'question_id = ?'     => (int) $object->getId(),
        );

        $this->_getWriteAdapter()->delete($this->getTable('Yourmoduldeprecfix/TableEnitity'), $condition);
    return parent::_beforeDelete($object);
}

Example:

Magento default have an good idea at Mage_Cms module cms page/block models.

Here if you delete data from cms/page model then it is automatically delete from cms_store table

see at Mage_Cms_Model_Resource_Page

Another solution:

$Answers=Mage::getModel('flquestion/answer')->addFieldToFilter('question_id', $id);

foreach($Answers as $eachanswer){
     $answer=Mage::getModel('flquestion/answer')->load($eachanswer->getId());
    $answer->delete();
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.