Topic 4 – Part 1: Models, resource models, and collections

We are going to release a new topic which is intended to spread understanding about working with database in Magento. Through this topic, your can enrich your knowledge of the Model element in the MVC (Model – View – Controller). This article today will basically starts with some main concepts of models, resource models, and collections.

• Describe basic concepts of models, resource models, and collections

A “model” is used to store data, and perhaps performs some business logics against that data.
A “resource model” is used to interact with the database (or maybe other types of persistent data) on behalf of the “model”. The “resource model” actually performs the CRUD operations.
A “collection model” holds from one to many “models” and knows how to tell the “resource model” to get rows in the basis of information it is given.

There’s a basic ActiveRecord-like/one-object-one-table Model, and there’s also an Entity Attribute Value (EAV) Model.

• Configure a database connection

<resources>
            <affiliateplus_setup>
                <setup>
                    <module>Magestore_Affiliateplus</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </affiliateplus_setup>
            <affiliateplus_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </affiliateplus_write>
            <affiliateplus_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </affiliateplus_read>
        </resources>

• Create and register new entities

<entities>
<account>
		<table>affiliateplus_account</table>
	</account>
  </entities>

• Use the Zend_Db classes to query the Database

• Connect to database by 1 adapter

$db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => '127.0.0.1',
    'username' => 'webuser',
    'password' => 'xxxxxxxx',
    'dbname'   => 'test',
	 ‘profiler’ => true,
));

• Returns to the information of queries.

$profiler = $db->getProfiler();

– getTotalElapsedSecs() returns the total number of seconds elapsed for all profiled queries.
– getQueryProfiles() returns an array of all query profiles.
– getLastQueryProfile() returns the last (most recent) query profile, regardless of whether or not the query has finished (if it hasn’t, the end time will be NULL)
– clear() clears any past query profiles from the stack.
• Zend_Db_Statement

$sql = 'SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?';
$stmt = new Zend_Db_Statement_Mysqli($db, $sql);

• Zend_Db_Table: Each class interacts with one table in the database, and you need to declare the database table for which this class define.
Example:

class Bugs extends Zend_Db_Table_Abstract
{
    protected $_name = 'bugs';
}
$table = new Bugs(array('db' => $db));

With $table object, you can use some methods to operate with the database such as: insert, update, delete.

• Zend_Db_Table_Row: is a record object in the table

$bugs = new Bugs();
$row = $bugs->fetchRow($bugs->select()->where('bug_id = ?', 1));
$rowArray = $row->toArray();

• Zend_Db_Select:

$select = $db->select()
    ->from( ...specify table and columns... )
    ->where( ...specify search criteria... )
    ->order( ...specify sorting criteria... );

• Database collection in Magento

The collection in Magento usually extends from class Mage_Core_Model_Resource_ Collection_Abstract or Mage_Core_Model_Mysql4_ Collection_Abstract. The collection has some methods for you to filter, sort and specify the selected values:
• addFieldToFilter(,): used to filter data
• setOrder(): used to sort data
• getSelect(): returns the selected query (is instance object of class Varien_Db_Select) to this collections. And you are able to use it to add specific selected value.
• Database resource
The database model and the collection connect to database through database resource layer. The resource class extends from an abstract class:

• abstract class Mage_Core_Model_Resource_Abstract

abstract class Mage_Core_Model_Mysql4_Abstract

In this class, you need to declare your database table and the id field of this table.
For example:

public function _construct(){
	$this->_init('affiliate/program', 'program_id');
}

• Use and resolve existing table names without hard coding them

$resource = Mage::getSingleton('core/resource');
$eavAttributeTable = $resource->getTable('eav/attribute');

“eav/attribute” here is your configuration for table eav_entity_attribute in your database.

Well, that’s all for today. Hope you can find it useful. Our next parts will dig more deeply into how to work with database based on these definitions. Nice day 😉