General search bar in customer order grid on frontend

Question

We would like to create a general search bar at the customer order grid on frontend. So customers can search within the placed orders for "order_id" and "sku". We already achieved this with "sku", but how can we extend the code so the field will also search for "order_id"?

CODE:

    $post['search'];

    $order->join(
        ["soi" => "sales_order_item"],
        'main_table.entity_id = soi.order_id
            AND
            soi.product_type in ("simple")',
        array('sku')
    )->addFieldToFilter('soi.sku', ['like' => '%'.$post['search'].'%']);

EDIT:

We tried the code below, that's working fine for an order with only 1 SKU. A order with multiple SKU's results in a item with the same ID "" already exists error. Is there a way to only get the first result?

    $order->join(
        ["soi" => "sales_order_item"],
        'main_table.entity_id = soi.order_id
            AND
            soi.product_type in ("simple")',
        array('sku')
    );
    
    // Add condition for searching by SKU and order_id
    $order->addFieldToFilter(
        ['soi.sku', 'main_table.increment_id'],
        [
            ['like' => '%' . $post['search'] . '%'],
            ['like' => '%' . $post['search'] . '%']
        ]
    );

Answer 1

The problem is that the join you are using is making the results to have the same order ID in the list when matching more than one result.

Try adding a grouping condition over the main_table.entity_id so they don't get repeated.

$order->getSelect()->group('main_table.entity_id');

Answer 2

Try this

$order->join(
    ["soi" => "sales_order_item"],
    'main_table.entity_id = soi.order_id
        AND
        soi.product_type in ("simple")',
    array('sku')
);

// Add condition for searching by SKU and order_id
$order->addFieldToFilter(
    ['soi.sku', 'main_table.increment_id'],
    [
        ['like' => '%' . $post['sku'] . '%'],
        ['like' => '%' . $post['order_id'] . '%']
    ]
);