The Problem:

You need a custom Model to interact with a database.

The Solution:

By now you’ve read all the previous posts, right, and you want to take your custom module to the next level.  You not only have business logic to perform, but you need to read and write data to a custom database table.  No problem, let’s take a look at Magento’s database layer.

There’s a lot of voodoo that goes into the Database layer, most of the confusion stems from a heavy reliance on naming conventions.  Once you understand these conventions you’ll have a much easier time getting data in and out of your database.

As always I’m going to use an example.  Let’s say that you need to create a Widget Model that stores Widgets into a database table.  We’ll need a Model to describe our Widget, a Class for the data layer, and the appropriate config files to let Magento know about our customizations.

I’m going to start with the Widget Model since it’s already been covered and should be the most familiar.

/app/code/local/Spinonesolutions/Helloworld/Model/Widget.php:

class Spinonesolutions_Helloworld_Model_Widget extends Mage_Core_Model_Abstract {
  function __construct() {
    parent::__construct();
    $this->_init("spinonesolutions_helloworld/widget");
  }

  public function save() {
    $this->setCreated(now());
    $this->setUpdated(now());

    parent::save();
    return $this;
  }
}

There are a couple of important things to make note of in the Class above.  First, I’ve extended Mage_Core_Model_Abstract, this is Magento’s basic Data Layer Class and it will provide the functions that we need to interact with the database.

Second, the _init() function’s parameter should match your config.xml, as we’ll see in just a moment.  Lastly, I’ve put a little bit of business logic in the save() method.  As we’ll see this sets the created, and modified fields in our table to the current datetime when we call save().

/app/code/local/Spinonesolutions/etc/config.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<config>
  <modules>
    <Spinonesolutions_Helloworld>
      <version>0.1.0</version>
    </Spinonesolutions_Helloworld>
  </modules>
  <frontend>
    <routers>
      <spinonesolutions> <!-- I make this match my front name but I'm not sure it matters -->
        <use>standard</use> <!-- Use standard routing as opposed to admin. IE: frontend vs admin -->
        <args>
          <module>Spinonesolutions_Helloworld</module> <!-- The module to search for controllers -->
          <frontName>spinonesolutions</frontName> <!-- The first descriminator in the path. "spinonesolutions" in http://localhost/spinonesolutions/ -->
        </args>
      </spinonesolutions>
    </routers>
  </frontend>
  <global>
    <models>
      <spinonesolutions_helloworld> <!-- This is used when istanting your Model EG: Mage::getModel("spinonesolutions_helloworld/hellworld") -->
        <class>Spinonesolutions_Helloworld_Model</class> <!-- That class to use when isntanting objects of type above. -->
        <resourceModel>spinonesolutions_helloworld_mysql4</resourceModel> <!-- Use a custom table instead of EAV, the Model is defomed below -->
      </spinonesolutions_helloworld>
      <spinonesolutions_helloworld_mysql4> <!-- Define a new Model to represent the Data Layer -->
        <class>Spinonesolutions_Helloworld_Model_Mysql4</class>
        <entities>
          <widget><table>widget</table></widget> <!-- map a table (widget) to an entity "widget" -->
        </entities>
      </spinonesolutions_helloworld_mysql4>
      <paygate>
        <rewrite>
          <authorizenet>Imagistic_General_Model_Paygate_Authorizenet</authorizenet>
        </rewrite>
      </paygate>
    </models>
    <resources>
     <spinonesolutions_helloworld_write>
       <connection><use>core_write</use></connection>
     </spinonesolutions_helloworld_write>
     <spinonesolutions_helloworld_read>
       <connection><use>core_read</use></connection>
     </spinonesolutions_helloworld_read>
   </resources>
  </global>
</config>

Let’s start at the top and work our way down.  Note: I’m going to skip the custom Model stuff and just focus on what’s new for this post.

<spinonesolutions_helloworld> <!-- This is used when istanting your Model EG: Mage::getModel("spinonesolutions_helloworld/hellworld") -->
  <class>Spinonesolutions_Helloworld_Model</class> <!-- That class to use when isntanting objects of type above. -->
  <resourceModel>spinonesolutions_helloworld_mysql4</resourceModel> <!-- Use a custom table instead of EAV, the Model is defomed below -->
</spinonesolutions_helloworld>

We just mapped a new resource Model (spinonesolutions_helloworld_model_mysql4) to spinonesolutions_helloworld.  You’ll notice that the Model itself is defined right after this line.

<spinonesolutions_helloworld_mysql4> <!-- Define a new Model to represent the Data Layer -->
  <class>Spinonesolutions_Helloworld_Model_Mysql4</class>
  <entities>
    <widget><table>widget</table></widget> <!-- map a table (widget) to an entity "widget" -->
  </entities>
</spinonesolutions_helloworld_mysql4>

Just as we did for our custom Model before, we’ve now defined a new Model (spinonesolutions_helloworld_mysql4) and told Magento what its class is (Spinonesolutions_Helloworld_Model_Mysql4).  Notice that we’ve already used this new Model as the resourceModel for spinonesolutions_helloworld.

As well as linking this Model to its Class we’ve also mapped a table to it in the entities section.  Now the “widget” entity maps to the table “widget” in our magento database.

We need one more file; we’ve mapped spinonesolutions_helloworld_mysql4 to the class Spinonesolutions_Helloworld_Model_Mysql4 so we need to create a file to house this code.

/app/code/local/Spinonesolutions/Helloworld/Model/Mysql4/Widget.php:

class Spinonesolutions_Helloworld_Model_Mysql4_Widget extends Mage_Core_Model_Mysql4_Abstract {
  public function _construct() {
    $this->_init("spinonesolutions_helloworld/widget", "id");
  }
}

Throughout this tutorial I’ve been following Magento’s naming conventions for Class definitions and file names.

The first parameter passed to the _init() function is the Model that defines our table, and the second parameter is the name of the PrimaryKey field.

Last but not least we have the “resources” section of the config.xml.  The read and write sections tell Magento what connection to use for read operations and write operations.  As long as you keep true to the naming conventions you shouldn’t need to change these values too much.

Let’s put it all together and do a test.  I’m going to create a table “widgets”:

widgets
id (int) – PK
title (varchar255)
description (varchar255)
created(datetime)
modified(datetime)

Now we’ll create a controller to manage CRUD for our new Model.  I’ll call it WidgetController.

/app/code/local/Spinonesolutions/Helloworld/controllers/WidgetController.php

class Spinonesolutions_Helloworld_WidgetController extends Mage_Core_Controller_Front_Action {
  //indexAction is the default Action for any controller
  function indexAction() {
    echo "Spinonesolutions_Helloworld_WidgetController.indexAction()";
  }

  //{base}/spinonesolutions/widget/create/
  function createAction() {
    echo "Spinonesolutions_Helloworld_WidgetController.createAction()";
    $widget = Mage::getModel("spinonesolutions_helloworld/widget");
    $data = array(
      "title" => "My new title",
      "description" => "My new description"
    );

    $widget->setData($data);
    $widget->save();
  }
}

Requesting {base}/spinonesolutions/widget/create/ results in the insertion of a new record into my widget table!  Let’s deconstruct what I’ve done a little bit to gain some understanding.

You’ll recognize the instantiation of the Model from my previous posts on custom Modules and Models.  Magento stores all of an object’s data in an array which is stored in a private variable _data.  Each of the array elements corresponds to a field in the table.  All Models derived from the abstract classes have getters and setters for their variables.  You can always call them by using the “get” and “set” prefix followed by camel case variable name.

So if the field in my database table is named “foo_bar”.  In the object, the value will be stored in _data[“foo_bar”].  I can set the value by calling $object->setFooBar(“value”).  I can get the value by calling $object->getFooBar();

That’s all for this post!  I’ll go over widget CRUD in the next!

Download the source – Helloworld_v4

1. Part I : Custom Module
2. Part II : Models
3. Part III : Controllers
4. Part IV : Extending Models
5. Part V : Data Layer

Data Layer, Magento, Model