2

I have found separate queries that get me some things on their own, but the problem is that the sku order they each have is different, at a given row it changes so I cannot paste the results into a single document. The export tool for Products in magento gives horrendous mixed up rows, it is completely useless. So, since I dont know how the php syntax for magento and its folder architecture is, I cant do it that way, I only am familiar with SQL but not so much with magento tables EAV.

For example the query for price gets me these results

product_id   price
1             45
2             56
3             98
etc

but the query for description gets them so

product_id   description
3             this is description for item 3
1             this is description for item 1
2             as you see, we cant match up all results

For price I have found in SO

SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, 

`price_index`.`final_price`,  `price_index`.`min_price`, `price_index`.`max_price`, 
`price_index`.`tier_price` FROM `cp8_catalog_product_entity` AS `e` INNER JOIN 
`catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND
 price_index.website_id = '1' AND price_index.customer_group_id = 0

for Description

SELECT e.entity_id, e.sku, eav.value AS 'description'
FROM cp8_catalog_product_entity e
JOIN cp8_catalog_product_entity_text eav
  ON e.entity_id = eav.entity_id
JOIN cp8_eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE ea.attribute_code = 'description'

So, I am strugling to get one complete result that contains:

product_id, name, description, price, url to the image, meta keywords, etc

  • If the anwer was useful, please considerupvoting too. – sv3n Aug 10 '17 at 22:17
1

Problem seems to be that description, meta_keywords art not part of flat table ...

Please try this query:

SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`
FROM `catalog_product_entity` AS `e` 
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0

If this attributes are part of flat tables this should work:

MAGENTO_ROOT/myFile.php

<?php
require_once('./app/Mage.php');
error_reporting(E_ALL);
ini_set('display_errors', 1);

umask(0);
Mage::app();

$attributes = array(
    'description',
    'meta_keyword'
);

$collection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect($attributes)
    ->addUrlRewrite()
    ->addMinimalPrice()
    ->addFinalPrice();

echo $collection->getSelect()->__toString();

Note: you can set this in Manage Attributes -> Use in Product Listing.

If not, you I had to diable flat tables first ...

MAGENTO_ROOT/myFile.php

<?php
require_once('./app/Mage.php');
error_reporting(E_ALL);
ini_set('display_errors', 1);

umask(0);
Mage::app();

$helper = Mage::helper('catalog/product_flat');
$status = $helper->getProcess()->getStatus();
$helper->getProcess()->setStatus(Mage_Index_Model_Process::STATUS_RUNNING);


$attributes = array(
    'description',
    'meta_keyword'
);

$collection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect($attributes)
    ->addUrlRewrite()
    ->addMinimalPrice()
    ->addFinalPrice();

echo $collection->getSelect()->__toString();

$helper->getProcess()->setStatus($status);

To output SQL-query, you can use this:

echo $collection->getSelect()->__toString();
  • thank you for that, but unfortunately I am not able to use that code as I am not familiar with magento at all. When it comes to magento I can only understand sql queries, – Arminius Jun 5 '17 at 14:41
  • Updated answer ... just put this content in the root folder of your magento install. – sv3n Jun 5 '17 at 14:45

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.