0

I am trying to list the Bestselling Products using the code below however it is giving error instead of listing the products, where am I doing wrong?

<?php
class Mage_Catalog_Block_Product_Bestseller extends Mage_Catalog_Block_Product_List
{
    function get_prod_count()
    {
        //unset any saved limits
        Mage::getSingleton('catalog/session')->unsLimitPage();
        return (isset($_REQUEST['limit'])) ? intval($_REQUEST['limit']) : 24;
    }

    function get_cur_page()
    {
        return (isset($_REQUEST['p'])) ? intval($_REQUEST['p']) : 1;
    }


    protected function _getProductCollection()
    {
        $collection = Mage::getResourceModel('reports/product_collection');

        $order = (isset($_REQUEST['order'])) ? $_REQUEST['order'] : "ordered_qty";
        $dir = (isset($_REQUEST['dir'])) ? $_REQUEST['dir'] : "desc";

        $collection = $this->_addProductAttributesAndPrices($collection)
            ->addStoreFilter()
            ->addOrderedQty()
            ->addAttributeToSelect('*')
            ->addAttributeToSelect(array('name', 'price', 'small_image'))
            ->addAttributeToSort($order, $dir)
            ->setPageSize($this->get_prod_count());

        $collection->setCurPage($this->get_cur_page());
        $this->setProductCollection($collection);

        Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
        Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);
        Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection($collection);

        return $collection;
    }
}

This is the error:

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`, `e`.`has_options`, `e`.`required_options`, `e`.`created_at`, `e`.`updated_at`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`cost`, `e`.`created_at`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`image_label`, `e`.`is_recurring`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `e`.`fc_sales`, `e`.`fc_views`, `e`.`name`, `e`.`price`, `e`.`small_image`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `at_inventory_in_stock`.`is_in_stock` AS `inventory_in_stock` 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
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '2'
 INNER JOIN `cataloginventory_stock_item` AS `at_inventory_in_stock` ON (at_inventory_in_stock.`product_id`=e.entity_id) AND ((at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=1 AND at_inventory_in_stock.is_in_stock=1) OR (at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=0) OR (at_inventory_in_stock.use_config_manage_stock = 1 AND at_inventory_in_stock.is_in_stock=1)) WHERE (parent_item_id IS NULL) GROUP BY `order_items`.`product_id` HAVING (SUM(order_items.qty_ordered) > 0) LIMIT 24

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.cost' in 'field list', query was: 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`, `e`.`has_options`, `e`.`required_options`, `e`.`created_at`, `e`.`updated_at`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`cost`, `e`.`created_at`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`image_label`, `e`.`is_recurring`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `e`.`fc_sales`, `e`.`fc_views`, `e`.`name`, `e`.`price`, `e`.`small_image`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `at_inventory_in_stock`.`is_in_stock` AS `inventory_in_stock` 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
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '2'
 INNER JOIN `cataloginventory_stock_item` AS `at_inventory_in_stock` ON (at_inventory_in_stock.`product_id`=e.entity_id) AND ((at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=1 AND at_inventory_in_stock.is_in_stock=1) OR (at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=0) OR (at_inventory_in_stock.use_config_manage_stock = 1 AND at_inventory_in_stock.is_in_stock=1)) WHERE (parent_item_id IS NULL) GROUP BY `order_items`.`product_id` HAVING (SUM(order_items.qty_ordered) > 0) LIMIT 24

#0 C:\xampp2\htdocs\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:\xampp2\htdocs\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 C:\xampp2\htdocs\lib\Zend\Db\Adapter\Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 C:\xampp2\htdocs\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT SUM(orde...', Array)
#4 C:\xampp2\htdocs\lib\Varien\Db\Adapter\Pdo\Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT SUM(orde...', Array)
#5 C:\xampp2\htdocs\lib\Zend\Db\Adapter\Abstract.php(737): Varien_Db_Adapter_Pdo_Mysql->query('SELECT SUM(orde...', Array)
#6 C:\xampp2\htdocs\lib\Varien\Data\Collection\Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT SUM(orde...', Array)
#7 C:\xampp2\htdocs\app\code\core\Mage\Eav\Model\Entity\Collection\Abstract.php(1045): Varien_Data_Collection_Db->_fetchAll('SELECT SUM(orde...')
#8 C:\xampp2\htdocs\app\code\core\Mage\Eav\Model\Entity\Collection\Abstract.php(871): Mage_Eav_Model_Entity_Collection_Abstract->_loadEntities(false, false)
#9 C:\xampp2\htdocs\app\code\core\Mage\Review\Model\Observer.php(78): Mage_Eav_Model_Entity_Collection_Abstract->load()
#10 C:\xampp2\htdocs\app\code\core\Mage\Core\Model\App.php(1338): Mage_Review_Model_Observer->catalogBlockProductCollectionBeforeToHtml(Object(Varien_Event_Observer))
#11 C:\xampp2\htdocs\app\code\core\Mage\Core\Model\App.php(1311): Mage_Core_Model_App->_callObserverMethod(Object(Mage_Review_Model_Observer), 'catalogBlockPro...', Object(Varien_Event_Observer))
#12 C:\xampp2\htdocs\app\Mage.php(448): Mage_Core_Model_App->dispatchEvent('catalog_block_p...', Array)
#13 C:\xampp2\htdocs\app\code\core\Mage\Catalog\Block\Product\List.php(164): Mage::dispatchEvent('catalog_block_p...', Array)
#14 C:\xampp2\htdocs\app\code\core\Mage\Core\Block\Abstract.php(918): Mage_Catalog_Block_Product_List->_beforeToHtml()
#15 C:\xampp2\htdocs\app\code\core\Mage\Core\Block\Text\List.php(43): Mage_Core_Block_Abstract->toHtml()
#16 C:\xampp2\htdocs\app\code\core\Mage\Core\Block\Abstract.php(919): Mage_Core_Block_Text_List->_toHtml()
#17 C:\xampp2\htdocs\app\code\core\Mage\Core\Block\Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#18 C:\xampp2\htdocs\app\code\core\Mage\Core\Block\Abstract.php(581): Mage_Core_Block_Abstract->_getChildHtml('content', true)
#19 C:\xampp2\htdocs\app\design\frontend\ultimo\default\template\page\1column.phtml(53): Mage_Core_Block_Abstract->getChildHtml('content')
#20 C:\xampp2\htdocs\app\code\core\Mage\Core\Block\Template.php(241): include('C:\\xampp2\\htdoc...')
#21 C:\xampp2\htdocs\app\code\core\Mage\Core\Block\Template.php(272): Mage_Core_Block_Template->fetchView('frontend\\ultimo...')
#22 C:\xampp2\htdocs\app\code\core\Mage\Core\Block\Template.php(286): Mage_Core_Block_Template->renderView()
#23 C:\xampp2\htdocs\app\code\core\Mage\Core\Block\Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#24 C:\xampp2\htdocs\app\code\core\Mage\Core\Model\Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#25 C:\xampp2\htdocs\app\code\core\Mage\Core\Controller\Varien\Action.php(390): Mage_Core_Model_Layout->getOutput()
#26 C:\xampp2\htdocs\app\code\core\Mage\Cms\Helper\Page.php(137): Mage_Core_Controller_Varien_Action->renderLayout()
#27 C:\xampp2\htdocs\app\code\core\Mage\Cms\Helper\Page.php(52): Mage_Cms_Helper_Page->_renderPage(Object(Mage_Cms_PageController), '16')
#28 C:\xampp2\htdocs\app\code\core\Mage\Cms\controllers\PageController.php(45): Mage_Cms_Helper_Page->renderPage(Object(Mage_Cms_PageController), '16')
#29 C:\xampp2\htdocs\app\code\core\Mage\Core\Controller\Varien\Action.php(418): Mage_Cms_PageController->viewAction()
#30 C:\xampp2\htdocs\app\code\core\Mage\Core\Controller\Varien\Router\Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('view')
#31 C:\xampp2\htdocs\app\code\core\Mage\Core\Controller\Varien\Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#32 C:\xampp2\htdocs\app\code\core\Mage\Core\Model\App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#33 C:\xampp2\htdocs\app\Mage.php(684): Mage_Core_Model_App->run(Array)
#34 C:\xampp2\htdocs\index.php(117): Mage::run('', 'store')
#35 {main}
  • What error are you getting? – Melvyn Mar 24 '15 at 18:25
  • @Melvyn I posted the error. – tufanayd Mar 24 '15 at 18:30
1

I don't think the code you posted is responsible for the SQL error you've posted. I'm saying this because I don't see a reference to the field cost in Mage_Reports_Model_Resource_Product_Collection or in the entire Mage_Reports_Model directory. Correct me if I'm wrong.

As for the SQL error, it is happening because column e.cost does not exist. The alias e refers to catalog_product_entity, which does not have this column.

I would check the collection where you're setting cost with addAttributeToSelect and remove cost it possibly.

  • It's not just cost, but a bunch of attributes starting with cost. Looks like the price index table is joined without the correct alias. There is a joinPrice* method inside the catalog module, look into that. That would be inside _addProductAttributesAndPrices inside your code. – Melvyn Mar 25 '15 at 8:18
  • @Melvyn, is this a third-party extension? – musicliftsme Mar 25 '15 at 13:28
  • No, look at OP's code. – Melvyn Mar 25 '15 at 18:58
  • Have you tried looking at Mage_Catalog_Block_Product_Bestseller in your CE 1.9.1? Because I don't see it. – musicliftsme Mar 25 '15 at 19:02
1

Since comments is a bit too short for this, I'll explain:

@tufanayd has created the file app/code/core/Mage/Catalog/Block/Product/Bestseller.php to create a bestseller block. This isn't standard procedure, as you'd put that in your own extension, but sometimes, especially with blocks, it's convenient to create a file in the core code to quickly check if something can be done, so you don't have to deal with setting up the module, referencing the layout XML etc.

The code for this block is listed in the question. He's using the collection reports/product_collection, which extends the catalog/product_collection and thus can be fed to Mage_Catalog_Block_Product_Abstract::_addProductAttributesAndPrices.

However, since this collection already is a join and _addProductAttributesAndPrices() doesn't allow you to specify join aliases, the join that is added in Mage_Catalog_Model_Resource_Product_Collection::_productLimitationPrice uses the already consumed join alias 'e'. The screw up may actually be in Mage_Core_Model_Resource_Helper_Mysql4::prepareColumnsList, which cleans up columns, but it's hard to tell without tracing the code, cause there's a lot of query string manipulation going on in this code.

Either way, in theory this should work, because Mage_Catalog_Model_Resource_Product_Collection::MAIN_TABLE_ALIAS sets the alias for the product table to 'e', and the reports collection sets the productEntityTableName correctly in it's constructor. I also do not see any references to the flat tables, or is_child, so there should be no mixing of flat tables and EAV here.

In short, I've given you a few pointers to dig into, but I don't see a magic flag / change in your code to make this work. Maybe someone else feels compelled to dig into it or has done it before :)

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.