Get collection query string

Question

I use 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 request that runs here, and I echo using this code

$collection->printLogQuery(true);

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

Now my problem is that the line

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

My last condition, addOrder, does not show, but the collection is really ordered by position field, I checked it.

I do not understand why the order condition is not displayed in the request. Thank.

Answer 1

The reason your order is not displayed is because orders are added to the request during the load () method.
Cm.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 call $collection->load(true), you will see SQL containing the order by clause.

Answer 2

:

$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

,

Answer 3

addAtributeToSort() :

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

Magento CE 1.5.1, , .