The Entity-Attribute-Value model is a data model intended for describing entities where the number of attributes is expected to be enormous, but in fact, the number of attributes to be used in the entity is small.
Entity stores information about the type of the data being stored. In the case of Magento, this is customer product, category, and etc.
Attribute is the individual property of each entity (name, weight, email address).
Value is the value of a given entity and attribute.
The benefits the EAV model brings are following:
- Flexible versatile data structure (it’s possible to change the number of properties without having to change the database schema).
- When adding a new attribute for a given entity, we have a possibility to use it in other entities.
- Quick to implement.
Database schema for EAV entities:
- eav_entity – (E) The Entity table.
- eav_entity_attribute (A) The Attrubute table
- eav_entity_{type} (V) – The Value table. {type} – datetime, decimals, int, text and varchar.
Diagram with eav_* tables:
Which EAV entities are there in Magento 2:
The list of entities can be found in the eav_entity_type table:
- customer
- customer_address
- catalog_category
- catalog_product
Which EAV entity types are there in Magento 2:
- eav_entity_int
- eav_entity_varchar
- eav_entity_text
- eav_entity_decimal
- eav_entity_datetime
Magento 2 Development
Take your online store to the next level with BelVG Magento 2 Development
Visit the pageEntities catalog_product in Magento 2
In order to understand the EAV model in Magento 2, let’s consider “catalog_product” example, the type of tables used to create attributes, the functions of attributes in the attribute set and attribute group. Besides, we have a closer look at the tables used to assign the product with an attribute. At every step, there’s SQL code being generated by Magento.
- Create an attribute for each type with default data.
- Create attribute set with new attributes.
- Add new attribute group in the attribute set.
- Consider the tables used to assign the product with an attribute.
1. First off, we create an attribute for each type with default data. You can perform it in the admin panel: Stores – Attributes – Product Attributes – Add New Attribute
- Text Field
- Text Area
- Date
- Yes/No
- Multiple Select
- Dropdown
- Price
- Media Image
- Fixed Product Tax
- Visual Swatch
- Text Swatch
1.1 Create product attribute with text field type
|
1 2 3 4 |
INSERT INTO `eav_attribute` (`entity_type_id`, `attribute_code`, `backend_model`, `backend_type`, `frontend_input`, `frontend_label`, `frontend_class`, `source_model`, `is_required`, `is_user_defined`, `default_value`, `is_unique`) VALUES (4, 'text_field', NULL, 'varchar', 'text', 'Text Field', NULL, NULL, 0, 1, NULL, 0) DELETE FROM `eav_attribute_label` WHERE (attribute_id ='159') |
If we forgot to assign the label for attribute, there would be a line added:
|
1 2 |
INSERT INTO `eav_attribute_label` (`attribute_id`, `store_id`, `value`) VALUES ('159', 1, 'Label Name') INSERT INTO `catalog_eav_attribute` (`attribute_id`, `is_global`, `is_searchable`, `is_filterable`, `is_comparable`, `is_visible_on_front`, `is_html_allowed_on_front`, `is_filterable_in_search`, `used_in_product_listing`, `used_for_sort_by`, `apply_to`, `is_visible_in_advanced_search`, `is_used_for_promo_rules`, `is_used_in_grid`, `is_visible_in_grid`, `is_filterable_in_grid`) VALUES (159, 0, 0, 0, 0, 0, 1, 0, 0, 0, NULL, 0, 0, 1, 1, 1) |
1.2. Create product attribute with text area type
|
1 2 3 |
INSERT INTO `eav_attribute` (`entity_type_id`, `attribute_code`, `backend_model`, `backend_type`, `frontend_input`, `frontend_label`, `source_model`, `is_required`, `is_user_defined`, `default_value`, `is_unique`) VALUES (4, 'text_area', NULL, 'text', 'textarea', 'Text Area', NULL, 0, 1, NULL, 0) DELETE FROM `eav_attribute_label` WHERE (attribute_id ='160') INSERT INTO `catalog_eav_attribute` (`attribute_id`, `is_global`, `is_searchable`, `is_filterable`, `is_comparable`, `is_visible_on_front`, `is_html_allowed_on_front`, `is_filterable_in_search`, `used_in_product_listing`, `apply_to`, `is_visible_in_advanced_search`, `is_wysiwyg_enabled`, `is_used_for_promo_rules`, `is_used_in_grid`, `is_visible_in_grid`, `is_filterable_in_grid`) VALUES (160, 0, 0, 0, 0, 0, 1, 0, 0, NULL, 0, 0, 0, 1, 1, 1) |
1.3. Create product attribute with date type
|
1 2 3 |
INSERT INTO `eav_attribute` (`entity_type_id`, `attribute_code`, `backend_model`, `backend_type`, `frontend_model`, `frontend_input`, `frontend_label`, `source_model`, `is_required`, `is_user_defined`, `default_value`, `is_unique`) VALUES (4, 'date', 'Magento\\Eav\\Model\\Entity\\Attribute\\Backend\\Datetime', 'datetime', 'Magento\\Eav\\Model\\Entity\\Attribute\\Frontend\\Datetime', 'date', 'Date', NULL, 0, 1, NULL, 0) DELETE FROM `eav_attribute_label` WHERE (attribute_id ='161') INSERT INTO `catalog_eav_attribute` (`attribute_id`, `is_global`, `is_searchable`, `is_filterable`, `is_comparable`, `is_visible_on_front`, `is_html_allowed_on_front`, `is_filterable_in_search`, `used_in_product_listing`, `used_for_sort_by`, `apply_to`, `is_visible_in_advanced_search`, `is_used_for_promo_rules`, `is_used_in_grid`, `is_visible_in_grid`, `is_filterable_in_grid`) VALUES (161, 0, 0, 0, 0, 0, 1, 0, 0, 0, NULL, 0, 0, 1, 1, 1) |
1.4. Create product attribute with yes/no type
|
1 2 3 |
INSERT INTO `eav_attribute` (`entity_type_id`, `attribute_code`, `backend_model`, `backend_type`, `frontend_input`, `frontend_label`, `source_model`, `is_required`, `is_user_defined`, `default_value`, `is_unique`) VALUES (4, 'yes_no', NULL, 'int', 'boolean', 'Yes No', 'Magento\\Eav\\Model\\Entity\\Attribute\\Source\\Boolean', 0, 1, '0', 0) DELETE FROM `eav_attribute_label` WHERE (attribute_id ='162') INSERT INTO `catalog_eav_attribute` (`attribute_id`, `is_global`, `is_searchable`, `is_filterable`, `is_comparable`, `is_visible_on_front`, `is_html_allowed_on_front`, `is_filterable_in_search`, `used_in_product_listing`, `used_for_sort_by`, `apply_to`, `is_visible_in_advanced_search`, `is_used_for_promo_rules`, `is_used_in_grid`, `is_visible_in_grid`, `is_filterable_in_grid`) VALUES (162, 0, 0, 0, 0, 0, 1, 0, 0, 0, NULL, 0, 0, 1, 1, 1) |
1.5. Create product attribute with multiple select type
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
INSERT INTO `eav_attribute` (`entity_type_id`, `attribute_code`, `backend_model`, `backend_type`, `frontend_input`, `frontend_label`, `source_model`, `is_required`, `is_user_defined`, `is_unique`) VALUES (4, 'multiple_select', 'Magento\\Eav\\Model\\Entity\\Attribute\\Backend\\ArrayBackend', 'varchar', 'multiselect', 'Multiple Select', NULL, 0, 1, 0) DELETE FROM `eav_attribute_label` WHERE (attribute_id ='163') INSERT INTO `catalog_eav_attribute` (`attribute_id`, `is_global`, `is_searchable`, `is_filterable`, `is_comparable`, `is_visible_on_front`, `is_html_allowed_on_front`, `is_filterable_in_search`, `used_in_product_listing`, `apply_to`, `is_visible_in_advanced_search`, `is_used_for_promo_rules`, `is_used_in_grid`, `is_visible_in_grid`, `is_filterable_in_grid`) VALUES (163, 0, 0, 0, 0, 0, 1, 0, 0, NULL, 0, 0, 1, 1, 1) INSERT INTO `eav_attribute_option` (`attribute_id`, `sort_order`) VALUES ('163', '1') DELETE FROM `eav_attribute_option_value` WHERE (option_id = '213') INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES ('213', '0', 'Option one') INSERT INTO `eav_attribute_option` (`attribute_id`, `sort_order`) VALUES ('163', '2') DELETE FROM `eav_attribute_option_value` WHERE (option_id = '214') INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES ('214', '0', 'Option two') UPDATE `eav_attribute` SET `default_value` = '' WHERE (attribute_id = '163') |
1.6. Create product attribute with dropdown type
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
INSERT INTO `eav_attribute` (`entity_type_id`, `attribute_code`, `backend_model`, `backend_type`, `frontend_input`, `frontend_label`, `source_model`, `is_required`, `is_user_defined`, `is_unique`) VALUES (4, 'dropdown', NULL, 'int', 'select', 'Dropdown', 'Magento\\Eav\\Model\\Entity\\Attribute\\Source\\Table', 0, 1, 0) DELETE FROM `eav_attribute_label` WHERE (attribute_id ='164') INSERT INTO `catalog_eav_attribute` (`attribute_id`, `is_global`, `is_searchable`, `is_filterable`, `is_comparable`, `is_visible_on_front`, `is_html_allowed_on_front`, `is_filterable_in_search`, `used_in_product_listing`, `used_for_sort_by`, `apply_to`, `is_visible_in_advanced_search`, `is_used_for_promo_rules`, `is_used_in_grid`, `is_visible_in_grid`, `is_filterable_in_grid`) VALUES (164, 0, 0, 0, 0, 0, 1, 0, 0, 0, NULL, 0, 0, 1, 1, 1) INSERT INTO `eav_attribute_option` (`attribute_id`, `sort_order`) VALUES ('164', 1) DELETE FROM `eav_attribute_option_value` WHERE (option_id = '215') INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES ('215', '0', 'Option one') INSERT INTO `eav_attribute_option` (`attribute_id`, `sort_order`) VALUES ('164', '2') DELETE FROM `eav_attribute_option_value` WHERE (option_id = '216') INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES ('216', '0', 'Option two') UPDATE `eav_attribute` SET `default_value` = '' WHERE (attribute_id = '164') |
1.7. Create product attribute with price type
|
1 2 3 4 5 |
INSERT INTO `eav_attribute` (`entity_type_id`, `attribute_code`, `backend_model`, `backend_type`, `frontend_input`, `frontend_label`, `source_model`, `is_required`, `is_user_defined`, `default_value`, `is_unique`) VALUES (4, 'price_example', 'Magento\\Catalog\\Model\\Product\\Attribute\\Backend\\Price', 'decimal', 'price', 'Price Example', NULL, 0, 1, NULL, 0) DELETE FROM `eav_attribute_label` WHERE (attribute_id ='165') INSERT INTO `catalog_eav_attribute` (`attribute_id`, `is_global`, `is_searchable`, `is_filterable`, `is_comparable`, `is_visible_on_front`, `is_html_allowed_on_front`, `is_filterable_in_search`, `used_in_product_listing`, `used_for_sort_by`, `apply_to`, `is_visible_in_advanced_search`, `is_used_for_promo_rules`, `is_used_in_grid`, `is_visible_in_grid`, `is_filterable_in_grid`) VALUES (165, 0, 0, 0, 0, 0, 1, 0, 0, 0, NULL, 0, 0, 1, 1, 1) |
1.8. Create product attribute with media image type
|
1 2 3 4 5 |
INSERT INTO `eav_attribute` (`entity_type_id`, `attribute_code`, `backend_model`, `backend_type`, `frontend_input`, `frontend_label`, `source_model`, `is_required`, `is_user_defined`, `is_unique`) VALUES (4, 'media_image', NULL, 'varchar', 'media_image', 'Media image', NULL, 0, 1, 0) DELETE FROM `eav_attribute_label` WHERE (attribute_id ='166') INSERT INTO `catalog_eav_attribute` (`attribute_id`, `is_global`, `is_searchable`, `is_filterable`, `is_comparable`, `is_visible_on_front`, `is_html_allowed_on_front`, `is_filterable_in_search`, `used_in_product_listing`, `used_for_sort_by`, `apply_to`, `is_visible_in_advanced_search`, `is_used_for_promo_rules`, `is_used_in_grid`, `is_visible_in_grid`, `is_filterable_in_grid`) VALUES (166, 0, 0, 0, 0, 0, 1, 0, 0, 0, NULL, 0, 0, 1, 1, 1) |
1.9. Create product attribute with Fixed Product Tax type
|
1 2 3 4 5 |
INSERT INTO `eav_attribute` (`entity_type_id`, `attribute_code`, `backend_model`, `frontend_input`, `frontend_label`, `source_model`, `is_required`, `is_user_defined`, `is_unique`) VALUES (4, 'fixed_product_tax', 'Magento\\Weee\\Model\\Attribute\\Backend\\Weee\\Tax', 'weee', 'Fixed Product Tax', NULL, 0, 1, 0) DELETE FROM `eav_attribute_label` WHERE (attribute_id ='167') INSERT INTO `catalog_eav_attribute` (`attribute_id`, `is_global`, `is_searchable`, `is_filterable`, `is_comparable`, `is_visible_on_front`, `is_html_allowed_on_front`, `is_filterable_in_search`, `used_in_product_listing`, `used_for_sort_by`, `apply_to`, `is_visible_in_advanced_search`, `is_used_for_promo_rules`, `is_used_in_grid`, `is_visible_in_grid`, `is_filterable_in_grid`) VALUES (167, 0, 0, 0, 0, 0, 1, 0, 0, 0, 'simple,virtual,bundle,downloadable,configurable', 0, 0, 1, 1, 1) |
The second part of the article is to be published soon. Stay tuned!
Magento Webdesign
Take your online store to the next level with BelVG Magento Webdesign
Visit the page
Lorem ipsum dolor sit amet consectetur
Lorem ipsum dolor sit amet consectetur. Ornare consectetur et massa vivamus. Sit blandit donec ornare semper. Nisl vel elit
Contact
Hi, Banvari!
Thank you!
Great tutorial…