In Magento sometimes we are required to write advanced operations in model and collection, to execute complex sql queries.

The model and collection architecture in magento is like, a magento model class it extends Mage_Core_Model_Abstract which intern extends Varien_Object.

SELECT QUERY
Let us write a select query,

<?php
class Namespace_Test_Model_Mysql_Test extends Mage_Core_Model_Mysql_Abstract
{
    public function _construct()
    {   
        $this->_init('test/test', 'test_id');
    }
    public function loadByField($field,$value){
        $table = $this->getMainTable();
        $where = $this->_getReadAdapter()->quoteInto("$field = ?", $value);
        $select = $this->_getReadAdapter()->select()->from($table,array('test_id'))->where($where);
        $id = $this->_getReadAdapter()->fetchOne($sql);
        return $id;
    }
}

Here, we are assuming that we have an resource file Namespace_Test_Model_Mysql_Test, now if we look in above code we have loadByField() method in which we are loading the table first and then we have the where condition set after defining the parameters the select query is written as :

$select = $this->_getReadAdapter()->select()->from($table,array('test_id'))->where($where);

we have used those parameters in this query to complete the SELECT query.

The model file Namespace_Test_Model_Test

<?php
class Namespace_Test_Model_Test extends Mage_Core_Model_Abstract
{
    public function _construct()
    {
        parent::_construct();
        $this->_init('test/test');
    }
    public function loadByField($field,$value){
        $id = $this->getResource()->loadByField($field,$value);
        $this->load($id);
    }
}

If we notice mostly we have used many functions to perform some operations like getMainTable() to load table name etc. let us check them one by one :

getTable() function : Returns the Table name.

getReadAdapter() function :  Returns an object which contains the function which perform the database query, like fetch(), fetchAll(), query(), etc.

_quoteInfo() function : Used to create our where condition.

from() function : Used to write the table name from which the database operations to be performed like from($table).