2

I am trying to gather the latest 100 items a customer has purchased across all his orders. I know I can use itemCollectionFactory and go order by order, but I was wondering if there is a faster way to get directly all the items, instead of running multiple queries for each order.

With a single query, I'd also be able to make sure the same items are not shown twice in the list.

Thanks!

1

Most effective is probably to use a SQL query with joins. E.g. like this

public function execute()
{
    $customerId = 1;

    $salesTable = $this->resource->getTableName('sales_order');
    $salesItemTable = $this->resource->getTableName('sales_order_item');

    $connection = $this->resource->getConnection();

    $select = $connection->select()
        ->from(
            ['sales_order' => $salesTable],
            []
        )
        ->join(
            ['sales_order_item' => $salesItemTable],
            'sales_order_item.order_id = sales_order.entity_id',
            ['sales_order_item.sku']
        )
        ->where(
            'customer_id = :customerId'
        )
        ->distinct(true)
        ->order('sales_order_item.item_id DESC')
        ->limit(100);
    $bind = ['customerId' => $customerId];

    $items = $connection->fetchAll($select, $bind);

    print_r($items);

}

Where $this->resource is \Magento\Framework\App\ResourceConnection

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.