When I run the following code the Containable behaviour does not work. I don't want to use a manual join!
$data = $this->Variant->find('first', array(
'contain' => array('VariantValue'),
'conditions' => array(
'Variant.product_id' => $product_id
),
'group' => 'VariantValue.variant_id having count(*) = 2'
));
Database:
CREATE TABLE `variants` (
`id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`price` decimal(7,2) NOT NULL
)
CREATE TABLE `variant_values` (
`id` int(11) NOT NULL,
`variant_id` int(11) NOT NULL,
`value_id` int(11) NOT NULL
)
The models:
class Variant extends AppModel {
public $actsAs = array('Containable');
public $belongsTo = array(
'Product' => array(
'className' => 'Product',
'foreignKey' => 'product_id'
)
);
public $hasMany = array('CartVariant', 'VariantValue');
}
class VariantValue extends AppModel {
public $actsAs = array('Containable');
public $belongsTo = array(
'Value' => array(
'className' => 'Value',
'foreignKey' => 'value_id'
),
'Variant' => array(
'className' => 'Variant',
'foreignKey' => 'variant_id'
)
);
}
The error message:
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column VariantValue.variant_id in group statement
SQL Query: SELECT Variant.id, Variant.product_id, Variant.price FROM variants AS Variant WHERE Variant.product_id = 1 GROUP BY VariantValue.variant_id having count(*) = 2
Try writing the code from the VariantValue perspective:
$data = $this->Variant->VariantValue->find('first', array(
'contain' => array(
'Variant' => array(
'conditions' => array('Variant.product_id' => $product_id))),
'group' => 'VariantValue.variant_id HAVING count(*) = 2'
));