CakePHP - 根据孩子查找父母

Question

I have three tables companies, products and prices.

Companies hasMany Products
Products hasMany Prices

I'm currently trying to search these tables for products that are less than a certain price, the code I'm trying is:

$results = $this->Product->find('all', array(
    'conditions' => array(
        'Price.price <=' 10
    )
));

For some reason using this code cakephp this brings back an error:

Unknown column 'Price.price' in 'where clause'

I think this is because products have multiple prices (it's joining with the companies table not the prices table), can anyone tell me why this is going wrong and know how to get it working?

Note: After getting this working, I want to be able to find products based on criteria from the prices and products tables, then display data from all 3 in a results page.

I thought about first searching the prices table and then searching the products table, but I believe there must be a more efficient way?

Answer 1

You may use adhoc-joins for these types of queries. Cake doesn't do joins for 1:n or n:n relationships, so you have to specify it manually.

$results = $this->Product->find('all', array(
    'joins' => array(
        array(
            'alias' => 'Price',
            'table' => 'prices',
            'foreignKey' => false,
            'conditions' => array('Price.product_id = Product.id'),
        ),
    ),
    'conditions' => array(
        'Price.price <=' => 10,
    ),
));