magento delete query with foreign key

Question

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

Answer 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.

Answer 2

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
                )

Answer 3

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();
}