how to get product specific attribute codes & values using raw sql query?

Question

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'";

• Marius: why do you need it from a query? Can't you just use collections?
• Sivakumar K: product collection above 4lakh.so i think through raw sql query i can get some performance.
• Sander Mangel: What are you trying to accomplish? If we know the end result helping you might be easier
• Sivakumar K: I want to store into one table like flat catalog table.but in my table i need all atttributes with sku as primary key.
• Prateek: 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.

Answer 1

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';

• André Ferraz: This is incorrect, entity_type_id does not exist in catalog_product_entity.

Answer 2

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.

An 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 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

Answer 3

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);

• André Ferraz: This is incorrect, entity_type_id does not exist in catalog_product_entity.