WooCommerce Orders and Items Array with JQuery DataTables

Question

I'm trying to grab some WooCommerce orders and the items within those orders and put them into an array that jQuery datatables can read.

So far I can get the order details in the array and display them just fine but I'm having a hard time figuring out how to get the array of items into the main array.

Here is what I have so far, this is displaying the order details (without the item details) perfectly fine.

The PHP Code

foreach ($orders as $order) {
    $order_data[] = array(
        'order_date' => $order->get_date_created()->format('m-d-y'),
        'order_id' => $order->get_id(),
        'order_billing_fName' => $order->get_billing_first_name(),
        'order_billing_lName' => $order->get_billing_last_name(),
        'order_email' => $order->get_billing_email(),
        'order_total' => $order->get_formatted_order_total(),
    );
}

$response['data'] = !empty($order_data) ? $order_data: [];     
$response['recordsTotal'] = !empty($order_data) ? count($order_data) : 0;

wp_send_json($response);

The jQuery Code

var dt = $('#example').DataTable({    
    ajax: {
        url: "/wp-admin/admin-ajax.php?action=shared_order_history",
        cache:false,
    },
    columns: [
        {data: 'order_date'},
        {data: 'order_id'},
        {data: 'order_billing_fName'},
        {data: 'order_billing_lName'},
        {data: 'order_email'},
        {data: 'order_total'},
    ],
    pageLength: 10
});

There are 2 issues.

#1 - In the PHP how do I get the order items and correctly merge that with the current order_data array?

#2 - In jQuery, I think I'll need to add several {data: 'order_item'}, how do I dynamically create the correct amount of these?

Attempt to Merge

I've tried multiple different ways of merging the tables but haven't got it right yet...

    foreach ($orders as $order) {
        $order_data[] = array(
            'order_date' => $order->get_date_created()->format('m-d-y'),
            'order_id' => $order->get_id(),
            'order_billing_fName' => $order->get_billing_first_name(),
            'order_billing_lName' => $order->get_billing_last_name(),
            'order_email' => $order->get_billing_email(),
            'order_total' => $order->get_formatted_order_total(),
        );


        foreach($order->get_items() as $item_id => $item){
            $product = $item->get_product();

            $item_data[] = array(
                'item_name' => $item->get_name(),
                'item_price' => $product->get_price(),
            );

            $merged = array_merge($order_data, $item_data);
        }
    }

Answer 1

Try to rework your array, something like this... It combines order data and item data...

foreach ($orders as $order) {
    $order_data = $order->get_data();
    $items = [];
    foreach ($order->get_items() as $item) {
      $product = wc_get_product( $item->get_product_id() );
      $items[] = [
        'name' => $item->get_name(),
        'sku' => $product ? $product->get_sku() : '', // Handle cases where product might be deleted
        'quantity' => $item->get_quantity(),
        'price' => $item->get_subtotal()
      ];
    }
    $order_data['items'] = $items;  // Add items to the order data
    $data[] = $order_data;
}

Then you can setup the jQuery dataTable like this...

$('#order-table').DataTable({
    "ajax": {
        "url": '/wp-json/wc/v3/orders-with-items',
        "type": "GET",
        "dataSrc": "" // Important:  Tells dataTables the data is directly in the response
    },
    "columns": [
        { "data": "id", "title": "Order ID" },
        { "data": "number", "title": "Order Number" },
        { "data": "date_created", "title": "Date Created" },
        { "data": "total", "title": "Total" },
        { "data": "status", "title": "Status" },
        { "data": "items", "title": "Items", "render": function (data, type, row) {
            let itemsHtml = "<ul>";
            data.forEach(item => {
              itemsHtml += `<li>${item.name} (x${item.quantity}) - ${item.sku} - $${item.price}</li>`;
            });
            itemsHtml += "</ul>";
            return itemsHtml;
          }
        },
        // ... other columns as needed
    ],
    // ... other DataTable options
});