0

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;
}

1 Answer 1

1

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

5
  • thanks for the input, however I hit another issue. since the sales_order_address table has 2 records for 1 order. 1 record is billing address and then other one is shipping address. If I want to get the billing address data. I found that there is a table called sales_order contain the billing address id and shipping address id. How can I get billing address data from my above function?
    – Paul Fan
    Oct 27, 2021 at 7:37
  • Updated my answer with distinct in query. Oct 27, 2021 at 7:42
  • thanks! I have a question by using distinct, how it define which is shipping address record and which is billing address record? without distinct, it should return both from the query
    – Paul Fan
    Oct 27, 2021 at 7:55
  • 1
    Now I have added the where condition to fetch billing address only. but you can change with shipping as well. Oct 27, 2021 at 12:02
  • oh right! How come I miss that column! thankyou for your input!
    – Paul Fan
    Oct 27, 2021 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.