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

databasemagento-2.1magento2

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 ?

Best Answer

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.

Related Topic