Cakephp 4 passing sub query from controller to view

Question

I'm trying to create a summary of orders from the database each line in the database contains a sepertate row for each item and is linked together by an order number so for example.

Order_number	item	Customer_name
ord001	widget	Joe Bloggs
ord001	thingy	Joe Bloggs
ord002	widget	Fred Flintstone

What i'm trying to create is a summary for each order number on a single page so it would look something like this:-

Order Number	ord001
Customer Name	Joe Bloggs
Item	widget
Item	thingy

Order Number	ord002
Customer Name	fred flintstone
Item	widget

I can get this to work to a certain degree from using echo within the controller this is my controller:-

    {
        $tomorrow = date("Y-m-d", strtotime('tomorrow'));
        $customer = $this->Customer->find()
            ->select(['order_number' => 'DISTINCT order_number'])
            ->where(['Customer.del_date LIKE' => $tomorrow])
            ->where (['Customer.van_number LIKE' => '2 - Internet Van']);

        $this->set('customer',$customer);

            foreach ($customer->all() as $order) {
            $ordnumber = ($order->order_number);
            echo $ordnumber."<br />";
            $orders = ($this->Customer->find()
                ->where(['order_number' => $ordnumber])
            );
            $this->set(compact('orders'));
          
            foreach ($orders as $order) {
            echo $order->item."<br />";
            }
           echo $order->customer_name."<br />";
            echo "<hr>";
            }

    

}

My problem is that when i try and pass the same variables to the view it only shows the last order from the loop which in this example is ord002 which is confusing me, here is my view

<?php foreach ($orders as $order) { ?>
<?= h($order->item) ?>
<br >
    <?php } ?>

<?= h($order->customer_name) ?>

Answer 1

You are overwriting $orders (in the view) with each call of $this->set(compact('orders'));

You should try to have every output logic in your view (and not in the controller). Fetch you data in your controller (make use of HasMany Relationship and ->contain()).

Loop through your customers and orders in your view.

Executing a SQL Query in a loop is usually not the best solution for your performance.

Also try to avoid using $this->set() in a loop. It is just error-prone and hard to debug. Instead fetch your data in an array or object and pass it in a single $this->set() call at the end of your controllers action to the view.

Answer 2

You should not put $this->set inside the loop in controller, try this in your controller and call the 'orders' variable in template.

{
    $tomorrow = date("Y-m-d", strtotime('tomorrow'));
    $customer = $this->Customer->find('all')
        ->select(['order_number' => 'DISTINCT order_number'])
        ->where(['Customer.del_date LIKE' => $tomorrow])
        ->where(['Customer.van_number LIKE' => '2 - Internet Van']);

    $orders = '';
    foreach ($customer->all() as $key => $order) {

        $ordnumber = $order->order_number;

        $orders = $this->Customer->find('all')->where(['order_number' => $ordnumber])->all();
    }

    $this->set(compact('orders'));    

}