6

The following query will give product all attribute codes & values.but I want specific attributes.and i want to store that result into one table.the table format like flat catalog product table.

Note:question is updated.

$query = "SELECT    ce.sku,          
       ea.attribute_code,
       CASE ea.backend_type
          WHEN 'varchar' THEN ce_varchar.value
          WHEN 'int' THEN ce_int.value
          WHEN 'text' THEN ce_text.value
          WHEN 'decimal' THEN ce_decimal.value
          WHEN 'datetime' THEN ce_datetime.value
          ELSE ea.backend_type
       END AS value
   FROM (select sku ,entity_type_id,entity_id from catalog_product_entity where type_id='simple' limit $limit,$offset ) AS ce
   LEFT JOIN eav_attribute AS ea
       ON ce.entity_type_id = ea.entity_type_id
   LEFT JOIN catalog_product_entity_varchar AS ce_varchar
       ON ce.entity_id = ce_varchar.entity_id
       AND ea.attribute_id = ce_varchar.attribute_id
       AND ea.backend_type = 'varchar'
   LEFT JOIN catalog_product_entity_int AS ce_int
       ON ce.entity_id = ce_int.entity_id
       AND ea.attribute_id = ce_int.attribute_id
       AND ea.backend_type = 'int'
   LEFT JOIN catalog_product_entity_text AS ce_text
       ON ce.entity_id = ce_text.entity_id
       AND ea.attribute_id = ce_text.attribute_id
       AND ea.backend_type = 'text'
   LEFT JOIN catalog_product_entity_decimal AS ce_decimal
       ON ce.entity_id = ce_decimal.entity_id
       AND ea.attribute_id = ce_decimal.attribute_id
       AND ea.backend_type = 'decimal'
   LEFT JOIN catalog_product_entity_datetime AS ce_datetime
       ON ce.entity_id = ce_datetime.entity_id
       AND ea.attribute_id = ce_datetime.attribute_id
       AND ea.backend_type = 'datetime'";
  • why do you need it from a query? Can't you just use collections? – Marius Dec 15 '15 at 9:18
  • product collection above 4lakh.so i think through raw sql query i can get some performance. – Sivakumar K Dec 15 '15 at 9:19
  • What are you trying to accomplish? If we know the end result helping you might be easier – Sander Mangel Dec 15 '15 at 9:26
  • I want to store into one table like flat catalog table.but in my table i need all atttributes with sku as primary key. – Sivakumar K Dec 15 '15 at 9:27
  • 1
    fetchAll for 4 lac is not a good idea either. You should set up some cron job to do this incrementally so that your server doesn't suffer when this query is executed. Also, check for the out of memory errors too. – Prateek Dec 15 '15 at 14:49
2

For Magento version 2.2, where a new redirection has been added in their EAV model, below query is working for my project. There the entity_type_id needed to be retrieved from catalog_product_entity.

SELECT    ce.sku,           
        ea.attribute_code,
        CASE ea.backend_type
           WHEN 'varchar' THEN ce_varchar.value
           WHEN 'int' THEN ce_int.value
           WHEN 'text' THEN ce_text.value
           WHEN 'decimal' THEN ce_decimal.value
           WHEN 'datetime' THEN ce_datetime.value
           ELSE ea.backend_type
        END AS value
    FROM (select cpe.sku, eas.entity_type_id, cpe.entity_id 
                FROM catalog_product_entity AS cpe, eav_attribute_set AS eas
                WHERE cpe.attribute_set_id=eas.attribute_set_id) AS ce
    LEFT JOIN eav_attribute AS ea
        ON ce.entity_type_id = ea.entity_type_id
    LEFT JOIN catalog_product_entity_varchar AS ce_varchar
        ON ce.entity_id = ce_varchar.entity_id
        AND ea.attribute_id = ce_varchar.attribute_id
        AND ea.backend_type = 'varchar'
    LEFT JOIN catalog_product_entity_int AS ce_int
        ON ce.entity_id = ce_int.entity_id
        AND ea.attribute_id = ce_int.attribute_id
        AND ea.backend_type = 'int'
    LEFT JOIN catalog_product_entity_text AS ce_text
        ON ce.entity_id = ce_text.entity_id
        AND ea.attribute_id = ce_text.attribute_id
        AND ea.backend_type = 'text'
    LEFT JOIN catalog_product_entity_decimal AS ce_decimal
        ON ce.entity_id = ce_decimal.entity_id
        AND ea.attribute_id = ce_decimal.attribute_id
        AND ea.backend_type = 'decimal'
    LEFT JOIN catalog_product_entity_datetime AS ce_datetime
        ON ce.entity_id = ce_datetime.entity_id
        AND ea.attribute_id = ce_datetime.attribute_id
        AND ea.backend_type = 'datetime';
1

You are doing more harm than good by avoiding the proper way of handling this. I would like to be sure that people know this is very much unacceptable if developing for a client or on a live production site.

The proper method would be to obtain the product collection, where you can apply any required filtering methods to reduce the collection to whatever is needed. Once you have your collection, then use the Magento Framework walk() method defined in the core/resource_iterator model class: ./app/code/core/Mage/Core/Model/Resource/Iterator.php.

Example is fairly well illustrated within magento/app/code/core/Mage/Rss/Block/Catalog/NotifyStock.php:

 // ...
        $product = Mage::getModel('catalog/product');
        /* @var $collection Mage_Catalog_Model_Resource_Product_Collection */
        $collection = $product->getCollection();
        Mage::getResourceModel('cataloginventory/stock')->addLowStockFilter($collection, array(
            'qty',
            'notify_stock_qty',
            'low_stock_date',
            'use_config' => 'use_config_notify_stock_qty'
        ));
        $collection
            ->addAttributeToSelect('name', true)
            ->addAttributeToFilter('status',
                array('in' => Mage::getSingleton('catalog/product_status')->getVisibleStatusIds())
            )
            ->setOrder('low_stock_date');

        /*
        using resource iterator to load the data one by one
        instead of loading all at the same time. loading all data at the same time can cause the big memory allocation.
        */
        Mage::getSingleton('core/resource_iterator')->walk(
            $collection->getSelect(),
            array(array($this, 'addNotifyItemXmlCallback')),
            array('rssObj'=> $rssObj, 'product'=>$product, 'globalQty' => $globalNotifyStockQty)
        );

I also think you may be unaware that EAV attribute table relations within Magento are not handled with left join, instead they are actually done with union

0

Following code is working fine.

 <?php
    include ('app/Mage.php');
    Mage::app();
    ini_set('display_errors',1);
    $resource = Mage::getSingleton('core/resource');
    $readConnection = $resource->getConnection('core_read');
    $query = "SELECT    ce.sku,           
            ea.attribute_code,
            CASE ea.backend_type
               WHEN 'varchar' THEN ce_varchar.value
               WHEN 'int' THEN ce_int.value
               WHEN 'text' THEN ce_text.value
               WHEN 'decimal' THEN ce_decimal.value
               WHEN 'datetime' THEN ce_datetime.value
               ELSE ea.backend_type
            END AS value
        FROM (select sku ,entity_type_id,entity_id from catalog_product_entity limit 100) AS ce
        LEFT JOIN eav_attribute AS ea
            ON ce.entity_type_id = ea.entity_type_id
        LEFT JOIN catalog_product_entity_varchar AS ce_varchar
            ON ce.entity_id = ce_varchar.entity_id
            AND ea.attribute_id = ce_varchar.attribute_id
            AND ea.backend_type = 'varchar'
        LEFT JOIN catalog_product_entity_int AS ce_int
            ON ce.entity_id = ce_int.entity_id
            AND ea.attribute_id = ce_int.attribute_id
            AND ea.backend_type = 'int'
        LEFT JOIN catalog_product_entity_text AS ce_text
            ON ce.entity_id = ce_text.entity_id
            AND ea.attribute_id = ce_text.attribute_id
            AND ea.backend_type = 'text'
        LEFT JOIN catalog_product_entity_decimal AS ce_decimal
            ON ce.entity_id = ce_decimal.entity_id
            AND ea.attribute_id = ce_decimal.attribute_id
            AND ea.backend_type = 'decimal'
        LEFT JOIN catalog_product_entity_datetime AS ce_datetime
            ON ce.entity_id = ce_datetime.entity_id
            AND ea.attribute_id = ce_datetime.attribute_id
            AND ea.backend_type = 'datetime'";
    $results = $readConnection->fetchAll($query);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.