Inventory count in CakePHP

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.

functio开发者_开发百科n 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.

---

When faced with a more complex multi-step problem like I would probably try using a temporary table.

So:

Create a temporary table with the value sets from stock_items and then update that table based on your linking logic with the order_items.

---

Actually, I think that's not possible. What I ended up doing was querying the two tables and then making the calculations with PHP.