Get collection query string

Question

I'm using the following code to get some data from a table.

    $collection = Mage::getModel('bannerslider/bannerslider')->getCollection()
        ->addFieldToFilter('status',1)
        ->addFieldToFilter('is_home',$this->_display)
    ->addOrder('position', 'ASC')
        ;

Just for my curiosity, I want to check the query that is executed here and I'm echo using this code

$collection->printLogQuery(true);

var_dump((string)$collection->getSelect());

Now, my problem is that the string

SELECT `main_table`.* FROM `bannerslider` AS `main_table` WHERE (status = '1') AND (is_home = '0')

is not showing my last condition, addOrder but the collection is really ordered by position field, I checked that.

What I don't understand is why the order condition is not visible in the query. Thank you.

score 13 · Accepted Answer · answered Feb 09 '12 at 11:39

The reason your order isn't showing is because the orders are added to the query during the load() method.
See Varien_Data_Collection_Db::load()

public function load($printQuery = false, $logQuery = false)
{
    // ... removed for brevity

    $this->_renderFilters()
         ->_renderOrders()
         ->_renderLimit();

    $this->printLogQuery($printQuery, $logQuery);
    $data = $this->getData();

    // ... removed for brevity
}

If you would call $collection->load(true) you would see the SQL containing the order by clause.

score 4 · Answer 2 · answered Feb 09 '12 at 08:58

Working with collection try next things:

$collection->setOrder('position', 'ASC'); // main order setter
$collectioon->getSelect()->order('position asc'); // alternative order setter

$collection->load(); // some times you need to call load() to be sure your collection don't get changes later in some place
echo $collection->getSelect(); // to print query

Hope it helps

score 1 · Answer 3 · answered Feb 09 '12 at 08:13

1

You could try using the addAtributeToSort() method like this :

$collection = Mage::getModel('bannerslider/bannerslider')->getCollection()
    ->addFieldToFilter('status',1)
    ->addFieldToFilter('is_home',$this->_display)
    ->addAtributeToSort('position', 'ASC');

This works on Magento CE 1.5.1, I hope it will for you.

answered Feb 09 '12 at 08:13

1

The method `addAttributeToSort()` is only available on EAV based collections extending `Mage_Eav_Model_Entity_Collection_Abstract`. Since the query in the question is referring to attributes that must be part of the main table, it very probably is not an EAV based entity. – Vinai Feb 09 '12 at 11:35
in addition to what @Vinai said, that Collection is not part on EAV entity. It's from a module with it's own table. – Ovidiu Feb 10 '12 at 16:31

Get collection query string

3 Answers3