EAV database for Magento (1.9) in practice (frontend)

The EAV (Entity Attribute Value) data model, as its name implies, is used for separated storing of entities, attributes and their values.



Under Magento it means the following:

  • entity table (defines entity)
  • attribute table (defines attribute, its type and other options, e.g. source model, type, input, label)
  • value table (it is separated according to types, partly because of indexability and searches, e.g. catalog_product_entity_int, catalog_product_entity_varchar etc.)

 

The attributes could be stored in the main table, couldn’t they?

In the EAV data model, while adding new attributes to the entities, there is no need to modify the original table. So too large tables can be avoided, thus the speed of queries will be faster, and also unnecessary data with lists will not appear, only those that we add to the collection.

Here, we are going to create a module, with both admin and frontend interfaces, using an EAV solution.

 

Having installed the online store on the server and after configuring it (base url, database name, database user stb.), and if everything shows right, we start the development.

 

For defining the module, we need to create an xml file named with the module’s name in the app/etc/modules directory. In our case it is Aion_Items.xml, the codePool is local, but it could also be placed in the community codePool.

 

The xml looks like this:

 

<?xml version="1.0"?>
<config>
    <modules>
        <Aion_Items>
            <active>true</active>
            <codePool>local</codePool>
            <depends>
                <Mage_Core/>
            </depends>
        </Aion_Items>
    </modules>
</config>

 

This is not yet finished. The module will not appear even after emptying the cache, not even in the module list in the admin panel.

Next we should create the module defined in the xml, within the directory that was set in the codePool. Here, in this case, it means the local directory (if it does not yet exist) under the app/code folder, and within it an Aion/Items directory is to be found.

So the final path in this case is: app/code/local/Aion/Items.

 

module_path

 

In the following steps to come, the general rules of module development are to be applied with some minor differences. If you have forgotten some details, it is completely acceptable to look up the content of Magento core modules for some help : )

Create the module config.xml file, step by step:

 

<?xml version="1.0" encoding="UTF-8" ?>
<config>
    <modules>
        <Aion_Items>
            <version>0.0.1</version>
        </Aion_Items>
    </modules>
</config>

 

Define the paths of the models and resource models, and also the entity table(s) for the module. Insert the following elements before the </config> node and right after the </modules> node:

 

<global>
    <models>
        <aion_items>
            <class>Aion_Items_Model</class>
            <resourceModel>aion_items_resource</resourceModel>
        </aion_items>
        <aion_items_resource>
            <class>Aion_Items_Model_Resource</class>
            <entities>
                <article>
                    <table>aion_items_article</table>
                </article>

 

We haven’t yet closed the <entities> node, we keep “staying with” it and define the other EAV data tables, type by type:

 

            <article_datetime>
                <table>aion_items_article_datetime</table>
            </article_datetime>
            <article_decimal>
                <table>aion_items_article_decimal</table>
            </article_decimal>
            <article_int>
                <table>aion_items_article_int</table>
            </article_int>
            <article_text>
                <table>aion_items_article_text</table>
            </article_text>
            <article_varchar>
                <table>aion_items_article_varchar</table>
            </article_varchar>
            <article_char>
                <table>aion_items_article_char</table>
            </article_char>
        </entities>
    </aion_items_resource>
</models>

 

Now we define the paths of helpers and blocks:

 

<helpers>
    <aion_items>
        <class>Aion_Items_Helper</class>
    </aion_items>
</helpers>
<blocks>
    <aion_items>
        <class>Aion_Items_Block</class>
    </aion_items>
</blocks>

 

Now, to have our tables created, we need to set the resources, which means defining the installer and database connection. It can be inserted below </blocks>, but it is important to have them on the same level.

 

        <resources>
            <aion_items_setup>
                <setup>
                    <module>Aion_Items</module>
                    <class>Aion_Items_Model_Resource_Setup</class>
                </setup>
            </aion_items_setup>
        </resources>
    </global>
</config>

 

As you can see, we have closed the nodes, next we will create the basic helper (it is very important (!), your module will not work without it) and the models. They need to be created in the location defined in the xml. The models are created in the Aion/Items/Model directory, while the helper in the Aion/Items/Helper directory.

 

The default helper belonging to the module is named Data in every case, the Mage::getHelper(‘aion_items’) tries to create this, which is equal to the Mage::getHelper(‘aion_items/data’) call.

Thus our helper has the class name Aion_Items_Helper_Data, and file name Data and is derived from the Mage_Core_Helper_Abstract class.

 

Let’s create the models and resource models.

We defined the article under <entities>, so let’s make it our model, let’s create the

  • Aion_Items_Model_Article
  • Aion_Items_Model_Resource_Article
  • Aion_Items_Model_Resource_Article_Collection classes.

 

Since we have chosen the EAV data model, we do not derive them from the usual Mage_Core_Model_Resource_Abstract and Mage_Core_Model_Resource_Db_Collection_Abstract classes, but, based on the order of the aforementioned elements, from the following classes:

  • Mage_Eav_Model_Entity_Abstract
  • Mage_Eav_Model_Entity_Collection_Abstract

There is still a small change in the resource model:

 

class Aion_Items_Model_Resource_Article extends Mage_Eav_Model_Entity_Abstract
{
const ENTITY = ‘aion_items_article’;

public function __construct()
{
    $this->setType(self::ENTITY)
        ->setConnection(‘core_read’, ‘core_write’);
}
}

 

The first model was omitted from the list deliberately (Aion_Items_Model_Article). It is still derived from the Mage_Core_Model_Abstract class, so there is no change. When the models have been created, the installer class needs to be created. This has been defined in the basic configuration file of the module (Aion_Items_Model_Resource_Setup). Let’s create it in the appropriate location, with the following content:

 

class Aion_Items_Model_Resource_Setup extends Mage_Eav_Model_Entity_Setup
{
    public function getDefaultEntities()
    {
        $entityAttributes = array(
            Aion_Items_Model_Resource_Article::ENTITY => array(
                'entity_model' => 'aion_items/article',
                'attribute_model' => '',
                'table' => 'aion_items/article',
                'attributes' => array(
                    'name' => array(
                        'type' => 'varchar',
                        'label' => 'Name',
                        'input' => 'text',
                        'global' => 0,
                        //this set the attribute to store entity data separated to every store not globally
                        'visible' => true,
                        'required' => true,
                        'user_defined' => true,
                        'visible_on_front' => true
                    ),
                    'customer_id' => array(
                        'type' => 'integer',
                        'label' => 'Customer',
                        'input' => 'select',
                        'global' => 1,
                        'visible' => false,
                        'required' => true,
                        'user_defined' => true,
                        'visible_on_front' => false,
                        'source' => 'aion_items/article_attribute_source_customer'
                    ),
                ),
            )
        );

        return $entityAttributes;
    }
}

 

These attributes will be created for the entity type when the installer is run. There are other possibilities apart from the attribute options mentioned above, but since these are not different from the default values, there is no need to define them.

 

We can see it at the customer_id attribute that we included the source option, so we will need a source model. Create the Customer.php file in the defined location and with the defined name in the app/code/local/Aion/Items/Model/Article/Attribute/Source directory, with the following content:

 

class Aion_Items_Model_Article_Attribute_Source_Customer extends Mage_Eav_Model_Entity_Attribute_Source_Table
{
    public function getAllOptions()
    {
        if (!$this->_options) {
            $customers = Mage::getResourceModel('customer/customer_collection')
                ->addAttributeToSort('lastname', 'ASC')
                ->addAttributeToSort('firstname', 'ASC');
            foreach ($customers as $customer) {
                $name = [$customer->getFirstname(), $customer->getLastname()];
                $this->_options[] = ['value' => $customer->getId(), 'label' => join(' ', $name)];
            }
        }
        return $this->_options;
    }
}

 

In order to create our table, there is only the installer left to be created. It should be done in the directory defined in the <resources> node, within the sql located under our module.

Since in this case it is <aion_items_setup>, therefore the directory will be aion_items_setup as well.

 

We have given the version, in <version> node, earlier in the config.xml file of our module. This will be the version number of our installer. Now, create an install-0.0.1.php file in the app/code/local/Aion/Items/sql/aion_items_setup directory, with the following content:

 

try {
    $this->startSetup();
    $this->createEntityTables('aion_items/article');
    $this->addEntityType(Aion_Items_Model_Resource_Article::ENTITY, [
        'entity_model' => 'aion_items/article',
        'attribute_model' => '',
        'table' => 'aion_items/article',
        'increment_model' => '',
        'increment_per_store' => '0'
    ]);

    $this->installEntities();
    $this->endSetup();
} catch (Exception $e) {
    Mage::logException($e);
}

 

When done, the module is ready for install. But before running it, take a look at the directory and file structure:

 

files_in_module_1

 

 

In order to make our module’s installer run, empty cache in the admin panel under System – > Cache management. If the installer has been run successfully, we are to see the following tables apart from the basic tables in the database.

 

eav_tables

 

 

Not to miss a thing, switch on error log in the admin area before running the installer. Also, check the var/log directory whether they contain exception.log or system.log files. Additionally, we can see if the customer_id and name attributes have been created in the eav_attribute table with a descending order based on the attribute_id. We can also see, next to it in the entity_id field, what ID number has been given to the new entity type. You can make it visible by clicking on it.

All done right? Then let’s make a frontend page for our module to test if everything works fine. Insert the following code section under <global> node, but before </config> node:

 

<frontend>
    <routers>
        <aion_items>
            <use>standard</use>
            <args>
                <module>Aion_Items</module>
                <frontName>aionitems</frontName>
            </args>
        </aion_items>
    </routers>
</frontend>

 

For frontend display, we also need layout, controller, block and template files. Insert the layout file definition under the same <frontend> node:

 

<layout>
    <updates>
        <aion_items>
            <file>aion/items.xml</file>
        </aion_items>
    </updates>
</layout>

 

Next, create the layout xml file here: app/design/frontend/rwd/default/layout. It is important to always create the basic template files, belonging to our module, in the layout directory under default belonging to the theme, because in this case any later modifications will affect its duplicate (copy) and not the original file. The same applies to template files.

 

<?xml version="1.0"?>
<layout version="0.1.0">
    <aion_items_index_index translate="label">
        <label>Aion Items</label>
        <reference name="root">
            <action method="setTemplate"><template>page/1column.phtml</template></action>
        </reference>
        <reference name="content">
            <block type="aion_items/list" name="aion.items.list" as="aion_items_list" template="aion/items/index.phtml"/>
        </reference>
    </aion_items_index_index>
</layout>

 

Now let’s get to the template file and the block. We have indicated aion/items/index.phtml as the template file in the xml, thus we place the index.phtml file one level higher in the app/design/frontend/rwd/default/template/aion/items directory. Then we place a List.php file in the app/code/local/Acion/Items/Block directory with the following content:

 

class Aion_Items_Block_List extends Mage_Core_Block_Template
{
    /**
     * @return \Aion_Items_Model_Resource_Article_Collection
     * @throws \Mage_Core_Exception
     */
    public function getArticleCollection()
    {
        $articles = Mage::getModel('aion_items/article')->getCollection()
            ->addAttributeToSelect('*');

        return $articles;
    }
}

 

Now let’s return to the index.phtml template file and, temporarily, make its content the following:

 

/** @var Aion_Items_Block_List $this */
$articles = $this->getArticleCollection();
Zend_Debug::dump($articles->getData(),'articles');

 

To have the content of the collection displayed, we only need a controller. Create the IndexController.php file in the app/code/local/Aion/Items/controllers directory with the following content:

 

class Aion_Items_IndexController extends Mage_Core_Controller_Front_Action
{
    /**
     * @return $this
     */
    public function indexAction()
    {
        $this->loadLayout();
        $this->renderLayout();

        return $this;
    }
}

 

Next, empty cache again in the admin panel and call the url belonging to the module on frontend (in the example this is /aionitems/ which is equal to an /aionitems/index/index/ call. If everything was set right, the following (or something similar) should appear:

 

frontend_display

 

 

So far so good.

But in fact nothing is yet displayed, as no data is included in the table. To make our example look good, write a data installer to see if our attributes work fine.

In one of the previous images there was a data directory whose role is exactly this. Non-system related modifications can be carried out through this. Structurally, it is connected to the sql directory, therefore we need an aion_items_setup sub-directory within it.

There is one more difference in terms of operation compared to a “plain” installer script: the files located under such structure are searched by Magento with a data- prefix.

Create a data-upgrade-0.0.1-0.0.2.php file in the app/code/local/Aion/Items/data/aion_items_setup directory (as the install-0.0.1.php has already been run) and also create an upgrade-0.0.1-0.0.2.php file in the app/code/local/Aion/Items/sql/ directory. In the latter we only indicate what happens and where it happens. Thus the upgrade-0.0.1-0.0.2.php contains the following:

 

/** @var Aion_Items_Model_Resource_Setup $this */
try {
    $this->startSetup();
    //app\code\local\Aion\Items\data\aion_items_setup\data-upgrade-0.0.1-0.0.2.php
    $this->endSetup();
} catch (Exception $e) {
    Mage::logException($e);
}

 

The app/code/local/Aion/Items/data/aion_items_setup/data-upgrade-0.0.1-0.0.2.php includes the following:

 

/** @var Aion_Items_Model_Resource_Setup $this */
try {
    $defaultStoreId = Mage::app()->getDefaultStoreView()->getId();
    $attributeSet = Mage::getResourceModel('eav/entity_attribute_set_collection')
        ->addFieldToFilter(
            'entity_type_id',
            Mage::getModel('eav/entity')
                ->setType(Aion_Items_Model_Resource_Article::ENTITY)
                ->getTypeId()
        )->addFieldToFilter(
            'attribute_set_name',
            'Default'
        )->getFirstItem();
    $articles = [
        [
            'store_id' => $defaultStoreId,
            'name' => 'Article name 1',
            'attribute_set_id' => $attributeSet->getId(),
        ],
        [
            'store_id' => $defaultStoreId,
            'name' => 'Article name 2',
            'attribute_set_id' => $attributeSet->getId(),
        ]
    ];
    foreach ($articles as $article) {
        Mage::getModel('aion_items/article')->setData($article)->save();
    }
} catch (Exception $e) {
    Mage::logException($e);
}

 

We haven’t finished yet.

After updating, the new installer does not run, so, again, emptying cache is needed. Additionally, increase 0.0.1, in the config.xml file belonging to the module, under <version> node, to 0.0.2, and then update the page. If everything has been working OK, you’ll see this in the databse:

 

data_installed

 

 

In the aion_items_article_varchar table the names are seen separately associated to the different entities.

 

data_eav_installed

 

 

After updating, we see the following on frontend:

 

frontend_display_data

 

 

Summary

I think I’m not very much mistaken if I say that EAV is a very useful and sophisticated solution which is not needed in most of the cases.

Most mid and small range developments do not require such a structural build since they include more, interconnected tables originally.

It is recommended to make it clear beforehand what characteristics our database management will have and what kind of resources it may use in the future, as it is possible to store these data in a generated, cumulative “flat” table—just the same as Magento does it with data organized in the same structure.

It would be quite time consuming and costly to reorganize existing tables with complex structures and large numbers into an EAV structure.

 

István Dombi

István Dombi

Backend Developer

István is a Senior Magento Developer. He is not afraid of challenges, even if they mean problems that others have already failed to solve. He is not particularly specialized in any given field of Magento, he just likes diving headlong into tasks that need a complex way of thinking. When not sitting in front of the computer screen, he happily spends his free time fishing.


NEED A RELIABLE, PROFESSIONAL MAGENTO DEVELOPMENT PARTNER?

Contact us if you have any question or requirement related to the preparation of a new or renewal of an existing online store.

magento_request
Do you need our support?
  • Magento Site Check
  • Magento Code Audit
  • Magento SEO Audit
  • Magento Project Rescue
Request help
Next