I want to display the order id, item description, sku and price of products from the sales order and sales order item tables. For some reason, the description columns in my sales order item table is null. So I want to retrieve the description from the catalog product table by joining it to the sales order or sales order item table. Anyone know the query for that ?

  • Display where? Within admin? Commented May 31, 2019 at 10:55
  • I need to write it in to a csv file. My requirement is such that I want to join tables
    – MGento
    Commented May 31, 2019 at 12:24
  • Build an array. Write the array to CSV. magestore.com/magento-2-tutorial/… Commented May 31, 2019 at 13:10

3 Answers 3


If you are making changes to the frotnend. Not great but easiest way I can think of doing it is loading product by id inside the loop

foreach ($_order->getAllVisibleItems() as $_item) {                    
     echo $_item->getProductId();                    

Then using standard load

public function __construct(
    Context $context,
    \Magento\Catalog\Api\ProductRepositoryInterface $productRepositoryInterface
    // ...
    $this->productRepository = $productRepositoryInterface;
    // ....
try {
   $product = $productRepository->getById(1234);
   // got description here
} catch (\Magento\Framework\Exception\NoSuchEntityException $e) {

Reason to go against the join is that you would have to join sales_order_item soi, catalog_product_entity_text cpet where cpet.entity_id = soi.product_id.

But then filter by cpet.attribute_id = 75 (for my install) and cpet.store_id = 0

Hardcoded IDs is never great. You can work out those IDs with additional lookups but already you see this is turning into a larger task.

If this is for the backend maybe this changes things a little.



Magento\Catalog\Model\ProductRepository you get product descriptionlike below

   public function __construct(\Magento\Catalog\Model\ProductRepository $productRepository){


  public function customMethod(){
   $sku=//get from order object;

**Note:**if you are using magento's older version where may be ProductRepository class not exits so you need to get product from Product Model

  • My requirement is like suh that I want to join these tables , sales order, sales order item, and catalog_product_entity and catalog product entity varchar
    – MGento
    Commented May 31, 2019 at 11:54

I'm not recommending this approach but since this is what you asked, this query should get you going.

SELECT sales_order.entity_id AS order_id, 
       value                 AS product_description 
FROM   sales_order 
       JOIN sales_order_item 
         ON order_id = sales_order_item.product_id 
       JOIN catalog_product_entity_text 
         ON product_id = catalog_product_entity_text.entity_id 
WHERE  catalog_product_entity_text.attribute_id = (SELECT attribute_id 
                                                   FROM   eav_attribute 
              attribute_code = 'description' 
              AND entity_type_id = 4);  

You might also what to consider store_id

  • this gives error, Unknown column 'catalog_product_entity_text.entity_id' in 'on clause'
    – MGento
    Commented Jun 1, 2019 at 5:32
  • I wont be writing raw query, I will convert this in to Magento DB functions
    – MGento
    Commented Jun 1, 2019 at 5:45
  • The query work on a clean install. You can see it here snag.gy/RezN5s.jpg Commented Jun 1, 2019 at 9:34
  • I get empty result set on unmodified 2.3.0 store with sample data. Although the query looks good. Weird. imgur.com/FqPq7WZ Commented Jun 1, 2019 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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