Delete related records , having has_many relationship

Question

i have has_many relationship between two tables like : "questions"-< "options" [ a question have many options] my class structure is : for question

class Model_Admin_sysQuestion extends ORM {

protected $_table_name = 'questions';

protected $_has_many = array(
    'options' => array(
        'model' => 'Admin_sysQuestionOption',
        'foreign_key' => 'question_id',
    ),
);... .

for options

Class Model_Admin_sysQuestionOption extends ORM {

protected $_table_name = 'questions_options';
protected $_belongs_to = array(
    'question' => array(
        'model' => 'Admin_sysSection',
        'foreign_key' => 'question_id',
    ),
); .... .

and i m trying to delete questions with all its options with the following code:

 $question = ORM::factory('Admin_sysQuestion', 30);
        $question->options->delete($question->id);
        $question->delete();

but it is giving error

error":"Cannot delete admin_sysquestionoption model because it is not loaded."

any idea? how to do it?

Answer 1

In addition to the post of @biakevoron you also have the MySQL option; just add a relationship between the two tables with an on delete cascade requirement on the options table. Personally, I also check for objects/rows relational to the current object working on, covering both the explicit (delete relational objects) as the implicit side of the game. You should in fact be able to trust MySQL doing it's job (which it does pretty well), but if someone ie. alters the relationship, or switches the table to MyIsam you'll probably not notice untill the table get's really, really big.

Play around with the following table. It forces a strict belongs-to relationship on the options table, meaning an options can only exist if it's parent exists.

CREATE TABLE `options` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `question_id` int(10) unsigned NOT NULL,
  `option` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE `questions` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `question` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


ALTER TABLE `options`
  ADD CONSTRAINT `options_belongs_to` FOREIGN KEY (`id`) REFERENCES `questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Note - The only restriction is you need to put the tables on the InnoDB engine (which is slightly different from the default MyIsam engine, checkout a summary here and a more extensive version here.

Answer 2

When loading multiple relations you must call find_all:

foreach($question->options->find_all() as $option)
{
   $option->delete();
}

Or use DB QBuilder for multiple deleting:

DB::delete('questions_options')
    ->where('question_id', '=', 30)
    ->execute($this->_db);