[FIXED] CakePHP: retrieve list with conditional arguments

Issue

I'm having hard time to understand how to retrieve associated data with a conditional formatting. I'm using CakePHP 3.7.9.

Table products

id
name
customer_code

Table orders

id
name
date

Table item_orders

id
order_id
name
product_id
description

In a View (of another controller) I have two select controls. The first one is populated with id|name of the existing orders. When the user selects one, the second select control should be populated following this criteria:

• if product_id is not null -> retrieve name (customer_code) in the products table
• else use the description value

I use ajax to send the current order_id to the controller that should send back the html needed to populate the second select:

<?php $this->Html->scriptStart(['block' => 'script', 'inline' => false]); ?>
    $(document).on('change', '#order-id', function() {
        $.ajax({
            type: "POST",
            headers: { 'X-CSRF-Token': <?= json_encode($this->request->getParam('_csrfToken')); ?> },
            url:  '<?php echo Router::url(array('controller' => 'ItemDeliveryNotes', 'action' => 'getOrderItems'));?>',
            data: { 'orderId': $('#order-id').val() },
            success: function(response) {
                $('#itemOrders').html(response.data.itemOrders);
            }
        });
    });
<?php $this->Html->scriptEnd(); ?>

public function getOrderItems()
{
    if ($this->request->is(['ajax', 'post']))
    {
        $id = $this->request->getData('orderId'); // <-- fixed
        $items = $this->getItems($id);
        $combo = [];
        foreach ($items as $key => $value)
        {
            $combo[] = "<option value='" . $key . "'>" . $value . "</option>";
        }

        $data = ['data' => ['itemOrders' => $combo]];
        return $this->json($data);
    }
}

private function getItems($id = null)
{
    $items = ???; // <-- here I need to retrieve the list as above
    return $items;
}

I'm able to fetch data from a single source, but in this case I don't understand how to compose the query.

UPDATE

I tried with this code:

private function getItems($id = null)
{
    $items = $this->ItemsDeliveryNotes->Orders->ItemOrders->
        find('list', [
            'keyField' => 'id',
            'valueField' => function ($q) {
                $productId = $q->get('product_id');
                if ($productId) {
                    $code = $this->ItemsDeliveryNotes->Orders->ItemOrders->Products->field('code', ['id' => $productId]);
                    $customerCode = $this->ItemsDeliveryNotes->Orders->ItemOrders->Products->field('customerCode', ['id' => $productId]);
                    return $code . ' (' . $customerCode . ')';
                }
                else return $q->get('description');
            }
        ])->where(['order_id' => $id]);
    return $items;
}

It works fine but the where clause. Without, I obtain all the items from all the orders using the requested criteria. But of course I'm interested only in the order that match the $id. Adding the where filter, nothing is returned.

Solution

As mentioned in the comments, you were sending the data as orderId, but accessing it as order_id, and as you've figured you were also using the wrong controller name.

You most likely didn't receive an error when generating the URL because you are using fallbacks, or manual catch-all routes, that is routes like /:controller/:action, which will match any controller/action. An error will be thrown when the request is being made to the non-existent endpoint, but you haven't defined an error/failure handler for your AJAX call, so the error is being swallowed, still there should be something in your CakePHP error logs.

That being said, you really shouldn't issue additional queries per row (ie issuing queries in the valueField callback), instead filter either on SQL level, for example using a CASE expression, or contain the Products association, so that you have all the data you need for filtering on PHP level, which should be as simple as:

$items = $this->ItemsDeliveryNotes->Orders->ItemOrders
    ->find('list', [
        'keyField' => 'id',
        'valueField' => function (\Cake\Datasource\EntityInterface $row) {
            if ($row->has('product')) {
                return $row->product->name . ' (' . $row->product->customer_code . ')';
            }

            return $row->description;
        }
    ])
    ->contain('Products')
    ->where([
        'order_id' => $id
    ]);

Answered By - ndm