I am having a complex SQL query to make a custom report & showing same to custom report grid but somehow not able to convert the query into Magento's equivalent Collection.

SELECT SUM(order_items.qty_ordered) AS `ordered_qty`, `order_items`.`name` AS `order_items_name`, `order_items`.`product_id` AS `entity_id`, `e`.`entity_type_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`sku`, `order`.`created_at`, `order`.`updated_at`, `ps`.`qty` AS `current_stock`, (ps.qty - SUM(order_items.qty_ordered)) AS `difrnc`, `c`.`value` as `costing`, ((ps.qty - SUM(order_items.qty_ordered)) * `c`.`value`) AS `totcsttostock` 
         FROM `sales_flat_order_item` AS `order_items` 
         INNER JOIN `sales_flat_order` AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state <> 'canceled' 
         LEFT JOIN `catalog_product_entity` AS `e` ON (e.type_id NOT IN ('grouped', 'configurable', 'bundle')) AND e.entity_id = order_items.product_id AND e.entity_type_id = 4 
         LEFT JOIN `cataloginventory_stock_item` AS `ps` ON ps.product_id=order_items.product_id 
         LEFT JOIN `catalog_product_entity_decimal` AS `c` ON c.entity_id=order_items.product_id and c.attribute_id=79 
         WHERE `order`.`created_at` BETWEEN '2017-09-22 00:00:00' AND '2017-09-22 23:59:59' and (parent_item_id IS NULL) 
         GROUP BY `order_items`.`product_id` HAVING (SUM(order_items.qty_ordered) > 0) ORDER BY `ordered_qty` desc

I have converted this much(which might have mistakes), but needed help/suggestion ahead

function __construct() {
    $this->_init('sales/order_item','item_id'); }
public function setDateRange($from, $to) {
             ->joinInner(array('i' => $this->getTable('sales/order_item')),'i.order_id = main_table.entity_id','SUM(i.qty_ordered) AS ordered_qty')
->joinLeft(array('ps' => $this->getTable('cataloginventory/stock_item')), 'ps.product_id=i.product_id', 'ps.qty as current_stock, (ps.qty - SUM(i.qty_ordered)) AS difrnc')
->joinLeft(array('e' => $this->getTable('catalog/product')), '(e.type_id NOT IN ("grouped", "configurable", "bundle")) AND e.entity_id = i.product_id AND e.entity_type_id = 4')
->joinLeft(array('c' => $this->getTableName('catalog/product','decimal')), 'c.entity_id=i.product_id and c.attribute_id=79', 'c.value as costing, ((ps.qty - SUM(i.qty_ordered)) * c.value) AS totcsttostock')
             ->where('i.parent_item_id is null')
             ->where("i.created_at BETWEEN '".$from."' AND '".$to."'")
             ->where('main_table.state = \'complete\'')
             ->columns(array('ordered_qty' => 'count(distinct main_table.entity_id)'));
             echo $this->getSelect();
        return $this;

Please, can someone help or suggest anything?

Your Answer

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

Browse other questions tagged or ask your own question.