We are developing an inventory tracking system. Basically we've got an order table in which orders are placed. When an order is payed, the status changes from 0 to 1. This table has multiple children in another table order_items.

This is the main structure.

CREATE TABLE order(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    status INT(1),
    total INT UNSIGNED
);
CREATE TABLE order_items(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED,
    article_id INT UNSIGNED,
    size enum('s', 'm', 'l', 'xl'),
    quantity INT UNSIGNED
);

Now, we've got a stocks table with similar architecture for the acquisitions. This is the structure.

CREATE TABLE stock(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    article_id INT UNSIGNED
);
CREATE TABLE stock_items(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stock_id INT UNSIGNED,
    size enum('s', 'm', 'l', 'xl'),
    quantity INT(2)
);

The main difference is that stocks has no status field. What we are looking for is a way to sum each article size from stock_items, then sum each article size from order_items where Order.status = 1 and substract both these items to find our current inventory.

This is the table we want to get from a single query:

Size     | Stocks   | Sales    | Available
  s      |     10   |      3   |       7  
  m      |     15   |     13   |       2  
  l      |      7   |      4   |       3  

Initially we thought abouth using complex find conditions, but perhaps that's the wrong approach.

Also, since it's not a direct join, it turns out to be quite hard.

This is the code we have to retrieve the stock's total for each item.

function stocks_total($id){
    $find = $this->StockItem->find('all', array(
        'conditions' => array(
            'StockItem.stock_id' => $this->find('list', array('conditions' => array('Stock.article_id' => $id)))
        ),
        'fields' => array_merge(
            array(
                'SUM(StockItem.cantidad) as total'
            ),
            array_keys($this->StockItem->_schema)
        ),
        'group' => 'StockItem.size',
        'order' => 'FIELD(StockItem.size, \'s\', \'m\' ,\'l\' ,\'xl\') ASC'
    ));
    return $find;
}

Thanks.