Skip to content

Magento 2 Get order collection using mysql query group by sku

i am trying to get order collection group by sku using sql query. this query is working in my local system but not in my live site. it returns – Uncaught TypeError: Cannot read properties of undefined (reading ‘select’)

Following is my code

<?php
namespace VendorExtensionModelResourceModelCustomreport;

class Collection extends MagentoReportsModelResourceModelOrderCollection
{
    public function setDateRange($from, $to)
    {

        $this->_reset()->addAttributeToSelect('*');

        $this->getSelect()
             ->join(
                 ['order_items' => $this->getTable('sales_order_item')],
                 'main_table.entity_id = order_items.order_id',
                 [
                     'sku',
                     'qty_sold' => new Zend_Db_Expr('SUM(order_items.qty_ordered)'),
                     'total_sale_value' => new Zend_Db_Expr('SUM(order_items.qty_ordered * order_items.price)'),
                     'final_sale_price' => 'order_items.price'
                 ]
             )
             ->joinLeft(
                 ['order' => $this->getTable('sales_order')],
                 'order_items.order_id = order.entity_id',
                 ['created_at' => new Zend_Db_Expr('DATE(order.created_at)')]
             )
             ->joinLeft(
                 ['product' => $this->getTable('catalog_product_entity_decimal')],
                 'order_items.product_id = product.entity_id AND product.attribute_id = (SELECT attribute_id FROM ' . $this->getTable('eav_attribute') . ' WHERE attribute_code = "price" AND entity_type_id = (SELECT entity_type_id FROM ' . $this->getTable('eav_entity_type') . ' WHERE entity_type_code = "catalog_product"))',
                 ['mrp' => 'product.value']
             )
             ->where("order_items.created_at BETWEEN '".$from."' AND '".$to."'")
             ->group(['order_items.sku', 'DATE(order.created_at)']);

        return $this;
    }

    public function setStoreIds($storeIds)
    {
        return $this;
    }

    public function setOrder($attribute, $dir = self::SORT_ORDER_DESC)
    {
        if (in_array($attribute, ['orders', 'ordered_qty'])) {
            $this->getSelect()->order($attribute . ' ' . $dir);
        } else {
            parent::setOrder($attribute, $dir);
        }

        return $this;
    }
}