Example #1
0
 /**
  * Prepare select query for products in carts report
  *
  * @return $this
  */
 public function prepareForProductsInCarts()
 {
     $productAttrName = $this->_productResource->getAttribute('name');
     $productAttrNameId = (int) $productAttrName->getAttributeId();
     $productAttrNameTable = $productAttrName->getBackend()->getTable();
     $productAttrPrice = $this->_productResource->getAttribute('price');
     $productAttrPriceId = (int) $productAttrPrice->getAttributeId();
     $productAttrPriceTable = $productAttrPrice->getBackend()->getTable();
     $this->getSelect()->useStraightJoin(true)->reset(\Zend_Db_Select::COLUMNS)->joinInner(['quote_items' => $this->getTable('quote_item')], 'quote_items.quote_id = main_table.entity_id', null)->joinInner(['e' => $this->getTable('catalog_product_entity')], 'e.entity_id = quote_items.product_id', null)->joinInner(['product_name' => $productAttrNameTable], 'product_name.entity_id = e.entity_id' . ' AND product_name.attribute_id = ' . $productAttrNameId . ' AND product_name.store_id = ' . \Magento\Store\Model\Store::DEFAULT_STORE_ID, ['name' => 'product_name.value'])->joinInner(['product_price' => $productAttrPriceTable], "product_price.entity_id = e.entity_id AND product_price.attribute_id = {$productAttrPriceId}", ['price' => new \Zend_Db_Expr('product_price.value * main_table.base_to_global_rate')])->joinLeft(['order_items' => new \Zend_Db_Expr(sprintf('(%s)', $this->getOrdersSubSelect()))], 'order_items.product_id = e.entity_id', [])->columns('e.*')->columns(['carts' => new \Zend_Db_Expr('COUNT(quote_items.item_id)')])->columns('order_items.orders')->where('main_table.is_active = ?', 1)->group('quote_items.product_id');
     return $this;
 }
Example #2
0
 /**
  * Separate query for product and order data
  *
  * @param array $productIds
  * @return array
  * @throws \Magento\Framework\Exception\LocalizedException
  */
 protected function getProductData(array $productIds)
 {
     $productConnection = $this->productResource->getConnection('read');
     $productAttrName = $this->productResource->getAttribute('name');
     $productAttrNameId = (int) $productAttrName->getAttributeId();
     $productAttrPrice = $this->productResource->getAttribute('price');
     $productAttrPriceId = (int) $productAttrPrice->getAttributeId();
     $select = clone $this->productResource->getSelect();
     $select->reset();
     $select->from(['main_table' => $this->getTable('catalog_product_entity')])->useStraightJoin(true)->joinInner(['product_name' => $productAttrName->getBackend()->getTable()], 'product_name.entity_id = main_table.entity_id' . ' AND product_name.attribute_id = ' . $productAttrNameId . ' AND product_name.store_id = ' . \Magento\Store\Model\Store::DEFAULT_STORE_ID, ['name' => 'product_name.value'])->joinInner(['product_price' => $productAttrPrice->getBackend()->getTable()], "product_price.entity_id = main_table.entity_id AND product_price.attribute_id = {$productAttrPriceId}", ['price' => new \Zend_Db_Expr('product_price.value')])->where('main_table.entity_id IN (?)', $productIds);
     $productData = $productConnection->fetchAssoc($select);
     return $productData;
 }