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? – Dominic Xigen May 31 '19 at 10:55
-
I need to write it in to a csv file. My requirement is such that I want to join tables – MGento May 31 '19 at 12:24
-
Build an array. Write the array to CSV. magestore.com/magento-2-tutorial/… – Dominic Xigen May 31 '19 at 13:10
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.
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 '19 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,
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 '19 at 5:32
-
I wont be writing raw query, I will convert this in to Magento DB functions – MGento Jun 1 '19 at 5:45
-
The query work on a clean install. You can see it here snag.gy/RezN5s.jpg – vitoriodachef Jun 1 '19 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 '19 at 22:33