CakePHP returning multiple records from a habtm

Question

I have a habtm relation between questions and categories. This habtm relation is standard and setup in the respective models. I now have the following issue. In a controller action I have an array of several question ids. I want to return all of the categories that are linked to these questions. The find I am using looks as followed.

$case_questions_categories = $this->Category->find('all', array('conditions' => array('OR' => $case_question_id_array)));

    // Here the $case_question_id_array is generated as followed:
    foreach($question['CaseQuestion'] as $case_question) {
        $case_question_id_array[] = "Category.question_id = " . $case_question['id'];
    }

Its clear that this will not work, and it throws the following error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Category.question_id' in 'where clause'

SQL Query: SELECT `Category`.`id`, `Category`.`name`, `Category`.`slug`, `Category`.`category_order`, `Category`.`created`, `Category`.`modified`, `Category`.`is_deleted` FROM `streetofwalls`.`categories` AS `Category` WHERE `Category`.`question_id` = 182

This makes sense because the join table from the habtm relation is not referenced in the query I have generated. How do I get habtm relation to be properly handled so that I can pass a group of question ids and be returned the relevant categories.

Thank you to anyone who offers time or help.

You could unbind the habtm and bind a belongsTo on the fly: book.cakephp.org/2.0/en/models/… — timstermatic
@timstermatic I'm not sure what you mean. Is binding a cakePHP thing? — usumoio
Yes, in the controller you can add a different type of association on the fly. — timstermatic

AD7six AD7six · Accepted Answer · 2013-06-05T19:57:01

Use a join

The kind of sql query you need to generate is:

SELECT 
    categories.* from categories 
JOIN 
    categories_questions ON (categories_questions.category_id = categories.id) 
WHERE 
    categories_questions.question_id IN (list, of, question, ids)

The simplest way to do that is to just use the join key in your query - this is also in the documentation.

Applied to the example in the question that'd be:

$ids = array()
foreach($question['CaseQuestion'] as $q) {
    $ids[] = $q['id'];
}

$this->Category->find('all', array(
    'conditions' => array(
        'CategoriesQuestion.id' => $ids // You do not need an OR
    ),
    'joins' => array(
        array(
            'table' => 'categories_questions',
            'alias' => 'CategoriesQuestion',
            'type' => 'inner',
            'conditions' => array(
                'Category.id = CategoriesQuestion.category_id'
            )
        )
    )
));

CakePHP returning multiple records from a habtm

1 Answers

Use a join