CODEWITHSUNDEEP
phpmysqlcakephp
Alex
Alex

Reputation: 1028

CakePHP - Find parent based on child

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?

Upvotes: 2

Views: 3321

Answers (2)

bancer
bancer

Reputation: 7525

In your place I would do this:

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

HasMany does not joins table but belongsTo does.

Upvotes: 2

tigrang
tigrang

Reputation: 6767

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,
    ),
));

Upvotes: 3

Related Questions