The article is dedicated to catalog product, the tables used for creating attributes, attribute set, attribute group. The first part of the article has already been published. Don’t forget to review EAV in Magento 2 in order to have a full understanding of the issue.
1.10. Create product attribute with Visual Swatch type
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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`, `additional_data`) VALUES (168, 0, 0, 0, 0, 0, 1, 0, 0, 0, NULL, 0, 0, 1, 1, 1, 'a:3:{s:17:"swatch_input_type";s:6:"visual";s:28:"update_product_preview_image";s:1:"0";s:28:"use_product_image_for_swatch";s:1:"0";}') INSERT INTO `eav_attribute_option` (`attribute_id`, `sort_order`) VALUES ('168', '1') DELETE FROM `eav_attribute_option_value` WHERE (option_id = '217') INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES ('217', '0', 'White') INSERT INTO `eav_attribute_option` (`attribute_id`, `sort_order`) VALUES ('168', '2') DELETE FROM `eav_attribute_option_value` WHERE (option_id = '218') INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES ('218', '0', 'Black') UPDATE `eav_attribute` SET `default_value` = '' WHERE (attribute_id = '168') INSERT INTO `eav_attribute_option_swatch` (`option_id`, `store_id`, `type`, `value`) VALUES (217, 0, 1, '#ffffff') INSERT INTO `eav_attribute_option_swatch` (`option_id`, `store_id`, `type`, `value`) VALUES (218, 0, 1, '#000000') |
1.11. Create product attribute with Text Swatch type
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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, 'text_swatch', NULL, 'int', 'select', 'Text Swatch', 'Magento\\Eav\\Model\\Entity\\Attribute\\Source\\Table', 0, 1, 0) DELETE FROM `eav_attribute_label` WHERE (attribute_id ='169') 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`, `additional_data`) VALUES (169, 0, 0, 0, 0, 0, 1, 0, 0, 0, NULL, 0, 0, 1, 1, 1, 'a:3:{s:17:"swatch_input_type";s:4:"text";s:28:"update_product_preview_image";s:1:"0";s:28:"use_product_image_for_swatch";i:0;}') INSERT INTO `eav_attribute_option` (`attribute_id`, `sort_order`) VALUES ('169', '1') DELETE FROM `eav_attribute_option_value` WHERE (option_id = '219') INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES ('219', '0', 'Description one') INSERT INTO `eav_attribute_option` (`attribute_id`, `sort_order`) VALUES ('169', '2') DELETE FROM `eav_attribute_option_value` WHERE (option_id = '220') INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES ('220', '0', 'Description two') UPDATE `eav_attribute` SET `default_value` = '' WHERE (attribute_id = '169') INSERT INTO `eav_attribute_option_swatch` (`option_id`, `store_id`, `type`, `value`) VALUES (219, 0, 0, 'Swatch one') INSERT INTO `eav_attribute_option_swatch` (`option_id`, `store_id`, `type`, `value`) VALUES (219, 1, 0, NULL) INSERT INTO `eav_attribute_option_swatch` (`option_id`, `store_id`, `type`, `value`) VALUES (220, 0, 0, 'Swatch two') INSERT INTO `eav_attribute_option_swatch` (`option_id`, `store_id`, `type`, `value`) VALUES (220, 1, 0, NULL) |
2. Create attribute set with our new attributes. You can perform this in the admin panel: Stores – Attributes – Attribute Sets – Add Attribute Sets
|
1 |
INSERT INTO `eav_attribute_set` (`entity_type_id`, `attribute_set_name`) VALUES (4,'Test Set') |
3. Add new attribute group in attribute set
Add new attribute group in attribute set as it is shown in the picture below. Save it and check the queries. Firstly, the record about a new attribute group is added:
|
1 |
INSERT INTO `eav_attribute_group` (`attribute_set_id`, `attribute_group_name`, `sort_order`, `attribute_group_code`) VALUES (16, 'Test group', 11, 'test-group') |
For each attribute, the same queries are made. The difference is in attribute_id and sort_order. Check all SQL queries.
Let’s have a closer look at SQL queries using the following attribute:
|
1 |
UPDATE `eav_attribute` SET `entity_type_id` = 4 WHERE (attribute_id='159') |
Redundant line, as all the attributes created have entity_type_id = 4
|
1 |
DELETE FROM `eav_attribute_label` WHERE (attribute_id ='159') |
Update attribute label. If we assigned the label for the attribute, there would be a new line added:
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO `eav_attribute_label` (`attribute_id`, `store_id`, `value`) VALUES ('159', 1, 'Label Name') UPDATE `catalog_eav_attribute` SET `attribute_id` = 159 WHERE (attribute_id = '159') DELETE FROM `eav_entity_attribute` WHERE (attribute_id =159) AND (attribute_set_id =16) INSERT INTO `eav_entity_attribute` (`entity_type_id`, `attribute_set_id`, `attribute_group_id`, `attribute_id`, `sort_order`) VALUES ('4', 16, 101, 159, 1) DELETE FROM `eav_entity_attribute` WHERE (attribute_id =159) AND (attribute_set_id =16) INSERT INTO `eav_entity_attribute` (`entity_type_id`, `attribute_set_id`, `attribute_group_id`, `attribute_id`, `sort_order`) VALUES ('4', 16, 101, 159, 1) |
Two couples of similar queries where we get data updated: eav_entity_attribute
In such a manner, if we remove query redundancy and imagine creating attributes with no labels for the first time, the only query will be enough:
|
1 |
INSERT INTO `eav_entity_attribute` (`entity_type_id`, `attribute_set_id`, `attribute_group_id`, `attribute_id`, `sort_order`) VALUES ('4', 16, 101, 159, 1); |
One might create 1 query for all 11 attributes:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
INSERT INTO `eav_entity_attribute` (`entity_type_id`, `attribute_set_id`, `attribute_group_id`, `attribute_id`, `sort_order`) VALUES ('4', 16, 101, 159, 1), ('4', 16, 101, 160, 2), ('4', 16, 101, 161, 3), ('4', 16, 101, 162, 4), ('4', 16, 101, 163, 5), ('4', 16, 101, 164, 6), ('4', 16, 101, 165, 7), ('4', 16, 101, 166, 8), ('4', 16, 101, 167, 9), ('4', 16, 101, 168, 10), ('4', 16, 101, 169, 11); |
The picture below contains the tables we’ve already dealt with:
4. Let’s consider the tables used when assigning attributes to products.
When saving attribute values, it’s necessary to use the tables eav_entity_{type}
entity_id – id product.
4.1. Text Field
|
1 |
INSERT INTO `catalog_product_entity_varchar` (`entity_id`,`attribute_id`,`value`,`store_id`) VALUES (2047, '159', 'Text Field Value', 0) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `value` = VALUES(`value`), `store_id` = VALUES(`store_id`) |
4.2. Text Area
|
1 |
INSERT INTO `catalog_product_entity_text` (`entity_id`,`attribute_id`,`value`,`store_id`) VALUES (2047, '160', 'Text Area Value', 0) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `value` = VALUES(`value`), `store_id` = VALUES(`store_id`) |
4.3. Date
|
1 |
INSERT INTO `catalog_product_entity_datetime` (`entity_id`,`attribute_id`,`value`,`store_id`) VALUES (2047, '161', '2017-12-20 00:00:00', 0) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `value` = VALUES(`value`), `store_id` = VALUES(`store_id`) |
4.4. Yes/No
|
1 |
INSERT INTO `catalog_product_entity_int` (`entity_id`,`attribute_id`,`value`,`store_id`) VALUES (2047, '162', 1, 0) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `value` = VALUES(`value`), `store_id` = VALUES(`store_id`) |
In the example yes/no attribute is equal to yes(1).
4.5. Multiple Select
|
1 |
INSERT INTO `catalog_product_entity_varchar` (`entity_id`,`attribute_id`,`value`,`store_id`) VALUES (2047, '163', '213,214', 0) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `value` = VALUES(`value`), `store_id` = VALUES(`store_id`) |
For multi-selection I chose 2 values. It’s apparent that id values are saved as lines.
4.6. Dropdown
|
1 |
INSERT INTO `catalog_product_entity_int` (`entity_id`,`attribute_id`,`value`,`store_id`) VALUES (2047, '164', 216, 0) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `value` = VALUES(`value`), `store_id` = VALUES(`store_id`) |
4.7. Price
|
1 |
INSERT INTO `catalog_product_entity_decimal` (`entity_id`,`attribute_id`,`value`,`store_id`) VALUES (2047, '165', 99.0, 0) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `value` = VALUES(`value`), `store_id` = VALUES(`store_id`) |
4.8. Media Image
|
1 |
INSERT INTO `catalog_product_entity_varchar` (`entity_id`,`attribute_id`,`value`,`store_id`) VALUES (2047, '166', '/h/o/hours.png.tmp', '0') ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `value` = VALUES(`value`), `store_id` = VALUES(`store_id`) |
4.9. Fixed Product Tax
|
1 2 3 |
DELETE FROM `weee_tax` WHERE (entity_id = 2047) AND (attribute_id = 167) INSERT INTO `weee_tax` (`website_id`, `country`, `state`, `value`, `attribute_id`, `entity_id`) VALUES ('0', 'AW', '', '1', '167', 2047) INSERT INTO `weee_tax` (`website_id`, `country`, `state`, `value`, `attribute_id`, `entity_id`) VALUES ('0', 'HT', '0', '2', '167', 2047) |
4.10. Visual Swatch
|
1 |
INSERT INTO `catalog_product_entity_int` (`entity_id`,`attribute_id`,`value`,`store_id`) VALUES (2047, '168', 217, 0) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `value` = VALUES(`value`), `store_id` = VALUES(`store_id`) |
4.11. Text Swatch
|
1 |
INSERT INTO `catalog_product_entity_int` (`entity_id`,`attribute_id`,`value`,`store_id`) VALUES (2047, '169', 219, 0) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `value` = VALUES(`value`), `store_id` = VALUES(`store_id`) |
Magento Custom Development
Take your online store to the next level with BelVG Magento Custom Development
Visit the page
Partner With Us
Looking for a partner to grow your business? We are the right company to bring your webstore to success.
Talk to Andrey
Thank you for reading, Syed!
A well-detailed guide. Thanks for sharing.