6

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.

5

In the end we were able to render the best sellers blocks from simple (child) products in sales/bestsellers_aggregated_monthly.

    $collection = Mage::getResourceModel('catalog/product_collection');
    $collection->getSelect()
        ->reset()
        ->from(array('aggregation' => $collection->getResource()->getTable('sales/bestsellers_aggregated_monthly')), null)
        ->joinLeft(
            array('pr' => $collection->getResource()->getTable('catalog/product_relation')),
            'aggregation.product_id = pr.child_id',
            array('SUM(aggregation.qty_ordered) AS sold_quantity')
        )
        ->joinLeft(
            array($collection::MAIN_TABLE_ALIAS => $collection->isEnabledFlat() ? $collection->getEntity()->getFlatTableName() : $this->getEntity()->getEntityTable()),
            'e.entity_id = pr.parent_id',
            array('entity_id', 'type_id', 'attribute_set_id')
        )
        ->where("aggregation.store_id={$storeId} AND aggregation.period BETWEEN '{$fromDate}' AND '{$toDate}'")
        ->group('e.entity_id')
        ->order(array('sold_quantity DESC', 'e.created_at'));

    $collection
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addCategoryFilter($category)
        ->addStoreFilter()
        ->addPriceData()
        ->addTaxPercents()
        ->addUrlRewrite()
        ->setPageSize(3);

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


    return $collection;

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.