magento 2.3 get order product id without loop

Question

I need the get the items' product id from certain orders. One way to do it is to simply call the items inside the order loop:

$orderCollection = $this->orderCollectionFactory->create()
    ->addAttributeToFilter($attributeCode, $attribueValue)
;
foreach($orderCollection as $order) {
   $items = $order->getAllVisibleItems();
   foreach($items as $item) {
       $itemIds[] = $item->getProductId();
   }
}

The problem I see using this method is the loop inside another loop. In order to avoid this, I thought about this solution:

$orderCollection = $this->orderCollectionFactory->create()
    ->addAttributeToFilter($attributeCode, $attribueValue)
;
foreach($orderCollection as $order) {
    $itemids = $this->itemsCollectionFactory->create()
        ->addAttributeToFilter('order_id', $order->getId())
        ->getColumnValues('product_id')
    ;
}

but I wonder if getting a second collection is actually better/faster.

Which way is better (two loops vs second collection)?

Is there a third, better way to get just the item ids without the second loop?

Answer 1

I would say that loading a second collection is a better approach than loading objects out of the database in a loop.

There are alternatives that are achieve the same with fewer or even a single query. Whether they are "better" is a matter of perspective. I think that they compromise code clarity, durability and potentially security. They may also result in database queries that introduce their own inefficiencies.

Personally, I would stick with the straightforward collection method, unless you have identified a specific need to do otherwise.

Be aware that all four of these examples result in queries against the sales_order table with no effective filtering (i.e. will return data for every order in the database).

These examples use ->distinct(true) to eliminate duplicate product_ids. This could be ommited and PHP could be used to filter the values.

Option 1 - Replace the second loop with a single query:

$orderCollection = $this->orderCollectionFactory->create()
    ->addAttributeToFilter('entity_id', ['notnull' => true]);
$itemsCollection = $this->itemsCollectionFactory->create()
    ->addAttributeToFilter('order_id', ['in' => $orderCollection->getAllIds()])
$itemsCollection->getSelect()
    ->distinct(true);
$productIds = $itemsCollection->getColumnValues('product_id');

Option 2 - Join sales_order_item to the order collection:

/**
 * After defining your collection
 *
 */
$orderCollection->getSelect()
    ->join(
        ['order_items_table' => 'sales_order_item'],
        'order_items_table.order_id = main_table.entity_id',
        ['product_id' => 'order_items_table.country_id']
    )
    ->reset(\Zend_Db_Select::COLUMNS)
    ->columns(['order_items_table.product_id'])
    ->distinct(true);

Option 3 - Bypass collections and build a query from scratch:

I think that this is inadvisable without a genuine justification, such as a table/join arrangement that collections somehow don't support.

Of particular concern is the potential to introduce SQL injection vulnerabilities that are otherwise avoided when using the collection framework.

Beyond that, they just create more brittle code as a tradeoff for efficiency that I think should be considered purely theoretical unless proven otherwise.

Both examples require dependency injection to make \Magento\Framework\App\ResourceConnection available as $this->resourceConnection

$connection = $this->resourceConnection->getConnection();
$select = $connection->select()
    ->from(
        ['sales_order']
    )
    ->join(
        ['sales_order_item'],
        'sales_order_item.order_id = sales_order.entity_id'
    )
    ->where(new \Zend_Db_Expr('sales_order.entity_id IS NOT NULL'))
    ->reset(\Zend_Db_Select::COLUMNS)
    ->columns(['sales_order_item.product_id'])
    ->distinct(true);
$productIds = array_column($connection->fetchAll($select), 'product_id');

This example includes a placeholder ->where expression to show where your orderCollection filters would need to be implemented.

This approach can further be boiled to with:

$productIds = array_column($resourceConnection
    ->getConnection()
    ->fetchAll('SELECT DISTINCT `sales_order_item`.`product_id` FROM `sales_order` INNER JOIN `sales_order_item` ON sales_order_item.order_id = sales_order.entity_id WHERE (sales_order.entity_id IS NOT NULL)'), 'product_id');

In general, this approach just substitutes brevity for even more of the downsides that come with using raw queries in general.

Option 4 - Use the order collection to create a subquery:

Unlike options 2 and 3, I haven't seen this approach used. Under the right circumstances, it could be used to create custom queries without bypassing the query-building safeguards added by the collection framework.

In this example the order collection is defined but never run against the database. Instead, the query is re-purposed as a subquery used to define the order item collection.

(This uses the original question's collection, but updates the filter to omit variables that are not defined in the example)

$orderCollection = $this->orderCollectionFactory->create()
    ->addAttributeToSelect('entity_id')
    ->addAttributeToFilter('entity_id', ['notnull' => true]) 
    ;
$orderSelect = new \Zend_Db_Expr($orderCollection->getSelect());
$itemsCollection = $this->itemsCollectionFactory->create()
    ->addAttributeToSelect('product_id')
    ->addAttributeToFilter('order_id', ['in' => $orderSelect]);
$itemsCollection->getSelect()
    ->distinct(true);
$productIds = $itemsCollection->getColumnValues('product_id');