Magento – Models, resource models, and collections

Here we will explain to work with database collection in Magento. As we know that Magento is enrich with MVC (Model – View – Controller). So we will basically starts with some main concepts of models, resource models, and collections.

Also, read:

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 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>Phpzag_Affiliate</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>affiliate_account</table>
</account>
</entities>

As we know that Zend_Db and its related classes provide a simple SQL database interface for Zend Framework. The Zend_Db_Adapter is the basic class you use to connect your PHP application to Mysql database.

Here we will use Zend_Db_Adapter_Pdo_Mysql classes to query the Database

<?php
$db = new Zend_Db_Adapter_Pdo_Mysql(array(
‘host’ => ‘127.0.0.1’,
‘username’ => ‘dbuser’,
‘password’ => ‘xxxxxxxx’,
‘dbname’ => ‘dbname’,
‘profiler’ => true,
));

Returns to the information of queries.

<?php
$profiler = $db->getProfiler();

Zend_Db_Statement

$sql = ‘SELECT * FROM order WHERE 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 Orders extends Zend_Db_Table_Abstract
{
protected $_oname = ‘order’;
}
$table = new Order(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:

This will return the record object in the table

$bugs = new Order();
$row = $bugs->fetchRow($bugs->select()->where(‘order_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
- abstract class Mage_Core_Model_Resource_Abstract
- abstract class Mage_Core_Model_Mysql4_Abstract

The database model and the collection connect to database through database resource layer. The resource class extends from an abstract class:

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’, ‘order_id’);
}

We can also 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.