Memory exhausted cakephp DB query
<p>I am new to CakePHP 2.4 and I hope someone can help me with a problem I have.</p>

<p>I have the memory exhausted problem in my project - and I have debugged it and got everything to work with 2 GB of memory - but my webhosting company only allows 1 GB.</p>

<p>So I was wondering what made the MySQL use up so much RAM - it happens when I add a coupon to an order - and the coupons contains every product in my shop.</p>

<p>I put debug before the line that caused the problem and a long list of arrays (approx 1200 A4 pages) - I find that a lot and I guess that is why the <code>mysql.php</code> in cake makes the following error:</p>

<pre><code>Fatal error: Allowed memory size of 188743680 bytes exhausted 
(tried to allocate 32 bytes) in lib/Cake/Model/Datasource/Database/Mysql.php on line 277
</code></pre>

<p>Is there a way to go around this - so either the call is smaller or the memory is increased.</p>

<p>EDITED: I think it's not the Coupon controller/model that makes the problem it's the order model cause it fetches all products - have put up the code below:   </p>

<p>Hope someone will take a look at it at give me a hint to how I can get it to work.
I have tried debug the memory usage and it only uses about 3 MB - but when add the coupon it just floods the memory and runs forever.</p>

<p>THE ORDER MODEL</p>

<pre><code>&lt;?php
App::uses('AppModel', 'Model');

class Order extends AppModel
{
    public $displayField = 'id';

    private $shipping_price = 39;
    private $free_shipping_threshold = 500;
    private $tax_rate = 0.20;

    public $validate = array(
        'state' =&gt; array(
            'rule'    =&gt; array('inList', array('unplaced', 'received', 'packed', 'resolved')),
            'allowEmpty' =&gt; false,
            'message' =&gt; 'Du skal vælge et stadie.'
         ),
        'shipping_price' =&gt; array(
            'decimal' =&gt; array(
                'rule' =&gt; 'decimal',
                'allowEmpty' =&gt; false,
                'message' =&gt; 'Du skal angive en pris.',
            ),
            'notNegative' =&gt; array(
                'rule'    =&gt; array('comparison', '&gt;=', 0),
                'message' =&gt; 'En pris kan ikke være negativ.'
            )
        ),
    );

    public $hasMany = array(
        'OrderItem'
    );

    public $belongsTo = array(
        'Customer',
        'Coupon'
    );

    private function purgeInactives($order_id)
    {
        // Find the order
        $order = $this-&gt;find('first', array('conditions' =&gt; array('Order.id' =&gt; $order_id), 'recursive' =&gt; 2));

        // Run through all order items in the order
        foreach ($order['OrderItem'] as $key =&gt; $order_item) {

        // Check if the order was a yarn batch
            if ($order_item['yarn_batch_id'] != 0) {
            // Check if the item is active
                if (!$order_item['YarnBatch']['is_active']) {
                    $this-&gt;OrderItem-&gt;delete($order_item['id']);
                    SessionComponent::setFlash(' Der blev fjernet en varer fra din indkøbskurv da det er ikke længere er aktivt. '.SessionComponent::read('Message.warning.message'), null, array(), 'warning');
                } // Check if there are any of this item left in stock
                else if ($order_item['YarnBatch']['stock_quantity'] &lt; 1) {
                    $this-&gt;OrderItem-&gt;delete($order_item['id']);
                    SessionComponent::setFlash(' En af varene i din indkøbskurv er ikke længere på lager. '.SessionComponent::read('Message.warning.message'), null, array(), 'warning');
                }
            } // Check if the order was a needle variant
            else if ($order_item['needle_variant_id'] != 0) {
            // Check if the item is active
                if (!$order_item['NeedleVariant']['is_active']) {
                    $this-&gt;OrderItem-&gt;delete($order_item['id']);
                    SessionComponent::setFlash(' Der blev fjernet en varer fra din indkøbskurv da det er ikke længere er aktivt. '.SessionComponent::read('Message.warning.message'), null, array(), 'warning');
                } // Check if there are any of this item left in stock
                else if ($order_item['NeedleVariant']['stock_quantity'] &lt; 1) {
                    $this-&gt;OrderItem-&gt;delete($order_item['id']);
                    SessionComponent::setFlash(' En af varene i din indkøbskurv er ikke længere på lager. '.SessionComponent::read('Message.warning.message'), null, array(), 'warning');
                }
            } // Check if the order was a recipe
            else if ($order_item['recipe_id'] != 0) {
            // Check if the item is active
                if (!$order_item['Recipe']['is_active']) {
                    $this-&gt;OrderItem-&gt;delete($order_item['id']);
                    SessionComponent::setFlash(' Der blev fjernet en varer fra din indkøbskurv da det er ikke længere er aktivt. '.SessionComponent::read('Message.warning.message'), null, array(), 'warning');
                }
            } else if ($order_item['color_sample_id'] != 0) {
            // Check if the item is active
                if (!$order_item['ColorSample']['is_active']) {
                    $this-&gt;OrderItem-&gt;delete($order_item['id']);
                    SessionComponent::setFlash(' Der blev fjernet en varer fra din indkøbskurv da det er ikke længere er aktivt. '.SessionComponent::read('Message.warning.message'), null, array(), 'warning');
                }
            } else {
                $this-&gt;OrderItem-&gt;delete($order_item['id']);
            }
        }
        // If the order have a coupon and it is no long active
        // if ($order['Coupon']['id'] !=0 &amp;&amp; !$order['Coupon']['is_active']) {
        //     SessionComponent::setFlash(' Kuponen i din indkøbskurv blev fjernet fordi den ikke længere er gyldig. '.SessionComponent::read('Message.warning.message'), null, array(), 'warning');
        //     $this-&gt;Order-&gt;set($order);
        //     $this-&gt;Order-&gt;saveField('coupon_id', null);
        // }

    }

    private function resetOrderItemsPrices($order_id)
    {
        $order_items = $this-&gt;OrderItem-&gt;find('all', array('conditions' =&gt; array('OrderItem.order_id' =&gt; $order_id)));

        foreach ($order_items as $key =&gt; $order_item) {
            $this-&gt;OrderItem-&gt;set($order_item);
            $this-&gt;OrderItem-&gt;resetPrice();
        }
    }

    public function refresh()
    {
        // Remove edited or removed items
        $this-&gt;purgeInactives($this-&gt;id);
        $this-&gt;resetOrderItemsPrices($this-&gt;id);

        $order = $this-&gt;find('first', array('conditions' =&gt; array('Order.id' =&gt; $this-&gt;id), 'recursive' =&gt; 2));


        // Items order by prices, highest first
        $order_items = Set::sort($order['OrderItem'], '{n}.price', 'desc');
        $order_price = 0;
        $order_saving = 0;
        $total_amount_of_items = 0;

        $is_only_color_samples = true;

        // Run through all items
        foreach ($order_items as $key =&gt; $order_item) {
            $new_total_item_price = $order_item['price'];
            if ($order['Coupon']['id'] != 0) {
                if ($order_item['yarn_batch_id'] != 0) {
                    // There exist an item that is not a color sample
                    $is_only_color_samples = false;
                    foreach ($order['Coupon']['YarnBatch'] as $key =&gt; $yarn_batch_on_coupon) {

                        if ($yarn_batch_on_coupon['id'] == $order_item['yarn_batch_id']) {
                            // Calcualte savings
                            $calculated_savings = $this-&gt;calculateSaving(
                                $yarn_batch_on_coupon['price'],
                                $order_item['amount'],
                                $order['Coupon']['percentage_discount'],
                                $order['Coupon']['actual_discount'],
                                $order['Coupon']['item_amount']
                            );

                            // Was this a better soloution
                            if ($order_item['saving'] &lt; $order_item['price'] - $calculated_savings['new_total_item_price']) {
                                $new_total_item_price = $calculated_savings['new_total_item_price'];
                                $best_coupon['amount_left'] = $calculated_savings['coupon_item_amount_left'];
                                $best_coupon['id'] = $order['Coupon']['id'];
                            }
                        } else {
                            continue;
                        }

                    }
                } else if ($order_item['needle_variant_id'] != 0) {
                    // There exist an item that is not a color sample
                    $is_only_color_samples = false;

                    if (!$order_item['NeedleVariant']['is_active']) {
                        foreach ($order['Coupon']['YarnBatch'] as $key =&gt; $yarn_batch_on_coupon) {
                            if ($needle_variant_on_coupon['id'] == $order_item['needle_variant_id']) {
                                // Calcualte savings
                                $calculated_savings = $this-&gt;calculateSaving(
                                    $needle_variant_on_coupon['price'],
                                    $order_item['amount'],
                                    $order['Coupon']['percentage_discount'],
                                    $order['Coupon']['actual_discount'],
                                    $order['Coupon']['item_amount']
                                );

                                // Was this a better soloution
                                if ($order_item['saving'] &lt; $order_item['price'] - $calculated_savings['new_total_item_price']) {
                                    $new_total_item_price = $calculated_savings['new_total_item_price'];
                                    $best_coupon['amount_left'] = $calculated_savings['coupon_item_amount_left'];
                                    $best_coupon['id'] = $order['Coupon']['id'];
                                }
                            } else {
                                continue;
                            }
                        }
                    }
                } else if ($order_item['recipe_id'] != 0) {
                    // There exist an item that is not a color sample
                    $is_only_color_samples = false;
                }

                $order_item['saving'] = $order_item['price'] - $new_total_item_price;

            }

                // Calucalte the prices of item
                $order_saving += $order_item['saving'];
                $order_price += $order_item['price'] - $order_item['saving'];

                // Store prices on item
                $this-&gt;OrderItem-&gt;set($order_item);
                $this-&gt;OrderItem-&gt;saveField('saving', $order_item['saving']);
                $this-&gt;OrderItem-&gt;saveField('price', $order_item['price'] - $order_item['saving']);

            // Count up the counter
            $total_amount_of_items += $order_item['amount'];
        }
        $shipping_price = 0;

        // Calculate shipping prince and remainder (if it is only color samples keep it at 0)
        if ($order_price &lt; $this-&gt;free_shipping_threshold) {
            $shipping_price = $this-&gt;shipping_price;
        }

        $free_shipping_remainder = $this-&gt;free_shipping_threshold - $order_price;



        $order_price = $order_price + $shipping_price;

        // Calculate taxing
        $tax = $order_price * $this-&gt;tax_rate;
        $sub_total = $order_price - $tax;

        // Store order data
        $this-&gt;saveField('shipping_price', $shipping_price);
        $this-&gt;saveField('free_shipping_remainder', $free_shipping_remainder);
        $this-&gt;saveField('amount', $total_amount_of_items);
        $this-&gt;saveField('saving', $order_saving);
        $this-&gt;saveField('price', $order_price);
        $this-&gt;saveField('tax', $tax);
        $this-&gt;saveField('sub_total', $sub_total);
        $this-&gt;saveField('modified', date('Y-m-d H:i:s'));

    }

    private function calculateSaving($item_price, $item_amount, $coupon_saving_percent, $coupon_saving_actual, $coupon_item_amount)
    {
        $new_item_price = 0;
        $new_total_item_price = 0;
        $coupon_item_amount_left = 0;

        // Check if the percentage discount is null, then use actual discount otherwise use percentage
        if ($coupon_saving_percent == null) {
            $new_item_price = $item_price - $coupon_saving_actual;
            $new_item_price = $new_item_price &lt; 0 ? 0 : $new_item_price;
        } else {
            $new_item_price = $item_price - ($item_price * $coupon_saving_percent/100 );
        }

        // Check if there is enough to get savings on all of this particular item
        if ($item_amount &lt;= $coupon_item_amount) {
            $new_total_item_price = $new_item_price * $item_amount;
            $coupon_item_amount_left = $coupon_item_amount - $item_amount;
        } else {
            $new_total_item_price = $new_item_price * $coupon_item_amount + $item_price * ($item_amount - $coupon_item_amount);
            $coupon_item_amount_left = $coupon_item_amount;
        }

        return array('new_total_item_price' =&gt; $new_total_item_price, 'coupon_item_amount_left' =&gt; $coupon_item_amount_left);
    }

    public function updateStockQuantityAfterPurchase()
    {
        // Find the order
        $order = $this-&gt;find('first', array('conditions' =&gt; array('Order.id' =&gt; $this-&gt;id), 'recursive' =&gt; 2));

        // Run through all order items in the order
        foreach ($order['OrderItem'] as $key =&gt; $order_item) {
        // Check if the order was a yarn batch
            if ($order_item['yarn_batch_id'] != 0) {
            // Find the item
                $yarn_batch = $this-&gt;OrderItem-&gt;YarnBatch-&gt;find('first', array('conditions' =&gt; array('YarnBatch.id' =&gt; $order_item['yarn_batch_id'])));

                // Calculate new stock amount
                $new_stock_quantity = $yarn_batch['YarnBatch']['stock_quantity'] - $order_item['amount'];

                $this-&gt;OrderItem-&gt;YarnBatch-&gt;id = $yarn_batch['YarnBatch']['id'];
                $this-&gt;OrderItem-&gt;YarnBatch-&gt;saveField('stock_quantity', $new_stock_quantity);
            } // Check if the order was a needle variant
            else if ($order_item['needle_variant_id'] != 0) {
            // Find the item
                $needle_variant = $this-&gt;OrderItem-&gt;NeedleVariant-&gt;find('first', array('conditions' =&gt; array('NeedleVariant.id' =&gt; $order_item['needle_variant_id'])));

                // Calculate new stock amount
                $new_stock_quantity = $needle_variant['NeedleVariant']['stock_quantity'] - $order_item['amount'];

                $this-&gt;OrderItem-&gt;NeedleVariant-&gt;id = $needle_variant['NeedleVariant']['id'];
                $this-&gt;OrderItem-&gt;NeedleVariant-&gt;saveField('stock_quantity', $new_stock_quantity);
            }
        }

        // If the order have a coupon and it is no long active
        if ($order['Coupon']['id'] != 0 &amp;&amp; !$order['Coupon']['is_active']) {
            SessionComponent::setFlash(' Kuponnen i din indkøbskurv blev fjernet fordi den ikke længere er gyldig. '.SessionComponent::read('Message.warning.message'), null, array(), 'warning');
            $this-&gt;Order-&gt;set($order);
            $this-&gt;Order-&gt;saveField('coupon_id', null);
        }

    }
}
</code></pre>