1

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?

5
  • why you don't use order item collection?
    – Amit Bera
    May 31, 2021 at 16:08
  • As Amit suggest, use the order item collection, it has the order_id and product_id fields you can filter by.
    – David Tay
    Jun 1, 2021 at 0:13
  • If you already have the order ids, you can use them to create an order item collection in one step. But since the first step in the question's code loads orders based on an attribute filter, it seems likely that order ids are not initially known. Once you have the IDs, using them to create a second collection is preferable to using them to load orders in a loop.
    – jiheison
    Jun 1, 2021 at 2:11
  • @AmitBera thank you for your response. If you look at the code I've posted, you can see that I am already using the order item collection. My problem is that I first need to get the order ids first, that's the reason why there is the double loop. The double loop is the thing I'm trying to avoid (if it can be done without losing performance, otherwise I will stick to the double loop)
    – Lou Nik
    Jun 3, 2021 at 13:18
  • @jiheison thank you, that was my doubt: which makes more sense, to load the item collection inside the order loop or to start another loop inside the order loop to get the order item ids
    – Lou Nik
    Jun 3, 2021 at 13:21

1 Answer 1

2

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');
2
  • thank you, this is very helpful indeed! I think I'll go with the second collection, as it seems the most straightforward and less lines of code, but thank you for the detailed explation, sure helps to better understand how things works
    – Lou Nik
    Jun 3, 2021 at 13:24
  • Glad that the answer was useful. Option 4 only occurred to me after describing the first three, and I think I will find use for it in the future.
    – jiheison
    Jun 3, 2021 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.