Magento Accessing SQL Database

Question

I am working on a module for Magento which involves taking the total price for every order submitted and storing it in an SQL table. My first instinct would be to use raw SQL commands to do so, but according to most of the Magento articles I have read so far on the topic, it seems I am supposed to ues Magento/Zend classes to do this instead.

How would I access the SQL database with Magento classes and what are the advantages of doing so instead of just using raw SQL commands?

All help is greatly appreciated and I always accept an answer!

Answer 1

The answer on question "why is usage of ORM/ActiveRecord better than raw SQL" is the same as for "Why do i need to use MVC or programming patterns?". It's a bit philosophical questions, so a lot of answers on them you can find using Google.

You must create business and resource models. Also you need to assign resource model to business one using config.xml file in you module etc folder and also in _construct methods of each model's classes.

For example, you have a business class with My_Module_Model_Bussines and resource class My_Module_Model_Resource_Bussines. The smallest realization (and must be) of these classes are

First One:

class My_Module_Model_Business extends Mage_Core_Model_Abstract
{

    protected function _construct()
    {
        $this->_init('my_module/business');
    }
}

Second:

class My_Module_Model_Resource_Business extends Mage_Core_Model_Mysql4_Abstract
{
    protected function _construct()
    {
        $this->_init('my_module/table', 'id');
    }
}

where the first parameter of method _init in each classes is an xpath in XML configuration, second one (in the resource class) is primary key name.

Also in config.xml you must have something like this:

<?xml version="1.0"?>
<config>
    <modules>
        <My_Module>
            <version>YOUR_VERSION_OF_MODULE(e.g. 0.0.1)</version>
        </My_Module>
    </modules>
    <global>
        <models>
            <my_module>
                <class>My_Module_Model</class>
                <!-- name of the resource model node -->
                <resourceModel>my_module_resource</resourceModel>
            </my_module>
            <my_module_resource>
                <class>My_Module_Model_Resource</class>
                <entities>
                    <table>
                        <table>your_table_name</table>
                    </table>
                </entities>
            </my_module_resource>
        </models>
    <!-- another config nodes -->
    </global>
</config>

After that you may use something like this:

Mage::getModel('my_module/business')->setFirstField('some data')
   ->setTotal('100.1')
   ->setOrderId(10)
   ->save();

If you use newer version of Magento than 1.5.1 you will need to change Mysql4 in class names to Resource