1

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 ?

2

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.

1

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

  • 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 May 31 at 11:54
0

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

  • this gives error, Unknown column 'catalog_product_entity_text.entity_id' in 'on clause' – MGento Jun 1 at 5:32
  • I wont be writing raw query, I will convert this in to Magento DB functions – MGento Jun 1 at 5:45
  • The query work on a clean install. You can see it here snag.gy/RezN5s.jpg – vitoriodachef Jun 1 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 – Dominic Xigen Jun 1 at 22:33

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.