How to use Join in Magento2. i want to get customer_firstname
from sales_order
table and price
, qty_ordered
columns from sales_order_item
table where sales_order.entity_id=sales_order_item.order_id
Kindly guide me it will be appreciated.
2 Answers
Try below script. It will help you definitely.
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$connection = $objectManager->create('\Magento\Framework\App\ResourceConnection');
$conn = $connection->getConnection();
$select = $conn->select()
->from(
['main_table' => 'sales_order'],
[
'main_table.customer_firstname',
'sales_item.price',
'sales_item.qty_ordered'
]
)
->join(
['sales_item' => 'sales_order_item'],
'main_table.entity_id = sales_item.order_id'
)->order('fieldname','DESC');
$data = $conn->fetchAll($select);
echo "<pre>";
print_r($data); die;
-
can we setOrder in this collection ? like $conn->fetchAll($select)->setOrder('feildname','DESC') ? Jul 4, 2019 at 7:59
-
-
-
It's working I have checked. if there are two fields with the same name, you can you alias like, main_table.fieldname Jul 4, 2019 at 9:35
You are joining sales_order
and sales_order_item
table correctly also add sales_order_item.base_row_total > 0
or sales_order_item.base_price > 0
in your where clause because in configurable product case it give you two rows one for configurable and other for simple item. So it will be good if you want to fetch single row against each product Id.
I hope this will help