Trying to save HABTM model data in CakePHP 2.0

Question

I have a data model that requires the use of HABTM, as follows:

surveys 
(
    id int(11) NOT NULL AUTO_INCREMENT,
    user_id int(11) NOT NULL,
    title varchar(50) DEFAULT NULL,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (id),
    KEY user_id (user_id)
);

questions 
(
    id int(11) NOT NULL AUTO_INCREMENT,
    user_id int(11) NOT NULL,
    title varchar(50) NOT NULL,
    body text NOT NULL,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (id),
    KEY user_id (user_id)
);

questions_surveys  
(  
    id int(11) NOT NULL AUTO_INCREMENT,  
    survey_id int(11) NOT NULL,  
    question_id int(11) NOT NULL,  
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
    PRIMARY KEY (id),  
    KEY survey_id (survey_id),  
    KEY question_id (question_id)  
);

And the relevant foreign keys:

ALTER TABLE questions_surveys
ADD CONSTRAINT questions_surveys_ibfk_1 FOREIGN KEY(survey_id) REFERENCES surveys(id);

ALTER TABLE questions_surveys
ADD CONSTRAINT questions_surveys_ibfk_2 FOREIGN KEY(question_id) REFERENCES questions(id);

Questions and Surveys have an HABTM relationship, so that one survey has many questions and one question can be in many different surveys.

In Survey.php:

public $hasAndBelongsToMany = array(
    'Question' => array(
        'className' => 'Question',
        'joinTable' => 'questions_surveys',
        'foreignKey' => 'survey_id',
        'associationForeignKey' => 'question_id'
    )
);

In Question.php:

public $hasAndBelongsToMany = array(
    'Survey' => array(
        'className' => 'Survey',
        'joinTable' => 'questions_surveys',
        'foreignKey' => 'question_id',
        'associationForeignKey' => 'survey_id'
    )
);

Here's my add controller from SurveysController.php:

public function add()
{
    $this->set('fields', $this->Survey->getFields());
    $this->set('users', $this->Survey->User->find('list', array('fields' => array('id', 'username'))));
    $this->set('questions', $this->Question->find('list', array('fields' => array('id', 'body'))));

    if (!empty($this->data))
    {
        $this->Survey->saveAll($this->data['Survey']);

        foreach($this->data['Question']['id'] as $question_id)
        {
            $newdata[] = array('Survey' => array('id' => $this->Survey->getInsertID()), 'Question' => array('id' => $question_id));
        }

        if ($this->Survey->saveAll($newdata))
        {
            $this->Session->setFlash('The survey was successfully added!');
            $this->redirect(array('action'=>'index'));
        }
        else
        {
            $this->Session->setFlash('Unable to add survey.');
        }
    }
}

The new survey is saved first, and then every question_survey is added to an array that then adds all of them at once. The data looks like this:

I keep getting this error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'QuestionsSurvey.survey_id' in 'where clause'
SQL Query: SELECT AppModel.question_id FROM engage.questions_surveys AS AppModel WHERE QuestionsSurvey.survey_id = 13

As far as I can tell, everything is named in accordance with CakePHP standards, and I tried using 'with' => 'QuestionsSurvey', but get this error:

Missing Database Table Error: Table app_models for model QuestionsSurvey was not found in datasource default.

And 'with' => 'QuestionsSurveys', but get the same error:

Missing Database Table Error: Table app_models for model QuestionsSurveys was not found in datasource default.

I've tried converting the model trio to a hasMany through model (didn't work, it all just said go back to HABTM).

I've used all kinds of different formats for the data (CakePHP Saving Your Data) but no luck there either.

I'm stumped. Does anybody know what I'm doing wrong? Also, I apologize for the very long entry and code sections but I wanted to make sure to be thorough.

Thank you for your time!
Matt

mmahgoub mmahgoub · Accepted Answer · 2013-09-23T08:31:53

Your data should look like the following:

array(
    'Survey' => array(
        'title' => 'example',
    ),
    'Question' => array(
        (int) 0 => '1',
        (int) 1 => '2',
    )
)

to save the data use: $this->Survey->saveAll($data);

Thanks

Trying to save HABTM model data in CakePHP 2.0

2 Answers