EAV Values

Value Table Structure

Common Value Table Structure

All EAV value tables follow the same pattern:

-- varchar values (short text)
CREATE TABLE catalog_product_entity_varchar (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
    attribute_id SMALLINT UNSIGNED NOT NULL COMMENT 'Attribute ID',
    entity_id INT UNSIGNED NOT NULL COMMENT 'Entity ID',
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Store ID',
    value VARCHAR(255) DEFAULT NULL COMMENT 'Value',
    PRIMARY KEY (value_id),
    INDEX IDX_ENTITY_ATTRIBUTE (entity_id, attribute_id),
    INDEX IDX_ATTRIBUTE (attribute_id),
    INDEX IDX_STORE (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- int values (integers and dropdowns)
CREATE TABLE catalog_product_entity_int (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    value INT DEFAULT NULL,
    PRIMARY KEY (value_id),
    INDEX IDX_ENTITY_ATTRIBUTE (entity_id, attribute_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- decimal values (prices, weights)
CREATE TABLE catalog_product_entity_decimal (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    value DECIMAL(12,4) DEFAULT NULL,
    PRIMARY KEY (value_id),
    INDEX IDX_ENTITY_ATTRIBUTE (entity_id, attribute_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- text values (long text)
CREATE TABLE catalog_product_entity_text (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    value TEXT DEFAULT NULL,
    PRIMARY KEY (value_id),
    INDEX IDX_ENTITY_ATTRIBUTE (entity_id, attribute_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- datetime values
CREATE TABLE catalog_product_entity_datetime (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    value DATETIME DEFAULT NULL,
    PRIMARY KEY (value_id),
    INDEX IDX_ENTITY_ATTRIBUTE (entity_id, attribute_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Querying Value Tables

Direct SQL Queries

-- Get all values for a product
SELECT 
    a.attribute_code,
    a.backend_type,
    CASE a.backend_type
        WHEN 'varchar' THEN v.value
        WHEN 'int' THEN CAST(i.value AS CHAR)
        WHEN 'decimal' THEN CAST(d.value AS CHAR)
        WHEN 'text' THEN LEFT(t.value, 100)
        WHEN 'datetime' THEN CAST(dt.value AS CHAR)
    END AS value
FROM eav_attribute a
LEFT JOIN catalog_product_entity_varchar v 
    ON v.attribute_id = a.attribute_id AND v.entity_id = 123 AND v.store_id = 0
LEFT JOIN catalog_product_entity_int i 
    ON i.attribute_id = a.attribute_id AND i.entity_id = 123 AND i.store_id = 0
LEFT JOIN catalog_product_entity_decimal d 
    ON d.attribute_id = a.attribute_id AND d.entity_id = 123 AND d.store_id = 0
LEFT JOIN catalog_product_entity_text t 
    ON t.attribute_id = a.attribute_id AND t.entity_id = 123 AND t.store_id = 0
LEFT JOIN catalog_product_entity_datetime dt 
    ON dt.attribute_id = a.attribute_id AND dt.entity_id = 123 AND dt.store_id = 0
WHERE a.entity_type_id = 4
AND (v.value IS NOT NULL OR i.value IS NOT NULL OR d.value IS NOT NULL 
     OR t.value IS NOT NULL OR dt.value IS NOT NULL);

Get Specific Attribute Value

-- Get product name (attribute_id=71, varchar)
SELECT value FROM catalog_product_entity_varchar 
WHERE entity_id = 123 AND attribute_id = 71 AND store_id = 0;

-- Get product price (attribute_id=75, decimal)
SELECT value FROM catalog_product_entity_decimal 
WHERE entity_id = 123 AND attribute_id = 75 AND store_id = 0;

-- Get product status (attribute_id=97, int)
SELECT value FROM catalog_product_entity_int 
WHERE entity_id = 123 AND attribute_id = 97 AND store_id = 0;

Multi-Store Values

-- Get store-specific value (store_id = 1)
SELECT value FROM catalog_product_entity_varchar 
WHERE entity_id = 123 AND attribute_id = 71 AND store_id = 1;

-- Fall back to default if store-specific doesn't exist
SELECT COALESCE(
    (SELECT value FROM catalog_product_entity_varchar 
     WHERE entity_id = 123 AND attribute_id = 71 AND store_id = 1),
    (SELECT value FROM catalog_product_entity_varchar 
     WHERE entity_id = 123 AND attribute_id = 71 AND store_id = 0)
) AS value;

Value Storage in PHP

Setting Attribute Values

// Get attribute ID
$attribute = $eavConfig->getAttribute('catalog_product', 'name');
$attributeId = $attribute->getAttributeId(); // 71

// Set value via product model
$product->setName('New Product Name');
$product->save();

// Set value directly in database
$connection = $setup->getConnection();
$connection->insert('catalog_product_entity_varchar', [
    'attribute_id' => 71,
    'entity_id' => 123,
    'store_id' => 0,
    'value' => 'New Product Name'
]);

// Update existing value
$connection->update(
    'catalog_product_entity_varchar',
    ['value' => 'Updated Name'],
    ['entity_id = ?' => 123, 'attribute_id = ?' => 71, 'store_id = ?' => 0]
);

Getting Attribute Values

// Via product model (recommended)
$name = $product->getName();
$price = $product->getPrice();
$status = $product->getStatus();

// Via getData()
$name = $product->getData('name');
$allData = $product->getData(); // All attributes as array

// Via attribute code
$value = $product->getData('custom_attribute');

// Check if attribute has value
if ($product->hasData('name')) {
    echo $product->getName();
}

Loading Specific Attributes

// Load only specific attributes (performance optimization)
$product = $productRepository->getById(123, true, null, false);
$product->setData('name', null); // Clear cache

// Load with specific attributes
$collection->addAttributeToSelect(['name', 'price', 'sku']);
// Only loads these 3 attributes instead of all

// Load with all attributes
$collection->addAttributeToSelect('*');
// Loads all attributes (slower)

Value Operations

Batch Value Operations

-- Bulk update attribute values
UPDATE catalog_product_entity_decimal 
SET value = value * 1.10 
WHERE attribute_id = 75 AND entity_id IN (1, 2, 3);

-- Copy values between stores
INSERT INTO catalog_product_entity_varchar (attribute_id, entity_id, store_id, value)
SELECT attribute_id, entity_id, 2, value 
FROM catalog_product_entity_varchar 
WHERE store_id = 0 AND attribute_id = 71;

-- Delete store-specific override (fall back to default)
DELETE FROM catalog_product_entity_varchar 
WHERE entity_id = 123 AND attribute_id = 71 AND store_id = 1;

Value Cleanup

-- Find orphaned values (no matching entity)
SELECT v.value_id, v.entity_id
FROM catalog_product_entity_varchar v
LEFT JOIN catalog_product_entity p ON p.entity_id = v.entity_id
WHERE p.entity_id IS NULL;

-- Delete orphaned values
DELETE v FROM catalog_product_entity_varchar v
LEFT JOIN catalog_product_entity p ON p.entity_id = v.entity_id
WHERE p.entity_id IS NULL;

-- Find duplicate values (same entity, attribute, different stores)
SELECT entity_id, attribute_id, COUNT(*) as cnt
FROM catalog_product_entity_varchar
WHERE store_id IN (0, 1)
GROUP BY entity_id, attribute_id, value
HAVING cnt > 1;

Value Statistics

-- Count values per attribute
SELECT 
    a.attribute_code,
    a.backend_type,
    CASE a.backend_type
        WHEN 'varchar' THEN (SELECT COUNT(*) FROM catalog_product_entity_varchar WHERE attribute_id = a.attribute_id)
        WHEN 'int' THEN (SELECT COUNT(*) FROM catalog_product_entity_int WHERE attribute_id = a.attribute_id)
        WHEN 'decimal' THEN (SELECT COUNT(*) FROM catalog_product_entity_decimal WHERE attribute_id = a.attribute_id)
    END AS value_count
FROM eav_attribute a
WHERE a.entity_type_id = 4
ORDER BY value_count DESC;

Flashcards

Question

What are the 5 EAV value tables?

Click to reveal answer

Answer

varchar, int, decimal, text, datetime

Question

What does store_id=0 mean?

Click to reveal answer

Answer

Default store values that other stores fall back to

Question

How to get attribute value?

Click to reveal answer

Answer

$product->getName() or $product->getData('name')

Question

How to set attribute value?

Click to reveal answer

Answer

$product->setName('value')->save()

Question

How to optimize attribute loading?

Click to reveal answer

Answer

Use addAttributeToSelect() to load only needed attributes

Revision Notes

Key Takeaways

1. 5 value tables: varchar, int, decimal, text, datetime
2. Each value row has: value_id, attribute_id, entity_id, store_id, value
3. store_id=0 is default, other stores fall back to it
4. Use addAttributeToSelect() to optimize loading
5. Direct SQL queries can be faster for bulk operations

Interview Tips

• Explain how EAV values are stored across multiple tables
• Discuss store-level value inheritance (fall back to default)
• Describe optimization techniques for attribute loading

Cheat Sheet

Value Tables:
  *_entity_varchar   â†’ Short text (name, SKU)
  *_entity_int       â†’ Integer (status, dropdowns)
  *_entity_decimal   â†’ Decimal (price, weight)
  *_entity_text      â†’ Long text (description)
  *_entity_datetime  â†’ Date (special_price_from)

Value Columns:
  value_id     â†’ Auto-increment ID
  attribute_id â†’ Links to eav_attribute
  entity_id    â†’ Links to entity table
  store_id     â†’ 0=default, N=store specific
  value        â†’ Actual data

PHP Access:
  $product->getName()
  $product->getData('name')
  $product->setData('name', $value)->save()

Value Table Structure

Common Value Table Structure

Querying Value Tables

Direct SQL Queries

Get Specific Attribute Value

Multi-Store Values

Value Storage in PHP

Setting Attribute Values

Getting Attribute Values

Loading Specific Attributes

Value Operations

Batch Value Operations

Value Cleanup

Value Statistics

Quiz

Flashcards

Revision Notes

Key Takeaways

Interview Tips

Cheat Sheet