3

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.

5

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;
4
  • can we setOrder in this collection ? like $conn->fetchAll($select)->setOrder('feildname','DESC') ? – HaFiz Umer Jul 4 '19 at 7:59
  • Yes, you can. Check I have updated my answer. – Ravi Soni Jul 4 '19 at 9:17
  • is not work ->order('fieldname','DESC'); – HaFiz Umer Jul 4 '19 at 9:31
  • It's working I have checked. if there are two fields with the same name, you can you alias like, main_table.fieldname – Ravi Soni Jul 4 '19 at 9:35
1

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

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.