Module 4. Working with Database. EAV Explained

  • Database Overview
  • Detailed Workflow
    • $model->load()
    • $model->_beforeLoad()
    • $resourceModel->load()
    • $model->_afterLoad()
    • $model->afterLoad()
    • $model->save()
    • $resourceModel->save()
    • $model->isDeleted([$flag])
    • $model->validateBeforeSave()
    • $model->beforeSave()
    • $model->isSaveAllowed()
    • $model->afterSave()
    • $model->afterCommitCallback()
    • $model->delete()
    • $model->beforeDelete()
    • $model->afterDelete()
    • $model->afterDeleteCommit()
  • Setup Scripts and Resources
  • EAV Concepts
  • EAV Specifics in Load and Save
  • Attribute Management
    • $eavSetup->addAttribute($entityTypeId, $code, $attr)
    • $eavSetup->updateAttribute($entityTypeId, $id, $field, $value, $sortOrder)

Database Overview

Database layer in Magento 2 is very similar to one Magento 1.x had.

As model is just an object representation of a data entity, it’s not necessarily a part of the data layer. To make it storable, a developer should extend a model from \Magento\Framework\Model\AbstractModel or one of its descendants. That will provide basic functionality to handle data model fields, primary key field, as well as a mechanism to declare a resource model. There is no need anymore to define resource models in config, an appropriate resource model is simply initiated in the model’s _construct() protected method. Thus, developers should write something like this:

 /**

 * Initialize resource

 *

 * @return void

 */

protected function _construct()

{

    $this->_init(‘Magento\Catalog\Model\ResourceModel\Product\Link’);

}

Similarly as in Magento 1.x, \Magento\Framework\Model\AbstractModel provides $this->_eventPrefix / $this->_eventObject mechanism that is used to fire corresponding CRUD-related events. It also provides a domain-level validation (as opposed to the data structure validation, performed in resource model.

 

Below there is a diagram showing relationship between different elements of the data model layer, resource model and resource collection.

In Magento 2, there is exactly one resource model and one collection defined for every model.

It’s not a data model responsibility to know how to persist itself, it only goes as far as declare a resource model that is responsible for performing CRUD operations over the data stored in the model. Even though there are load(), save() and delete() methods, they are only functional through the declared resource model. As in Magento 1.x, when any of CRUD methods is called, the data model passes itself as a data container and delegates CRUD operations to the resource model along, which is using low level commands of resource adapter to perform them.

 

Therefore, the business logic is decoupled from the storage layer logic, and the storage schema is decoupled from the database driver implementation. 

All resource models that work with the DB storage layer are extended from \Magento\Framework\Model\ResourceModel\Db\AbstractDb. Resource models are responsible for the following:

  • processing CRUD operations (load/save/delete)
  • implementing all necessary additional logic that is needed to manipulate the storage data for the model
  • mapping object properties to the database fields and vice versa.

Unlike Magento 1.x, Magento 2 resource models provide a single entry point to access the storage adapter, getConnection(). Magento 2 Enterprise provides a module ResourceConnection that implements features that allow to utilize DB master/slave mode for better load balancing, performance and security.

Resource collection represents a list of model instances of specific type. All resource collections are extended from \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection and also can perform their internal logic through the resource adapter. Resource collections provide the following:

  • implementing EncapsulatedCollection pattern
  • implementing IterationAggregate, that allows to iterate the collection
  • representing entity groups
  • containing logic for group operations, such as filtering, sorting, paging.

Resource adapter used in all DB resource models and DB collections implements \Magento\Framework\DB\Adapter\AdapterInterface. By default, it’s \Magento\Framework\DB\Adapter\Pdo\Mysql (it also extends Zend_Db_Adapter_Pdo_Mysql).

 

There are a number of Model Type interfaces declared across Magento. Model Type interfaces make it easier for developers to work with the application without knowing low level details. Type interfaces define specific setters and getters, as well as allow using “magic” getters and setters implemented in \Magento\Framework\DataObject.

Detailed Workflow

$model->load()

  • requires a first argument as an ID to retrieve a record and map it to the model instance
  • a loaded instance is always returned, if loading fails, an exception is being thrown
  • an optional second attribute allows to specify the name of the field that should be used as fetching ID when loading

Categories and products, as EAV entities, can be loaded by an alternative attribute using loadByAttribute() method.

Anatomy of the load() method:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

/**

 * Load object data

 *

 * @param integer $modelId

 * @param null|string $field

 * @return $this

 */

public function load($modelId, $field = null)

{

    $this->_beforeLoad($modelId, $field);

    $this->_getResource()->load($this, $modelId, $field);

    $this->_afterLoad();

    $this->setOrigData();

    $this->_hasDataChanges = false;

    $this->updateStoredData();

    return $this;

}

$model->_beforeLoad()

  • dispatches model_load_before
  • dispatches $model->_eventPrefix . ‘_load_before’
  • possible use case: checking ACL to decide if reading from DB is appropriate
  • this WILL not be dispatched when loading models in a collection

$resourceModel->load()

  • dispatches load() request to the data mapper layer
  • $resourceModel->_getLoadSelect() uses ID and selection criterion

$model->_afterLoad()

  • dispatches model_load_after
  • dispatches $model->_eventPrefix . ‘_load_after’
  • possible use case: joining two domain items; for example, products and inventory objects
  • this WILL not be dispatched when loading models in a collection

$model->afterLoad()

  • this public method is provided for using in collections that load data outside of the $model->load() workflow. This is done for performance optimization.

$model->save()

  • Inserts or updates a database record mapped with the model properties
  • Determines the presence of a record by checking on an existence of a primary key
  • Composite key entities are best handled by adding an auto increment primary key

$resourceModel->save()

  • dispatches save() request to the data mapper layer

$model->isDeleted([$flag])

  • checks if an instance is marked for deletion, sets a deletion flag if specified

$model->validateBeforeSave()

  • initiates a validator factory to validate all validation rules that the model contains
  • is used in resource model before save

$model->beforeSave()

  • dispatches model_save_before
  • dispatches $model->_eventPrefix . ‘_save_before’
  • possible use case: can be used for business logic check of ACL check

$model->isSaveAllowed()

  • checks $model->_dataSaveAllowed flag
  • it can be helpful to control the saving process in case, for example, data validation failed

$model->afterSave()

  • dispatches model_save_after
  • dispatches $model->_eventPrefix . ‘_save_after’
  • dispatches clean_cache_by_tags
  • possible use case: can be used for post-processing logic

$model->afterCommitCallback()

  • dispatches model_save_commit_after
  • dispatches $model->_eventPrefix . ‘_save_commit_after’
  • is used for further operations after data has been committed
  • is called via call_user_func() in the resource model 
  • possible use case: can be used for post-processing logic

$model->delete()

  • is used for deleting database records that represent model entities
  • deletes a record based on the value of the primary key
  • dispatches delete() request to the data mapper layer

$model->beforeDelete()

  • dispatches model_delete_before
  • dispatches $model->_eventPrefix . ‘_delete_before’
  • existing events handlers are used to check if deletion is allowed for the current area, and to invalidate the object-related cache
  • possible use cases: can be used for various business logic

$model->afterDelete()

  • dispatches model_delete_after
  • dispatches $model->_eventPrefix . ‘_delete_after’
  • dispatches clean_cache_by_tags
  • possible use cases: can be used for various business logic

$model->afterDeleteCommit()

  • dispatches model_delete_commit_after
  • dispatches $model->_eventPrefix . ‘_delete_commit_after’
  • is used for further operations after delete has been committed
  • possible use case: can be used for various business logic, including post-delete cleanup

Setup Scripts and Resources

Setup scripts is a way to install or upgrade schema or predefined stored data when a module is installed afresh, or a new version of the module is updated.

Module version is set in the module’s module.xml file:

1

2

3

4

5

6

 <module name=“Magento_Cms” setup_version=“2.0.0”>

    <sequence>

        <module name=“Magento_Store”/>

        <module name=“Magento_Theme”/>

    </sequence>

</module>

Processed module versions are registered in the setup_module table:

In order to install (or upgrade) a module, the following commands must be ran:

  • php bin/magento cache:flush
  • php bin/magento setup:upgrade
  • php bin/magento module:enable <Your_ModuleName>

Then check if the module is present in the System Configuration under Advanced/Advanced.

Note that schema_version and data_version are always same unless an error occurred during running the setup script.

In order to install a new module, there should be install schema class created:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

namespace Magento\Cms\Setup;

 

use Magento\Framework\Setup\InstallSchemaInterface;

use Magento\Framework\Setup\ModuleContextInterface;

use Magento\Framework\Setup\SchemaSetupInterface;

use Magento\Framework\DB\Adapter\AdapterInterface;

 

/**

 * @codeCoverageIgnore

 */

class InstallSchema implements InstallSchemaInterface

{

    /**

     * {@inheritdoc}

     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)

     */

    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)

    {

        $installer = $setup;

        $installer->startSetup();

        $connection = $installer->getConnection();

 

        // …

 

        $installer->endSetup();

    }

}

The syntax for DDL commands used for manipulating DB schema are the same as in Magento 1.x.

Once the module is successfully installed, and the both versions are updated in the setup_module table, the InstallSchema::install() method will never be run again (short of deleting the module’s record in the setup_module table). Instead, an upgrade class should be created. Upgrade schema class is very similar to the install schema class with a few key differences.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

namespace Magento\Catalog\Setup;

 

use Magento\Framework\Setup\UpgradeSchemaInterface;

use Magento\Framework\Setup\ModuleContextInterface;

use Magento\Framework\Setup\SchemaSetupInterface;

use Magento\Catalog\Model\ResourceModel\Product\Attribute\Backend\Media;

use Magento\Catalog\Model\Product\Attribute\Backend\Media\ImageEntryConverter;

 

/**

 * Upgrade the Catalog module DB scheme

 */

class UpgradeSchema implements UpgradeSchemaInterface

{

    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)

    {

        $setup->startSetup();

 

        / …

 

        $setup->endSetup();

    }

}

Install/upgrade data classes are for preparing, presetting and persisting model-level data at the moment the module is installed or upgraded. 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

namespace Magento\Catalog\Setup;

 

use Magento\Framework\Setup\InstallDataInterface;

use Magento\Framework\Setup\ModuleContextInterface;

use Magento\Framework\Setup\ModuleDataSetupInterface;

 

/**

 * @codeCoverageIgnore

 */

class InstallData implements InstallDataInterface

{

    public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)

    {

        $setup->startSetup();

 

        // …

 

        $setup->endSetup();

    }

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

namespace Magento\Catalog\Setup;

 

use Magento\Framework\Setup\UpgradeDataInterface;

use Magento\Framework\Setup\ModuleContextInterface;

use Magento\Framework\Setup\ModuleDataSetupInterface;

 

/**

 * Upgrade Data script

 * @codeCoverageIgnore

 */

class UpgradeData implements UpgradeDataInterface

{

    public function upgrade(ModuleDataSetupInterface $setup, ModuleContextInterface $context)

    {

        $setup->startSetup();

 

 

        // …

 

        $setup->endSetup();

    }

}

Setup process provides a setup context to every install or upgrade script. The context object contains information necessary for the setup process.

It’s mandatory for schema setup classes and is considered a best practice with data setup classes to start your install/upgrade logic with $setup->startSetup() and end it with $setup->endSetup().

Even though it’s still possible to command setup logic to run SQL clauses directly, it’s highly recommended to avoid this in favor using DDL syntax to make the code unified and be in RDBMS-independent way.

The workflow Magento 2 uses for setting installs/upgrades, includes the following commands:

  • php bin/magento setup:db:status
  • php bin/magento setup:upgrade
  • php bin/magento setup:db-schema:upgrade
  • php bin/magento setup:db-data:upgrade

EAV Concepts

EAV in Magento is used to provide flexibility in storing variable amount of attributes to different entities, maintain different types of attributes and store attribute values across different stores. It also allows developers to encapsulate attribute-related business logic and manage attributes by adding new and removing unused ones.

EAV storage is separated into meta information and content parts.

Meta Information contains:

  • Entity types
  • Attributes per entity type
  • Attribute sets and groups

Content part contains:

  • Entity records
  • Attribute values

Major difference between a classic EAV and Magento EAV implementation:

  • In Magento, core EAV tables are prefixed with “eav_
  • A backend type is assigned to each attribute (examples: varchar, int, decimal, text…)
  • EAV tables store entity data in separate tables. There entity-specific tables (catalog_category_entity, catalog_product_entity, customer_entity, etc.) and common eav_entity table that contains meta data
  • Special unique attributes with global scope are integrated into the entity-specific tables and are called static attributes, they have a static backend type
  • Attribute value tables are also split by entity_type and backend type
  • Certain information is duplicated between tables for performance reason (for example, entity_type_id is included into three different tables)

In Magento, it’s also possible to group attributes into groups and sets. While there can be only one attribute set assigned to a specific entity type, the groups are used only for clarity, so that developers could group attributes for better end user perception.

There can be different groups in each attribute set, but there is always a default group with a code “general“.

As it was mentioned before, the storage of attribute values is distributed across several DB tables. For example, for the entity type catalog_product:

Table name

Description

catalog_product_entity

The main entity table, among other things contains values for static attributes

catalog_product_entity_int

Stores integer values for attributes

catalog_product_entity_decimal

Stores decimal value types for attributes

catalog_product_entity_datetime

Stores attribute values of timestamp type

catalog_product_entity_text

Stores values for attributes with big string values

catalog_product_entity_varchar

Stores values for one-line text fields

References to the models related to each attribute are stored in the eav_attribute table and correspond the following:

Field

Description

backend_model

A name of PHP class used to process the attribute data before and after CRUD operations

source_model

A name of PHP class used for providing options for select and multiselect input type attributes

frontend_model

A name of PHP class used for formatting attribute value before display

Comparing to a flat table model structure processing, EAV makes it more complex. Both backend and source models are playing significant role in pre- and post-CRUD operations in EAV models. When displaying attributes for EAV models like catalog_product, catalog_category and customer, the attribute set model and group model are used. The attribute set model is also used when loading all the attributes associated to a specific product for a frontend detail view.

EAV Specifics in Load and Save

There are significant differences in how EAV model CRUD mechanisms work. As the EAV model is still extended from \Magento\Framework\Model\AbstractModel, the resource collection it uses should be extended from \Magento\Eav\Model\Entity\AbstractEntity (let the class name not fool you, it’s a special case of resource model, not data model).

In addition to the standard arsenal of resource model methods, EAV resource model implements additional methods that makes working with attributes easier:

  • getAttribute() — allows to get an attribute instance by a code
  • saveAttribute() — allows to save an attribute value to the EAV entity without going through the whole model saving process. You have to set the attribute value to the entity instance first.
  • unsetAttributes() — allows to unset specified attributes from the entity model
  • getEntityTable() — implemented in contrast to getMainTable() of flat table resource models

EAV collection also has additional distinct methods which influence the load process  that are worth mentioning:

  • addAttributeToSelect()
  • addAttributeToFilter()
  • joinAttribute()

They work same way as in Magento 1.x.

Unlike Magento 1.x, where attribute values were fetched from EAV tables using complex JOINs, in Magento 2 UNION construct is used that is much faster. It applies for both the attributes loaded for the collection, and for the process of loading a single entity.

Attribute Management

Below are the most important field within an attribute record with description. Note that some of those fields are stored in the main attribute table, eav_attribute, some will be stored in additional attribute table.

Field

Description

attribute_id

Unique attribute ID

entity_type_id

Entity type ID to associate the attribute with a specific entity type

attribute_code

Unique human-readable attribute code, must be unique within same entity type

attribute_model

Optional alternative model to use. It defaults to \Magento\Eav\Model\Entity\Attribute if not specified.

backend_model

Optional alternative backend model. It defaults to \Magento\Eav\Model\Entity\Attribute\Backend\DefaultBackend if not specified.

backend_type

Data type of the attribute. It’s either “static“, or corresponds to the postfilx of an attribute value table specific for each entity type

backend_table

Optional name of the attribute value table. If not specified, the name is formed as <entity_type_code>_<backend_type>.

frontend_model

Optional alternative frontend model. It defaults to \Magento\Eav\Model\Entity\Attribute\Frontend\DefaultFrontend if not specified.

frontend_input

Input type for the attribute in case the admin HTML form is being rendered automatically

frontend_label

Default label in case the admin HTML form is being rendered automatically

frontend_class

Optional CSS class name which will be added to the admin HTML input element for the attribute in case the admin HTML form is being rendered automatically. Useful for JS validation.

source_model

Optional alternative source model. Should only be specified for the attributes with select and multiselect frontend input type. Each source model must be extended from \Magento\Eav\Model\Entity\Attribute\Source\AbstractSource and implement \Magento\Eav\Model\Entity\Attribute\Source\SourceInterface.

is_required

Enabled JS validation in case the admin HTML form is being rendered automatically. It is also evaluated during the DataFlow and ImportExport processes.

default_value

Optional default value, it is being displayed by the frontend model if the attribute doesn’t have a value.

When an access to the meta info of the EAV system is accessible through \Magento\Eav\Model\Config. It aggregates the meta information about entity types and attributes.

As it was mentioned before, there are a number of standard backend types for attributes. Starting with “static“, which defines an attribute that is stored in the main entity data table, and completing the list with “varchar“, “text“, “int“, “decimal“, “datetime“. However, custom backend types are possible, too.

The EAV setup model \Magento\Eav\Setup\EavSetup must be used to work with attributes and entity types in the module setup classes. It’s a very important class for this reason.

$eavSetup->addAttribute($entityTypeId, $code, $attr)

  • adds an attribute with a specific code to a specific entity type
  • fields in the $attr argument are being mapped before applied to the attribute table fields
  • if the default values are sufficient, there is no need to specify a field in the array of parameters

$eavSetup->updateAttribute($entityTypeId, $id, $field, $value, $sortOrder)

  • updates a specific field of an attribute
  • the $value goes directly to a database table column that corresponds to $field

The fields mapper is different for different EAV entity implementations, for example this is how it looks for \Magento\Catalog\Model\ResourceModel\Setup\PropertyMapper:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

namespace Magento\Catalog\Model\ResourceModel\Setup;

 

use Magento\Eav\Model\Entity\Setup\PropertyMapperAbstract;

 

class PropertyMapper extends PropertyMapperAbstract

{

    /**

     * Map input attribute properties to storage representation

     *

     * @param array $input

     * @param int $entityTypeId

     * @return array

     * @SuppressWarnings(PHPMD.UnusedFormalParameter)

     */

    public function map(array $input, $entityTypeId)

    {

        return [

            ‘frontend_input_renderer’ => $this->_getValue($input, ‘input_renderer’),

            ‘is_global’ => $this->_getValue(

                $input,

                ‘global’,

                \Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface::SCOPE_GLOBAL

            ),

            ‘is_visible’ => $this->_getValue($input, ‘visible’, 1),

            ‘is_searchable’ => $this->_getValue($input, ‘searchable’, 0),

            ‘is_filterable’ => $this->_getValue($input, ‘filterable’, 0),

            ‘is_comparable’ => $this->_getValue($input, ‘comparable’, 0),

            ‘is_visible_on_front’ => $this->_getValue($input, ‘visible_on_front’, 0),

            ‘is_wysiwyg_enabled’ => $this->_getValue($input, ‘wysiwyg_enabled’, 0),

            ‘is_html_allowed_on_front’ => $this->_getValue($input, ‘is_html_allowed_on_front’, 0),

            ‘is_visible_in_advanced_search’ => $this->_getValue($input, ‘visible_in_advanced_search’, 0),

            ‘is_filterable_in_search’ => $this->_getValue($input, ‘filterable_in_search’, 0),

            ‘used_in_product_listing’ => $this->_getValue($input, ‘used_in_product_listing’, 0),

            ‘used_for_sort_by’ => $this->_getValue($input, ‘used_for_sort_by’, 0),

            ‘apply_to’ => $this->_getValue($input, ‘apply_to’),

            ‘position’ => $this->_getValue($input, ‘position’, 0),

            ‘is_used_for_promo_rules’ => $this->_getValue($input, ‘used_for_promo_rules’, 0),

            ‘is_used_in_grid’ => $this->_getValue($input, ‘is_used_in_grid’, 0),

            ‘is_visible_in_grid’ => $this->_getValue($input, ‘is_visible_in_grid’, 0),

            ‘is_filterable_in_grid’ => $this->_getValue($input, ‘is_filterable_in_grid’, 0),

        ];

    }

}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s