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;
}
}