cakephp group on assosiated model / table

Question

Short background: I have orders that contains products called 'Komplexes'. Komplexes have different sizes (height and width) if there are multiple Komplexes with the same measures in an order they have to be grouped and a counter must be added to create jobs for the workers.

My Models:

class Order extends AppModel {
public $hasMany = 'Komplex';
public $belongsTo = array(
    'Customer' => array(
        'counterCache' => true
    )
);
}


class Komplex extends AppModel {
public $belongsTo = array(
    'Order' => array(
        'counterCache' => true
    )
);
...<validation and calculations>
}

In my OrdersController I'm starting with

    public function orderproductionjob($id = NULL) {
    if (!$id) {
        throw new NotFoundException(__('Invalid ID'));
    }
    $order = $this->Order->find('all', array(
        'conditions' => array('Order.id =' => $id),
        'group' => array('Komplex.height')
    ));
    die(debug($order));

This gives me a database error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Komplex.height' in 'group statement'

The same 'find' without the 'group' gives me the right result (exept the grouping ;-)

So it's pretty obvious that I'm doing something wrong with the group. I could find examples for assosiations and examples for grouping on the web and in the cookbook but this combination wasn't mentioned or likely I haven't found it. As this is my first project with cakephp I'm hoping, that sombody with more experience can help me out.

What I'm trying to archive in SQL:

SELECT orders.id, orders.name, komplexes.width, komplexes.height, count(komplexes.id) as Count
FROM orders, komplexes
WHERE orders.id = 1 AND komplexes.order_id = orders.id
group by komplexes.width, komplexes.height;

Answer 1

Try changing your code to Group on the Komplex model.

$komplex = $this->Order->Komplex->find('all', array(
    'fields' => array('Komplex.height', 'Komplex.width', 'Count(*) as `Count`')
    'conditions' => array('Komplex.order_id =' => $id),
    'group' => array('Komplex.height', 'Komplex.width')
));

FYI

1. Your SQL statement works because you are guaranteed to only have 1 orders row. It can and most likely will return wrong results if you try to join to more than 1 orders row.
2. You need to be careful using SQL reserved words in your statement. In your case Count as the aliased column name. You may want to change that. Please note that my code sample has COUNT surrounded by backticks.