Magento 2 - How use join between sales_order and sales_order_item table

Question

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.

Answer 1

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;

Answer 2

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