Magento2 – Join sales_order_address Table with Sales Collection Factory

join-tablemagento2order-grid

If I already have the function (last 30 minutes order cron job)below to get sales_order_grid table data, how can I join table to get sales_order_address table and get country_id, city and postal_code?

public function getSalesOrderGrid()
{
    try {
        $itemData = $this->salesCollectionFactory->create();
        $currentDate = date("Y-m-d H:i:s"); // Y-m-d h:i:s
        $newDate = strtotime('-30 MINUTE', strtotime($currentDate));
        $newDate = date('Y-m-d H:i:s', $newDate);

        $itemData->addFieldToFilter('updated_at', ['gteq' => $newDate]);
        $itemData->addFieldToFilter('updated_at', ['lteq' => $currentDate]);
        $itemData->load();

        return $itemData;
    } catch (Exception $e) {
        $this->logger->error($e->getMessage());
    }

    return null;
}

Best Answer

Can you try with the below code.

public function getSalesOrderGrid()
{
    try {
        $itemData = $this->salesCollectionFactory->create();
        $currentDate = date("Y-m-d H:i:s"); // Y-m-d h:i:s
        $newDate = strtotime('-30 MINUTE', strtotime($currentDate));
        $newDate = date('Y-m-d H:i:s', $newDate);

        $itemData->addFieldToFilter('updated_at', ['gteq' => $newDate]);
        $itemData->addFieldToFilter('updated_at', ['lteq' => $currentDate]);
        /* join with sales order address table */
        $itemData->getSelect()
        ->join(
            ["soa" => "sales_order_address"],
            'main_table.entity_id = soa.parent_id',
            array('country_id', 'city', 'postal_code')
        )
        ->where(
        'soa.address_type=?',
        'billing');
        $itemData->load();

        return $itemData;
    } catch (Exception $e) {
        $this->logger->error($e->getMessage());
    }

    return null;
}

For more details you can Refer below source code link

Related Topic