Magento 1.9 SQL Join – Best Sellers Block for Monthly Bestseller Aggregation

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.