Get customer orders excluding ones with a specific order item

Question

I want to retrieve all customer orders' except the ones that have a specific item (walle product)

Currently, I get all orders that have the item and then filter them from orderCollection

$orderItemCollection = $this->orderItemCollectionFactory->create();
$orderItemCollection->addFieldToFilter('sku', Data::WALLET_PRODUCT_SKU);

$walletOrders = [];
foreach ($orderItemCollection->getItems() as $orderItem) {
    $walletOrders[] = $orderItem->getOrderId();
}
$orderCollection->addFieldToFilter('entity_id', ['nin' => $walletOrders]);

Is there a better way to do this without getting order IDs first?

Answer 1

just use Collections and JOIN them to filter the orders based on the ordered skus (order items)

use \Magento\Sales\Model\ResourceModel\Order\CollectionFactory;
use \Magento\Framework\App\ResourceConnection;

//Inject them to your Constructor

/* @var \Magento\Framework\App\ResourceConnection $resource */
$resourceConnection = $this->resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);

$salesOrderItem = $resourceConnection->getTableName('sales_order_item');

/* @var \Magento\Sales\Model\ResourceModel\Order\CollectionFactory $orderCollectionFactory */
$_orderCollection = $this->orderCollectionFactory->create();

$_orderCollection ->getSelect()->joinLeft(
    ['order_item' => $salesOrderItem],
    'main_table.entity_id=order_item.order_id',
    ['order_item.sku']
)
->where('order_item.sku != ?', Data::WALLET_PRODUCT_SKU); // filter orders don't have this SKU 

return $_orderCollection; // loop through the collection to get each orders