Attributwerte von Kategorien und Produkten über SQL-Abfragen zugreifen

Hin- und wieder ist ein Zugrif auf Attributwerte von Kategorien und Produkten über SQL-Abfragen notwendig. Solche Abfragen sind bei Fehlersuche und -Korrektur hilfreich, aber auch beim Import und Export von Daten.

Aufgrund des flexiblen Entity-Attribute-Value-Modells (EAV-Modells) von Magento sind aus dem Backend bekannte Attributcodes (z. B. price) und Attributwerte (z. B. 9.90) auf verschiedene Tabellen verteilt, die über JOIN miteinander verknüpft werden müssen.

Die nachfolgende SQL-Abfragen sind ausschließlich lesende Abfragen und demonstrieren, wie einzelne Produkt- und Kategorieattribute zugegriffen werden können.

Produkte

Produktnamen und URL-Keys

Namen, URL-Keys und andere Werte von einzeiligen Textfeldern sind in der Tabelle catalog_product_entity_varchar gespeichert. Die Spalte für Nutzdaten hat den Datentyp VARCHAR(255), welcher aufgrund der UTF8-Kodierung 1-3 Byte pro Zeichen und 1 Byte für die Kodierung der Länge verwendet.

Produktnamen:

SELECT entity_id, sku, eav_vc.value as name, store_id FROM catalog_product_entity
JOIN catalog_product_entity_varchar eav_vc USING(entity_id)
JOIN eav_attribute eav ON (eav_vc.entity_type_id=eav.entity_type_id AND eav_vc.attribute_id=eav.attribute_id
AND eav.attribute_code = 'name');

URL-Key (diese Abfrage unterscheidet sich von der vorherigen nur im Attributcode):

SELECT entity_id, sku, eav_vc.value as name, store_id FROM catalog_product_entity
JOIN catalog_product_entity_varchar eav_vc USING(entity_id)
JOIN eav_attribute eav ON (eav_vc.entity_type_id=eav.entity_type_id AND eav_vc.attribute_id=eav.attribute_id
AND eav.attribute_code = 'url_key');

Preise

Magento speichert Preise in der Tabelle catalog_product_entity_decimal. Um Rundundsfehler zu vermeiden, wird für Preise ein Festpunkt- einem Fließpunkt-Datentyp vorgezogen. Die Nutzspalte hat den Datentyp DECIMAL(12,4), d. h. der Preis kann insgesamt 8 Stellen vor dem Komma und 4 Nachkommastellen aufnehmen. Ein Produkt kann in Magento also maximal 100 Millionen teuer sein.

SELECT entity_id, sku, eav_dec.value as price FROM catalog_product_entity
JOIN catalog_product_entity_decimal eav_dec USING(entity_id)
JOIN eav_attribute eav ON (eav_dec.entity_type_id=eav.entity_type_id AND eav_dec.attribute_id=eav.attribute_id
AND eav.attribute_code = 'price');

Kombinierte Abfrage

Produktname, URL-Key und Preise lassen sich in einer Abfrage kombinieren. Für verschiedene Attributspalten werden dabei gleiche Tabellen mehrfach über verschiedene Aliases referenziert:

SELECT entity_id, sku, eav_vc2.value name, eav_vc1.value url_key, eav_dec.value as price FROM catalog_product_entity
JOIN catalog_product_entity_decimal eav_dec USING(entity_id)
JOIN eav_attribute eav ON (eav_dec.entity_type_id=eav.entity_type_id AND eav_dec.attribute_id=eav.attribute_id AND eav_dec.store_id=0
AND eav.attribute_code = 'price')
JOIN catalog_product_entity_varchar eav_vc1 USING(entity_id)
JOIN eav_attribute eav1 ON (eav_vc1.entity_type_id=eav1.entity_type_id AND eav_vc1.attribute_id=eav1.attribute_id AND eav_vc1.store_id=0
AND eav1.attribute_code = 'url_key')
JOIN catalog_product_entity_varchar eav_vc2 USING(entity_id)
JOIN eav_attribute eav2 ON (eav_vc1.entity_type_id=eav2.entity_type_id AND eav_vc2.attribute_id=eav2.attribute_id AND eav_vc2.store_id=0
AND eav2.attribute_code = 'name');

Für mehr Übersicht wurde diese Abfrage auf die Standardwerte reduziert. Name und URL-Key in anderen StoreViews wird nicht aufgelistet.

Werenbestand

Der Warenbestand wird in der Tabelle cataloginventory_stock_item gespeichert, wobei neben qty (Anzahl, engl.: quantity) auch die Spalte is_in_stock (Im Lager) für die Anzeige des Produkts im Frontend ausschlaggebend sind:

SELECT entity_id, sku, eav_vc.value as name, qty, is_in_stock
FROM catalog_product_entity p
JOIN catalog_product_entity_varchar eav_vc USING(entity_id)
JOIN eav_attribute eav ON (eav_vc.entity_type_id=eav.entity_type_id AND eav_vc.attribute_id=eav.attribute_id AND store_id=0
AND eav.attribute_code = 'name')
JOIN cataloginventory_stock_item ON(p.entity_id=product_id);

Kategorien

Name, URL-Key und vollständige Pfade zu allen Kategorien im Shop auflisten:

SELECT cat.entity_id, parent_id, eav_vc.value name, eav_vc1.value url_key, request_path, rew.store_id
FROM catalog_category_entity cat
JOIN catalog_category_entity_varchar eav_vc USING(entity_id)
JOIN eav_attribute eav ON(eav_vc.entity_type_id=eav.entity_type_id AND eav_vc.attribute_id=eav.attribute_id AND eav_vc.store_id=0
AND eav.attribute_code = 'name')
JOIN catalog_category_entity_varchar eav_vc1 USING(entity_id)
JOIN eav_attribute eav1 ON(eav_vc1.entity_type_id=eav1.entity_type_id AND eav_vc1.attribute_id=eav1.attribute_id AND eav_vc1.store_id=0
AND eav1.attribute_code = 'url_key')
JOIN core_url_rewrite rew ON (rew.category_id=cat.entity_id AND rew.store_id=1)
WHERE id_path LIKE 'category/%';

Das Ergebnis wird auf die Standardwerte reduziert. Um Werte für alle StoreViews anzeigen zu lassen, muss überall die Bedingung store_id=0 entfernt werden.

Tipp zum einfachen Kopieren der Abfragen: Doppelklick in den Rahmen deaktiviert die Formatierung und markiert die gesamte Abfrage.

Dieser Beitrag wurde unter Reports veröffentlicht. Setze ein Lesezeichen auf den Permalink.

4 Antworten auf Attributwerte von Kategorien und Produkten über SQL-Abfragen zugreifen

  1. Vinai Kopp sagt:

    Klasse Post, vielen Dank!
    Als kleine Ergänzung: statt Tabellen und Spalten direkt anzugeben ist es kompatibler sie sich von den jeweiligen Entitäten zu holen. Bei reinem SQL ist das natürlich nicht möglich, aber falls die Queries in PHP gebaut werden ist das eine gute Idee. Zum Beispiel:
    Die Tabele catalog_product_entity bekommt man durch $product->getResource()->getEntityTable().
    Die Tabelle in der das price Attribut gespeichert wird:
    $product->getResource()->getAttribute(‚price‘)->getBackend()->getTable()

  2. Pingback: Türchen 09: StoreView-spezifische EAV-Daten über schnelle SQL-Abfragen auslesen « Magento Blog für Entwickler und eCommerce-Shops - webguys.de Magento Blog für Entwickler und eCommerce-Shops – webguys.de

  3. Immanuel sagt:

    Die eav_attribute-Tabellen müssen mit einem LEFT [OUTER] JOIN hinzugefügt werden, ein normaler JOIN liefert ein leeres Ergebnis:
    Mein Beispiel für eine kombinierte Abfrage:
    mysql_query(“
    SELECT entity_id, CE.sku AS sku, attribute_id, V.value AS „.$attr1.“, V1.value AS „.$attr2.“, V2.value“.$attr3.“
    FROM catalog_product_entity CE
    JOIN catalog_product_entity_int V USING (entity_id)
    LEFT JOIN eav_attribute EAV ON (V.entity_type_id = EAV.entity_type_id AND V.attribute_id = EAV.attribute_id AND EAV.attribute_code = ‚“.$attr1.“‚)
    JOIN catalog_product_entity_int V1 USING (entity_id)
    LEFT JOIN eav_attribute EAV1 ON (V1.entity_type_id = EAV1.entity_type_id AND V1.attribute_id = EAV1.attribute_id AND EAV1.attribute_code = ‚“.$attr2.“‚)
    JOIN catalog_product_entity_int V2 USING (entity_id)
    LEFT JOIN eav_attribute EAV2 ON (V2.entity_type_id = EAV2.entity_type_id AND V2.attribute_id = EAV2.attribute_id AND EAV2.attribute_code = ‚“.$attr3.“‚)

Schreib einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *