0

Trying to fetch multiple attribute values from multi-store in Magento via SQL. But it seems not to be an easy task. Though, I can fetch Website wise attribute value via:

SELECT 
        e.entity_id,
        cw.website_id,
        COALESCE(store_name.value, default_name.value) AS 'name'
FROM `catalog_product_entity` AS `e`
 CROSS JOIN `store_website` AS `cw`
 INNER JOIN `catalog_product_index_website` AS `cwd` ON cw.website_id = cwd.website_id
 INNER JOIN `store_group` AS `csg` ON csg.website_id = cw.website_id AND cw.default_group_id = csg.group_id
 INNER JOIN `store` AS `cs` ON csg.default_store_id = cs.store_id AND cs.store_id != 0
 INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = e.entity_id AND pw.website_id = cw.website_id
LEFT JOIN catalog_product_entity_varchar AS default_name ON default_name.entity_id = e.entity_id AND default_name.store_id = 0 AND default_name.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)
LEFT JOIN catalog_product_entity_varchar AS store_name ON store_name.entity_id = e.entity_id AND store_name.store_id = `cs`.store_id AND store_name.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)

as we have catalog_product_website table which relates between website_id & product_id. But for store<->product relationships, we don't have any special table.

Fetching a single attribute value as per store is fairly easier:

SELECT 
main_tbl.entity_id, 
store_tbl.store_id,
name_tbl.value AS 'name' 
FROM catalog_product_entity main_tbl
LEFT JOIN catalog_product_entity_decimal price_tbl ON price_tbl.entity_id = main_tbl.entity_id AND name.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price' AND entity_type_id = 4)
LEFT JOIN catalog_product_entity_varchar name ON name.entity_id = main.entity_id AND name.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)
INNER JOIN store cs ON cs.store_id = cpev.store_id
ORDER BY cpe.entity_id, cs.store_id ASC

But fetching values for multiple attributes at the same time via SQL is trickier (as some attribute have global scope while other having store view or website scope):

+-----------+----------+------+-------+--------+
| entity_id | store_id | name | price | status |
+-----------+----------+------+-------+--------+
  • what do you want to do.Do you want just a product list in a table view with the name,sku,price,store_id,status format? – Mujahidh Feb 3 '17 at 5:39
  • Yeah, that's correct! – MagePsycho Feb 3 '17 at 6:11
  • @MagePsycho did you get it? – zus Feb 16 '18 at 12:04
2

Create a new file with below contents and run in your root.This is for magento 1.

<?php
require_once 'app/Mage.php';
umask(0);
Mage::app('default');

$collection = Mage::getModel('catalog/product')->getCollection();

echo '<table border="1"> ';
echo '<tr>';
echo "<th>entity_id <th />";
echo "<th>store_id <th />";
echo "<th>name<th />";
echo "<th>sku <th />";
echo "<th>price<th />";
echo "<th>status<th />";
echo '</tr>';

foreach ($collection as $product_all) {
  $sku = $product_all['sku'];
  $product_id =  $product_all['entity_id'];
  // call product model and create product object
  $product    = Mage::getModel('catalog/product')->load($product_id);

  $pk_sku = $product['sku'];
  $pk_name = $product['name'];
  $store_id = $product->getStoreIds();
  $pk_price = $product['price'];
  $status = $product->getStatus();

  echo '<tr>';
  echo "<td>".$product_id."<td />";
  echo "<td>".$store_id[0]."<td />";
  echo "<td>".$pk_name."<td />";
  echo "<td>".$pk_sku."<td />";
  echo "<td>".$pk_price."<td />";
  echo "<td>".$status."<td />";
  echo '</tr>'; 
 }
echo '</table>';?>
  • I need the results using SQL – MagePsycho Feb 3 '17 at 6:49
  • Then some one will help you in your Question.I will leave my answer for some future user.it might be help for them – Mujahidh Feb 3 '17 at 6:51
  • What the hell is that HTML ? <td><td /> ????? – Marcos Pérez Gude Mar 15 '18 at 8:30

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.