Magento 2 – How to Use Join Between Sales Order and Sales Order Item Table

join;magento2sales-order

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.

Best Answer

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;

Create Virtual Query

Create ZEND SQL query which is give you artworks_uploaded basic of artist_id,which give an virtual result query with count and artist id.

$ReadAdapter=Mage::getSingleton('core/resource')->getConnection('core_read');
        $select = $ReadAdapter()->select()
            ->from('mage_art_works', array())
            ->columns(array('artist_id',
    'artworks_uploaded' => new Zend_Db_Expr('COUNT(*)'))
    )->group('mage_art_works.artist_id');

Alias table using above result:

then add the below ZEND SQL to the alias table

$collection->getSelect()
    ->joinLeft(
        array('art' => new Zend_Db_Expr(" ( $select ) ")),
        'main_table.user_id = art.artist_id',
        array()
    );

Best Answer

Related Solutions

Magento Collection – How to Use Group By with Join Query

Collection Join Query – How to Join a Collection with a Query

Create Virtual Query

Alias table using above result:

Related Topic