CODEWITHSUNDEEP
magento
balanv
balanv

Reputation: 10898

How are product attributes and attribute options stored in Magento database?

I am trying to figure out how the linkage between attribute and attribute options, and product and attributes are made in Magento. Is there any reference to how this is working? or anyone give me a hint on this.

Thanks,

Balan

Upvotes: 33

Views: 95746

Answers (6)

Mohamad Hamouday
Mohamad Hamouday

Reputation: 2783

You can get all product properties by using this query:

SELECT CPEV.entity_id, CPE.sku, EA.attribute_id, EA.frontend_label, CPEV.value
    FROM catalog_product_entity_varchar AS CPEV 
    INNER JOIN catalog_product_entity AS CPE ON CPE.entity_id = CPEV.entity_id
    INNER JOIN eav_attribute AS EA ON(CPEV.attribute_id = EA.attribute_id AND EA.entity_type_id = 4)
    INNER JOIN catalog_eav_attribute AS CEA ON(CEA.attribute_id = EA.attribute_id AND CEA.is_visible_on_front = 1 AND CEA.is_visible_in_grid = 1)

Upvotes: 0

Anthony
Anthony

Reputation: 3218

As Alan Storm says: "you do not have to know about how your db works. You have to learn how the models work ". (This is not an exact quote. I gave you the meaning).

But I created own scheme to understand the DB structure. So this screen shows how it works: enter image description here enter image description here

Hope, it helps.

Also I recommend you to look through these links:

http://www.magentocommerce.com/wiki/2_-_magento_concepts_and_architecture/magento_database_diagram

http://alanstorm.com/magento_advanced_orm_entity_attribute_value_part_1

Upvotes: 70

dlink
dlink

Reputation: 1595

I've found these queries to be very helpful for hunting down things like - where does it say the product color is black?, for example.

-- show_product_attr.sql
select
   p.entity_id,
   p.entity_type_id,
   p.attribute_set_id,
   p.type_id,
   p.sku,
   a.attribute_id,
   a.frontend_label as attribute,
   av.value
from
   catalog_product_entity p
   left join catalog_product_entity_{datatype} av on
      p.entity_id = av.entity_id
   left join eav_attribute a on
      av.attribute_id = a.attribute_id
where
   -- p.entity_id = 28683
   -- p.sku = '0452MR'
   p.entity_id = {eid}
;

And for attr_options

-- show_product_attr_options.sql
select
   p.entity_id,
   -- p.entity_type_id,
   -- p.attribute_set_id,
   p.type_id,
   p.sku,
   a.attribute_id,
   a.frontend_label as attribute,
   -- a.attribute_code,
   av.value,
   ao.*
from
   catalog_product_entity p

   left join catalog_product_entity_int av on
      p.entity_id = av.entity_id

   left join eav_attribute a on
      av.attribute_id = a.attribute_id
   left join eav_attribute_option_value ao on
      av.value = ao.option_id 
where
   -- p.entity_id = 28683
   p.entity_id = {eid}
;

You need to replace {datatype} with text, varchar, int, decimal, etc, for the first query, and {eid} with entity_id for both queries. Which you can do on the command like like this:

$ cat show_product_attr_options.sql | sed -e "s/{eid}/30445/" | mysql -uUSER -pPASS DATABASE -t
+-----------+---------+--------------+--------------+---------------------------+-------+----------+-----------+----------+--------------------+-------------+
| entity_id | type_id | sku          | attribute_id | attribute                 | value | value_id | option_id | store_id | value              | colorswatch |
+-----------+---------+--------------+--------------+---------------------------+-------+----------+-----------+----------+--------------------+-------------+
|     30445 | simple  | 840001179127 |           96 | Status                    |     1 |     5972 |         1 |        0 | Male               | NULL        |
|     30445 | simple  | 840001179127 |          102 | Visibility                |     1 |     5972 |         1 |        0 | Male               | NULL        |
|     30445 | simple  | 840001179127 |          122 | Tax Class                 |     2 |     5973 |         2 |        0 | Female             | NULL        |
|     30445 | simple  | 840001179127 |          217 | Size                      |   257 |    17655 |       257 |        0 | XS                 | NULL        |
|     30445 | simple  | 840001179127 |          217 | Size                      |   257 |    17657 |       257 |        1 | XS                 | NULL        |
|     30445 | simple  | 840001179127 |          224 | Color                     |   609 |    18717 |       609 |        0 | Arctic Ice Heather | NULL        |
|     30445 | simple  | 840001179127 |          260 | Featured                  |     0 |     NULL |      NULL |     NULL | NULL               | NULL        |
|     30445 | simple  | 840001179127 |          262 | Clearance Product         |     0 |     NULL |      NULL |     NULL | NULL               | NULL        |
|     30445 | simple  | 840001179127 |          263 | Skip from Being Submitted |     0 |     NULL |      NULL |     NULL | NULL               | NULL        |
|     30445 | simple  | 840001179127 |          283 | Discontinued              |     0 |     NULL |      NULL |     NULL | NULL               | NULL        |
+-----------+---------+--------------+--------------+---------------------------+-------+----------+-----------+----------+--------------------+-------------+

A similar set of sql scripts can be created for catalog.

Upvotes: 6

Waschbär
Waschbär

Reputation: 389

1) The attributes are stored in eav_attribute. There you get the attribute_id.

2) The options are stored in eav_attribute_option_value. There yout get the option_id.

3) The options are assigned to the product in catalog_product_entity_varchar. There you need the entity_id of the product, the attribute_id from 1) and the value which are the comma separated option_ids from 2)

Upvotes: 38

komodosp
komodosp

Reputation: 3646

SELECT pei.value 
FROM `catalog_product_entity_int` pei 
JOIN `eav_attribute` ea 
ON pei.attribute_id = ea .attribute_id 
WHERE pei.entity_id = {your product_id} 
AND ea.attribute_code = '{your attribute_code}'

Note that there are a number of different tables like catalog_product_entity_int depending on the type of the attribute, so one of those other ones could be appropriate.

Upvotes: 4

ShaunOReilly
ShaunOReilly

Reputation: 2206

Product Attributes are extra values that you can assign to a product and is stored in the main EAV table, by name, and the data is then stored in a few different tables based on the data type, like varchar, decimal, text Integer, date, etc.

if you had multiple values for your Product Attribute, then that will be stored in the Attribute Options tables, again, different tables based on the data type.

the following link explains the relationships better: http://www.magentocommerce.com/wiki/2_-_magento_concepts_and_architecture/magento_database_diagram

And deeper developer's detail: http://www.magentocommerce.com/knowledge-base/entry/magento-for-dev-part-7-advanced-orm-entity-attribute-value

And Attribute sets will be the other thing you come across, like the name suggests, a set of attributes grouped together. http://www.magentocommerce.com/knowledge-base/entry/how-do-i-create-an-attribute-set

HTH Shaun

Upvotes: 3

Related Questions