CakePHP Many To Many With Conditions

Question

I'm newbie on CakePHP, and now I'm stuck on many to many situation

ok, i have 3 Table :

1. questions with fields (id, question)
2. question_product with fields (id, question_id, product_id, question_number, is_enabled)
3. products with fields (id, name, code, is_enabled)

so when i want to select questions with specific field, i don't know how to fix it for now, my code is like this :

Question.php (Model)

class Question extends AppModel {

    public $hasAndBelongsToMany = array (
        'Product' => array (
            'joinTable' => 'question_product',
            'foreignKey' => 'question_id',
            'associationForeignKey' => 'product_id',
            'unique' => 'keepExisting',
            'order' => 'question_number',
            'fields' => array (
                'QuestionProduct.question_number',
                'Product.id',
                'Product.name'
            ),
            'conditions' => array (
                'QuestionProduct.is_enabled' => 1,
            )
        )
    );
}

QuestionsController.php (Controller)

public function loadQuestions($productId) {
    $this->view = 'load_questions';
    $questions = $this->Question->find('all', array (
        'fields' => array (
            'Question.id',
            'Question.question',
            'Question.is_optional',
            'Question.reason_optional',
            'Question.text_size'
        ),
        'conditions' => array (
            'QuestionProduct.product_id' => $productId
        )
    ));
    $this->set($questions);
}

method loadQuestions have one parameter to select with specified product

if i using sql query, it will be like this

select all from Question with condition Product.product_id=4, sorted by QuestionProduct.question_number ascending

select questions.*
from questions
join question_product on questions.id=question_product.question_id
join products on products.id=question_product.product_id
where products.id=4
order by question_product.question_number;

any answer will be appreciated :)

Thanks !

Answer 1

Any time you use a many-many (HABTM) relation with any other field that requires conditions, it is no longer many-many as far as Cake is concerned. You want the HasManyThrough relationship