StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
12838492
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
3
CommunityOwnedDate
CreationDate
2012-10-11T11:30:02.303
FavoriteCount
0
LastActivityDate
2012-10-18T17:16:42.667
LastEditDate
2012-10-18T17:16:42.667
LastEditorUserId
927404
OwnerUserId
927404
ParentId
12836608
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
You're using a lot of objects wrappers in your sample script. Methods like <code>load</code> encapsulated into multiple loops will produce huge delays and possibly huge memory usage (base on your product collection size). When I addressed this issue the other day I decided to use straight ORM methods instead of objects for better performance. There are two possible ways to show bestsellers. The less resources-consuming is to use aggregated bestsellers tables (like <code>sales_bestsellers_aggregated_daily</code>), but it has huge drawback - the data inside those tables is not updating authomatically. It's used in Admin Reports section, and will be updates only when you choose to refresh your statistics. The other but much more reliable method is to join <code>sales_flat_order_item</code> table to retrieve sales_qty for each product. Obviously it's more resource consuming, since you have to count it yourself. In my script I've chosen the latter path. I've modified it to meet your logic requirements. Also I've added a couple of <code>joins</code> to get Category name, you might not need that. But enough talking :) here's the code of my <code>test.php</code> shell script: <pre><code><?php require_once 'abstract.php'; /** * Magento Test Bestsellers script * * @category Mage * @package Mage_Shell */ class Mage_Shell_Test extends Mage_Shell_Abstract { /** * Run script * */ public function run() { // benchmarking $memory = memory_get_usage(); $time = microtime(); echo "Starting mem usage: $memory\n"; $catId = $this->getArg('category'); /** @var $collection Mage_Catalog_Model_Resource_Product_Collection */ $collection = Mage::getResourceModel('catalog/product_collection'); // join sales order items column and count sold products $expression = new Zend_Db_Expr("SUM(oi.qty_ordered)"); $condition = new Zend_Db_Expr("e.entity_id = oi.product_id AND oi.parent_item_id IS NULL"); $collection->addAttributeToSelect('name')->getSelect() ->join(array('oi' => $collection->getTable('sales/order_item')), $condition, array('sales_count' => $expression)) ->group('e.entity_id') ->order('sales_count' . ' ' . 'desc'); // join category $condition = new Zend_Db_Expr("e.entity_id = ccp.product_id"); $condition2 = new Zend_Db_Expr("c.entity_id = ccp.category_id"); $collection->getSelect()->join(array('ccp' => $collection->getTable('catalog/category_product')), $condition, array())->join(array('c' => $collection->getTable('catalog/category')), $condition2, array('cat_id' => 'c.entity_id')); $condition = new Zend_Db_Expr("c.entity_id = cv.entity_id AND ea.attribute_id = cv.attribute_id"); // cutting corners here by hardcoding 3 as Category Entiry_type_id $condition2 = new Zend_Db_Expr("ea.entity_type_id = 3 AND ea.attribute_code = 'name'"); $collection->getSelect()->join(array('ea' => $collection->getTable('eav/attribute')), $condition2, array())->join(array('cv' => $collection->getTable('catalog/category') . '_varchar'), $condition, array('cat_name' => 'cv.value')); // if Category filter is on if ($catId) { $collection->getSelect()->where('c.entity_id = ?', $catId)->limit(1); } // unfortunately I cound not come up with the sql query that could grab only 1 bestseller for each category // so all sorting work lays on php $result = array(); foreach ($collection as $product) { /** @var $product Mage_Catalog_Model_Product */ if (isset($result[$product->getCatId()])) { continue; } $result[$product->getCatId()] = 'Category:' . $product->getCatName() . '; Product:' . $product->getName() . '; Sold Times:'. $product->getSalesCount(); } print_r($result); // benchmarking $memory2 = memory_get_usage(); $time2 = microtime(); $memDiff = ($memory2 - $memory)/1000000; $timeDiff = $time2 - $time; echo 'Time spent:' . $timeDiff . "s\n"; echo "Ending mem usage: $memory2\n"; echo "Mem used : {$memDiff}M\n"; } /** * Retrieve Usage Help Message * */ public function usageHelp() { return <<<USAGE Usage: php -f test.php -- [options] php -f test.php -- --category 1 --categories <category> Filter by Category, if not specified, all categories are outputted help This help USAGE; } } $shell = new Mage_Shell_Test(); $shell->run(); </code></pre> To use it just create a file <code>test.php</code> in your shell folder and insert the code I've provided into the file. See <code>usageHelp</code> if you're not familiar with command line php calls. P.S. Added some benchmarking there to track your mem_usage and time. UPDATE Upon reviewing the issue further I've found more elegant way to get the Bestsellers for each category using only <code>Zend_Db</code> adapter. The result will contain only <code>category_id</code> => <code>product_id</code> connection (not Magento Objects), but it's much easier and overall better. This code should go between the benchmarking blocks into the <code>run</code> function: <pre><code> $catId = $this->getArg('category'); /** @var $resource Mage_Core_Model_Resource */ $resource = Mage::getModel('core/resource'); /** @var $adapter Zend_Db_Adapter_Abstract */ $adapter = $resource->getConnection('core_read'); $select = $adapter->select() ->from(array('c' => $resource->getTableName('catalog/category')), array('cat_id'=>'entity_id')) ->join(array('ccp' => $resource->getTableName('catalog/category_product')), 'c.entity_id = ccp.category_id', array()) ->join(array('oi' => $resource->getTableName('sales/order_item')), 'ccp.product_id = oi.product_id', array('max_qty' => new Zend_Db_Expr('SUM(oi.qty_ordered - oi.qty_canceled)'), 'product_id' => 'product_id')) ->where('oi.parent_item_id is null') ->group('c.entity_id') ->group('oi.product_id') ->order('entity_id ASC') ->order('max_qty DESC'); if ($catId) { $select->where('c.entity_id = ?', $catId); } $res = $adapter->fetchAll($select); $result = array(); foreach ($res as $oneRes) { if (isset($result[$oneRes['cat_id']])) { continue; } $result[$oneRes['cat_id']] = $oneRes; } array_walk($result, function($var, $key) { echo 'Category Id:' . $key . ' | Product Id:' . $var['product_id'] . ' | Sales Count:' . $var['max_qty'] . "\n"; }); </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POShowing bestseller of each category in navigation in Magento 1.7
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USSlayer Birden
UserOwnerUserId
1. USSlayer Birden
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.