How to write simple Query in magento ?

Retrieving database information in Magento can be a bit complicated. Technically it is possible to write raw SQL statements to load entities and all their associated attribute values. However due to the EAV database design it is difficult to write query statements to get exactly what you want. Utilizing an EAV modeling pattern allows for unlimited attributes on any product, category, customer, or order, but EAV also depletes a programmer’s ability to write queries against the data.

SQL query example:

Lets look at an example of an SQL statement which gets all the products (product_id) in a particular category:

$sql = “SELECT product_id FROM catalog_category_product WHERE category_id=3″;
$data = Mage::getSingleton(‘core/resource’) ->getConnection(‘core_read’)->fetchAll($sql);

You will get the product ids as an array in $data. However if we wanted to get all the attributes of a product based on the product_id then it is difficult as all the product info is not present in one table. Due to the EAV design it is loosely present in many tables. So we require to join a number of tables to get the product information. In such cases we should try using Magento’s built-in query methods instead of directly running queries on the database. More on built in query methods later.

Analyzing the code:

Lets analyze the code:

$data = Mage::getSingleton(‘core/resource’);

First of all we require a connection to the database to perform queries. This is done by using resources. The role of a ‘resource’ in Magento is to manage database connections. So using getSingleton(‘core/resource’) we get an instance of Mage_Core_Model_Resource.

Check out that file and you will see a function called getConnection($name). This is used to get a ‘read’ or ‘write’ connection (‘core_read’ – read connection, ‘core_write’ – write connection).

The fetchAll function executes the query and returns all the rows to $data. The results are returned as an array of results (array of arrays).

How do i know the table names ?

You can see Magento’s database design by checking this link http://www.magentocommerce.com/wiki/development/magento_database_diagram.

The core ResourceModel has a method called getTable() to get the table name of any model in the system. Table names do not have to follow the name of the model, an end-user can change the table names by changing an XML setting.Therefore, it is best to use the getTable method of the core resource.

$res = Mage::getSingleton(’core/resource’)->getConnection(’core_read’);
$tableName = $res->getTable(’catalog/product’);

This will give $tableName as ’catalog_product_entity’. This happens because we have the following XML configuration in the catalog module’s config file.

Mage_Catalog_Model
catalog_resource_eav_mysql4

Mage_Catalog_Model_Resource_Eav_Mysql4

catalog_product_entity

ResourceModels:

ResourceModels manage the different read/write connections and automatically figure out table names based on convention and perform the database related operations for a particular Model. ie. It acts as an abstraction to retrieve (or write) data from database. For eg: in the Mage_Catalog_Model_Product class you can see that to get data it uses the getResource() function. This function gets the ResourceModel to perform the required database operation.

For the Catalog Module the ResourceModels can be found in Catalog\Model\Resource\Eav\Mysql4 directory. (this is defined in the config.xml – search for ). For getting ResourceModels we use the getResourceModel() method.

Mage::getResourceModel(‘catalog/product’) will create an instance of Mage_Catalog_Model_Resource_Eav_Mysql4_Product.php. (If class names have the term Mysql4 in their names, they are generally called ResourceModels. If the word Entity appears in the class name, then the resource is an EAV Entity). You will get the same result by calling Mage::getModel(‘catalog/resource_eav_mysql4_product’) also.

Now take a look at this Product.php file. You will see some sql statements like the one below:

public function getIdBySku($sku)
{return $this->_read->fetchOne(’select entity_id from ‘.$this->getEntityTable().’ where sku=?’, $sku);}

So if we need to get the product id for a sku value then we can simply do it by:

$id = Mage::getResourceModel(‘catalog/product’)->getIdBysku(5);

Its easier to use predefined functions than write your own query statements. Similiarly there are other funtions predefined in the Resource Models that you can use to retrieve data from database.

Entities:

Entities extend Magento’s resource objects. Basically, Entities pair up to selected Models and help them save to the database. Entities behave mostly like resource models, as they are just a special sub-class of resources. Entities for catalog module are in Mage\Catalog\Model\Entity.

Collections:

Entities are designed to load one item, or record, at a time. But, most of the time when we think of database queries we want to write a SELECT statement that gives use a result with multiple rows. The entity models are not able to do that.

What if we want to select all records from the database matching some criteria. Normally, a simple SELECT statement with a WHERE clause would work. But, things are not that simple because of the EAV design. Not all of the data that makes up an entity lives in one table, so we need to JOIN more tables. To properly construct a WHERE clause we would have to know exactly which tables our specific data is stored in. This is the problem that collections solve. Loading an arbitrary number of records based on criteria is the job of entity collection.

Probably the most useful method of a collection is the addAttributeToFilter method. This method takes an attribute code and a condition.

$products = Mage::getModel(’catalog/product’)->getCollection();
$products->addAttributeToFilter(’sku’, ’9999’);
$products->load();

In the above example, the condition is a simple string, but the condition can also be an array. When passing a condition array, the key of the array designates the type of comparison. The type can be eq, for equals, like for like comparisons, gt for a greater than comparison. Here is the same example above, but searching for an array of product IDs.

$products = Mage::getModel(’catalog/product’)->getCollection();
$products->addAttributeToFilter(’entity_id’, array(’in’=> array(1,2,36,35) ));
$products->load();

//runs the query
SELECT * FROM ‘catalog_product_entity‘ WHERE (e.entity_id in (1, 2, 36, 35))
*/

For loading selected attribute values , we can use the addAttributeToSelect method.

This is only scratching the surface of the SQL that you can generate with collections. Look at the Eav/Model/Entity/Collection/Abstract.php file for a full list of methods to manipulate your SQL. Remember that collections are the only way to load entity objects if you need to use a WHERE clause other than querying against the table’s primary key field. When dealing with non-entity models, you can always write raw SQL and run it against a resource connection.

Using Zend to prepare statements:

You may have noticed that in the ResourceModel files magento team does not use plain SQL. Instead they prepare it using Zend_Db This means that Magento is using Zend for handling requests.

$read = Mage::getSingleton(’core/resource’)->getConnection(’core_read’);
echo get_class($read);

You will get the class as Varien_Db_Adapter_Pdo_Mysql which is an extension of Zend_Db_Adapter Abstract. You can create an instance of a Zend_Db_Select object using the select() method of a Zend_Db_Adapter_Abstract object. This can be used to build ‘SELECT’ queries. When building the query, you can add clauses of the query one by one. There is a separate method to add each clause to the Zend_Db_Select object. For example:

To Build this query:
“SELECT product_id, product_name, price FROM “products” WHERE price > 100.00″ //this is not a magento query

We can write:
$select = $read->select()->from(‘products’, array(‘product_id’, ‘product_name’, ‘price’))->where(‘price > 100.00′);

More on how to build ‘SELECT’ statements using Zend here : http://framework.zend.com/manual/en/zend.db.select.html.

Lets look at an example:

What if you want to load all products in a particular category:

$resource = Mage::getSingleton(‘core/resource’);
$read = $resource->getConnection(‘core_read’);
$categoryProductTable=$read->getTableName(‘category/category_product’)
$select = $read->select()->from(array(‘cp’=>$categoryProductTable))->where(‘cp.category_id=?’, ‘3′);
$products=$read->fetchAll($select);
foreach($products as $row)
{
$product = Mage::getModel(‘catalog/product’)->load($row['product_id']);
echo $product->getName();
}