What EAV Is

Why a product's fields are rows in six tables instead of columns in one, which table holds what, and what that costs.

Syntaxcatalog_product_entity.entity_id = catalog_product_entity_varchar.entity_id AND attribute_id = ? AND store_id = ?

EAV stands for entity-attribute-value. It is a storage pattern where a record's fields are rows rather than columns. Magento stores products, categories and customers this way so a merchant can add a field to every product from the admin without a database migration. Adding a field means inserting a row.

The tables

catalog_product_entity holds one row per product and eight columns: entity_id, attribute_set_id, type_id, sku, has_options, required_options, created_at, updated_at. There is no name column and no price column.

An attribute is one named field, such as name. Its definition is a row in eav_attribute. Its value is a row in the table named by the attribute's backend_type: catalog_product_entity_varchar, _int, _decimal, _text or _datetime. Each value row holds value_id, attribute_id, store_id, entity_id and value. The store_id column is what lets one store view show a different product name.

A few attributes are columns on the entity table itself. Those are called static, and sku is one of them.

What it costs

One product with ten non-static attributes is eleven rows spread over up to six tables. Reading a list of products therefore costs extra queries, and most of this stage is about managing that cost: value loading grouped by table, index tables, and a cache over attribute definitions.

Magento Open Source links a value row to its product with entity_id. Adobe Commerce, where content staging keeps several versions of a product, links with row_id instead. Ask the entity metadata for the link field rather than hardcoding either one.

Example

Example · php
<?php
// app/code/Vendor/EavDemo/Console/Command/WhereStored.php
declare(strict_types=1);

namespace Vendor\EavDemo\Console\Command;

use Magento\Catalog\Api\Data\ProductAttributeInterface;
use Magento\Eav\Model\Config as EavConfig;
use Magento\Framework\Console\Cli;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class WhereStored extends Command
{
    public function __construct(
        private readonly EavConfig $eavConfig,
        ?string $name = null
    ) {
        parent::__construct($name);
    }

    protected function configure(): void
    {
        $this->setName('vendor:eav:where-stored');
        $this->setDescription('Show which table holds each product attribute value');
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        foreach (['sku', 'name', 'price', 'description'] as $code) {
            $attribute = $this->eavConfig->getAttribute(
                ProductAttributeInterface::ENTITY_TYPE_CODE,
                $code
            );

            $output->writeln(sprintf(
                '%-12s %-9s %s',
                $code,
                $attribute->getBackendType(),
                $attribute->isStatic()
                    ? 'catalog_product_entity (column)'
                    : $attribute->getBackendTable()
            ));
        }

        return Cli::RETURN_SUCCESS;
    }
}

// bin/magento vendor:eav:where-stored
// sku          static    catalog_product_entity (column)
// name         varchar   catalog_product_entity_varchar
// price        decimal   catalog_product_entity_decimal
// description  text      catalog_product_entity_text

When to use it

  • Explaining to a backend developer why catalog_product_entity has no name column.
  • Writing a raw report query against product data and needing to know which table a field lives in.
  • Deciding whether a new field belongs in EAV or in its own table with declarative schema.

More examples

Ask for the link field, never hardcode it

getLinkField() returns 'entity_id' on Magento Open Source and 'row_id' on Adobe Commerce when content staging is installed. Any code that writes a raw join against a value table has to use it, or the query works on one edition and fails on the other. The comment block shows the promoted constructor property next to the longhand assignment it desugars to, so you can recognise both in other people's modules.

Example · php
<?php
// app/code/Vendor/EavDemo/Model/LinkField.php
declare(strict_types=1);

namespace Vendor\EavDemo\Model;

use Magento\Catalog\Api\Data\ProductInterface;
use Magento\Framework\EntityManager\MetadataPool;

class LinkField
{
    // Shorthand: a promoted constructor property.
    public function __construct(private readonly MetadataPool $metadataPool)
    {
    }

    public function get(): string
    {
        return $this->metadataPool
            ->getMetadata(ProductInterface::class)
            ->getLinkField();
    }
}

// The same constructor, longhand. The two are identical:
//
//     private MetadataPool $metadataPool;
//
//     public function __construct(MetadataPool $metadataPool)
//     {
//         $this->metadataPool = $metadataPool;
//     }

Count the value rows behind one product

A diagnostic that makes the storage shape concrete: a single simple product in a two-store installation usually returns a few dozen rows. Note the bound parameter in where('entity_id = ?', $entityId) rather than a value concatenated into the string, and getTableName(), which applies the installation's table prefix. Reach for this when you are explaining to someone why one product save writes to five tables.

Example · php
<?php
// app/code/Vendor/EavDemo/Model/ValueRowCount.php
declare(strict_types=1);

namespace Vendor\EavDemo\Model;

use Magento\Framework\App\ResourceConnection;

class ValueRowCount
{
    private const VALUE_TABLES = [
        'catalog_product_entity_varchar',
        'catalog_product_entity_int',
        'catalog_product_entity_decimal',
        'catalog_product_entity_text',
        'catalog_product_entity_datetime',
    ];

    public function __construct(private readonly ResourceConnection $resource)
    {
    }

    public function forProduct(int $entityId): int
    {
        $connection = $this->resource->getConnection();
        $total = 0;

        foreach (self::VALUE_TABLES as $table) {
            $select = $connection->select()
                ->from($this->resource->getTableName($table), 'COUNT(*)')
                ->where('entity_id = ?', $entityId);

            $total += (int) $connection->fetchOne($select);
        }

        return $total;
    }
}

Related

m2 eav attributesm2 attribute setsm2 eav queriesm2 eav flatm2 model trio

Discussion

  • Be the first to comment on this lesson.