"A joint field with this alias (bestsellers) is already declared" product sorting Magento

Question

I am facing this issue on product sorting in product list page. I have few new options in sorting like 'Best Sellers','Best Viewed'. When i used this options for sorting , it is giving me an error "A joint field with this alias (bestsellers) is already declared" or "A joint field with this alias (0) is already declared"

I think this is cache issue. When i disabled cache. its start working but when i enabled cache it throw error.

Code used for that is as follows

For bestseller

public function setOrder($collection, $dir)
    {
        $period = (int)Mage::helper('productsorting')->getConfig('bestsellers_period');
        if ($period) {
            $cond = array(
                'created_at' => array(
                    'gteq' => date('Y-m-d h:i:s', time() - $period * 24 * 60 * 60)
                )
            );
        } else {
            $cond = NULL;
        }

        $collection
            ->joinTable('sales/order_item', 'product_id=entity_id',
                array('bestsellers'=>'SUM(sales_flat_order_item.qty_ordered)'), $cond, 'left')
            ->groupByAttribute('entity_id');

        $collection->getSelect()
            ->order('bestsellers');
    }

For bestviewed

public function setOrder($collection, $dir)
    {
        $period = (int)Mage::helper('productsorting')->getConfig('bestviewed_period');
        if ($period) {
            $cond = array(
                'logged_at' => array(
                    'gteq' => date('Y-m-d h:i:s', time() - $period * 24 * 60 * 60)
                )
            );
        } else {
            $cond = NULL;
        }
        $collection
            ->joinTable('reports/event', 'object_id=entity_id',
                array('COUNT(report_event.event_id) AS bestviewed'), $cond, 'left')
            ->groupByAttribute('entity_id');

        $collection->getSelect()
            ->order('bestviewed ' . Mage::helper('productsorting')->reflectDir($dir));
    }

Please help & tell me what i did wrong.

Answer 1

What I've done before to avoid these is already declared errors is check the query string to see if it contains the name of the table I'm joining. I find that depending on where you add your join code, it might be executed two times. Instead of battling with logs and events to figure out the workflow of that action and try moving your code somewhere else, I just check if the select has already been modified. You can check for table or column aliases.

//only do this if it hasn't already been done.
//check if the query string contains the alias 'bestviewed'
if(!strpos((string)$collection->getSelect(), 'bestviewed') > 0) {
    $collection
        ->joinTable('reports/event', 'object_id=entity_id',
            array('COUNT(report_event.event_id) AS bestviewed'), $cond, 'left')
        ->groupByAttribute('entity_id');
}

Answer 2

In your case you did not specify calling your function area. I think if you need to call your setOrder function again and again this problem will occur. If you call once your function the column is joined if you call another time the setOrder function “A joint field with this alias (bestsellers) is already declared" is displayed.

Answer for solution: You can to join the table once (or) you can join the table particular condition is satisfied