4

So I have this resource model that I pulled from some admin code on the dashboard. It works, but I can't seem to join to it. Doing ->getSelect()->__toString(); shows nothing? So is this not a normal collection? Why does it not work like other collections?

My guess that in order to accomplish what I want to accomplish is that I am going to have to extend the collection model, but I just wanted to see if I can get more of an idea of what is going on here.

$storeId = $this->helper('core')->getStoreId();
$collection = Mage::getResourceModel('sales/report_bestsellers_collection')
    ->setModel('catalog/product')
    ->addStoreFilter($storeId);

$collection->getSelect()->join(
        array('category_product' => 'catalog_category_product'),
        'main_table.product_id = category_product.product_id', 
        array('category_product.*')
    )->where('category_product.category_id = ?', '3')
     ->order('main_table.qty_ordered DESC')
     ->limit($limit);
9

The reporting collections work a bit differently that the Mage_Core_Model_Abstract/CRUD collections. The CRUD collections will have a SQL query assigned immediately at instantiation. The reporting collection, however, initializes its SQL statement during its load operation. You can see this in the load method down in the abstract base class for all reporting collections.

#File: app/code/core/Mage/Reports/Model/Resource/Report/Collection/Abstract.php
public function load($printQuery = false, $logQuery = false)
{
    if ($this->isLoaded()) {
        return $this;
    }
    $this->_initSelect();
    if ($this->_applyFilters) {
        $this->_applyDateRangeFilter();
        $this->_applyStoresFilter();
        $this->_applyCustomFilter();
    }
    return parent::load($printQuery, $logQuery);
}

There's no select until _initSelect is called.

The general approach you'll want to take here is

  1. Create a new resource model class which extends the Mage_Reports_Model_Resource_Report_Collection_Abstract class

  2. Define a _applyCustomFilter method in your class

and then use your new resource model. Your _applyCustomFilter method would look something like this.

protected function _applyCustomFilter()
{
    $return = parent::_applyCustomFilter();
    //manipulate the select all you want here (`$this->getSelect()`)
    return $return;
}
1

Looks like you're trying to load a list of "best selling" products (and filtered by a given category), I've done it in the past using the product collection from the Reports module.

Give this a shot:

$category = Mage::getModel('catalog/category')->load(3);
$report = Mage::getResourceModel('reports/product_collection')
        ->addAttributeToSelect('*')
        ->addOrderedQty()
        ->setOrder('ordered_qty', 'desc')
        ->addCategoryFilter($category);
  • I wanted to avoid this method because I thought it would be too heavy, and preferred the already aggregated table over it thaet is populated by a cron job. I mean is that query above going to be heavy if I put a limit 30 on it? I guess it could be further investigated by me, but for now I'm going to stick with the bestsellers aggregate table due to lack of time to investigate – Bryan Ruiz Mar 22 '13 at 21:50

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.