Ttdfjt

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 ?

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,
 MagentoCatalogApiProductRepositoryInterface $productRepositoryInterface
 // ...
 ) 
 
 $this->productRepository = $productRepositoryInterface;
 // ....

try 
 $product = $productRepository->getById(1234);
 // got description here
 catch (MagentoFrameworkExceptionNoSuchEntityException $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.

Using

MagentoCatalogModelProductRepository you get product descriptionlike below

 public function __construct(MagentoCatalogModelProductRepository $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

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