In Magento2, how to get the product description from Sales order table

Question

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 ?

Answer 1

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

$productRepository; 
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.

Answer 2

Using

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

   public function __construct(\Magento\Catalog\Model\ProductRepository $productRepository){
$this->$productRepository=$productRepository;

}

  public function customMethod(){
   $sku=//get from order object;
   $product=$this->$productRepository->get($sku);
   $product->getDescription();
   }

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

Answer 3

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, 
       product_id, 
       price, 
       sku, 
       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 
                                                   WHERE 
              attribute_code = 'description' 
              AND entity_type_id = 4);  

You might also what to consider store_id