So we're trying to implement a best sellers block on the Magento homepage using this example provided by Inchoo...
Only problem is though, when I dissect the SQL select query, which is the below, the sold_quantity is NULL for every product even though there is a product with one sale in the sales/bestsellers_aggregated_monthly table (for both store id's).
The SQL query is:-
SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`name`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`small_image`, `e`.`thumbnail`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`url_key`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`tax_class_id`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`product_teaser`, `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`, SUM(aggregation.qty_ordered) AS `sold_quantity`, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_flat_1` AS `e` 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 LEFT JOIN `sales_bestsellers_aggregated_monthly` AS `aggregation` ON e.entity_id = aggregation.product_id AND aggregation.store_id=1 AND aggregation.period BETWEEN '2014-11-01' AND '2014-12-01' 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' GROUP BY `e`.`entity_id` ORDER BY `sold_quantity` DESC, `e`.`created_at` ASC
Which I obtained from using the following in the template file:-
<?php $sql = $this->getBestsellerProducts()->getSelect(); ?>
<?php echo $sql; ?>
As you can see, it is meant to sort by sold_quantity in descending order primarily and then by created_at date secondary. Because sold_quantity returns NULL, it just spews out the products in order they were created regardless of any sales.
The function is as follows:-
public function getBestsellerProducts()
{
$storeId = (int) Mage::app()->getStore()->getId();
// Date
$date = new Zend_Date();
$toDate = $date->setDay(1)->getDate()->get('Y-MM-dd');
$fromDate = $date->subMonth(1)->getDate()->get('Y-MM-dd');
$collection = Mage::getResourceModel('catalog/product_collection')
->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
->addStoreFilter()
->addPriceData()
->addTaxPercents()
->addUrlRewrite()
->setPageSize(3);
$collection->getSelect()
->joinLeft(
array('aggregation' => $collection->getResource()->getTable('sales/bestsellers_aggregated_monthly')),
"e.entity_id = aggregation.product_id AND aggregation.store_id={$storeId} AND aggregation.period BETWEEN '{$fromDate}' AND '{$toDate}'",
array('SUM(aggregation.qty_ordered) AS sold_quantity')
)
->group('e.entity_id')
->order(array('sold_quantity DESC', 'e.created_at'));
Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);
return $collection;
}
Presumably, there is something to do with the join array that isn't working properly but I haven't managed to get my head round it yet.
The template file for this block (not that I think it is needed in the question) is as follows:-
<?php $sql = $this->getBestsellerProducts()->getSelect(); ?>
<?php echo $sql; ?>
<table border="0" cellspacing="0">
<tbody>
<?php $counter=0; foreach ($this->getBestsellerProducts() as $product): ?>
<?php if ($counter%2 == 0): ?><tr class="<?php echo $counter%4 ? 'even' : 'odd'; ?>"><?php endif ?>
<td>
<a href="<?php echo $product->getProductUrl() ?>"><img class="product-img" src="<?php echo $this->helper('catalog/image')->init($product, 'small_image')->resize(99); ?>" alt="<?php echo $this->stripTags($this->getImageLabel($product, 'small_image'), null, true) ?>" width="95" border="0" /></a>
<div class="product-description">
<p><a href="<?php echo $product->getProductUrl() ?>"><?php echo $this->stripTags($product->getName(), null, true); ?></a></p>
</div>
</td>
<?php if ($counter++%2): ?></tr><?php endif ?>
<?php endforeach; ?>
</tbody>
</table>
Edit / Answer?
Ah, I've just realised, the Zend_date is set to display BETWEEN 2014-11-01 AND 2014-12-01!?
I tried removing the 'to' and 'from' dates from the join array but this made no difference.
How can I modify the following to specify the date up until current date?
// Date
$date = new Zend_Date();
$toDate = $date->setDay(1)->getDate()->get('Y-MM-dd');
$fromDate = $date->subMonth(1)->getDate()->get('Y-MM-dd');
Edit 2
I've managed to get it to run from the current date (for the past 12 months) modified as:-
$date = new Zend_Date();
// Up until the 1st of the current month
//$toDate = $date->setDay(1)->getDate()->get('Y-MM-dd');
$toDate = $date->getDate()->get('Y-MM-dd');
// The 1st of the previous month
$fromDate = $date->subMonth(12)->getDate()->get('Y-MM-dd');
Yet, the sold_quantity stills returns NULL for every product from the SELECT query.
Edit 3
I've figured it out. It wasn't the date at all, it was because the only sales were associated products, part of a configurable product that were set to 'Not visible individually' and of course, Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection); was only selecting those products that were visible in the catalog.
Will post as answer once I have figured out to associate the sales to the configurable product.
