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

  • 2
    set forgin key for question_id in anwser table with on delete casecade – MeenakshiSundaram R Jun 25 '15 at 10:30
  • thanks, but i like to work with the query, can you suggest your answer – senthil Jun 25 '15 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. – Vernard Luz Jun 25 '15 at 11:21
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.

  • it will work, if the question have multiple answer ? – senthil Jun 25 '15 at 10:40
  • Yes, Check my updated answer. – Adarsh Khatri Jun 25 '15 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, privacy policy and cookie policy

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