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.

Answer 1

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.

Answer 2

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