Magento2 – SQL Query for Custom Attribute

attributesdatabasemagento2querysql

I have create custom attribute in product upc

I want custom query (sql) to get SKUs and UPCs

Can anyone tell me the related tables from where I can get data, or if anyone can share query that would be great.

Best Answer

Magento uses the Eav structure to save the product attribute value.

First, you have to find out your attribute type of upc.So, you have to open eav_attribute and run the below for getting backend_type]

SELECT attribute_id,backend_type,attribute_code FROM eav_attribute where entity_type_id =4 and attribute_code ='upc'

Suppose, this attribute backend_type is varchar then magento is save the value for products of this attribute at catalog_product_entity_varchar

Magento is saved product lie entity wise like

Date time type attribute  to catalog_product_entity_datetime
Decimal/Price type attribute to catalog_product_entity_decimal
Image type attribute to  catalog_product_entity_gallery
Dropdown type attribute  to catalog_product_entity_int
Image type catalog_product_entity_media_gallery
Image type catalog_product_entity_media_gallery_value
Image type catalog_product_entity_media_gallery_value_to_entity
text type attribute  catalog_product_entity_text
tier price catalog_product_entity_tier_price
Varchar type attribute  to  catalog_product_entity_varchar

So, you have to find the appropriate table of this attribute then run below Query like:

SELECT * FROM catalog_product_entity_varchar where attribute_id =(SELECT attribute_id FROM eav_attribute where entity_type_id =4 and attribute_code ='name')

Here I have done for the name attribute. For your case, you have to change catalog_product_entity_varchar with respect to your backend type of attribute.

If select specific attributes and their values below query helpful

select * from eav_attribute_option join eav_attribute_option_value on eav_attribute_option_value.option_id = eav_attribute_option.option_id where eav_attribute_option.attribute_id={attribute_id}
Related Topic