How to Fetch Canceled Order Details in Magento 2

Table of Contents

In Magento 2, there is no core functionality for programmatically fetching canceled orders. In this blog post, we will explore two methods to retrieve canceled order details such as name, email, shipping method, order comment, and other information from the database tables sales_order and sales_order_status_history. Additionally, we have implemented a frontend form allowing users to specify a date range (from and to) for fetching order details. The method outlined in this post will then fetch and display the relevant canceled orders based on the provided date range and after clicking submit, it will download a CSV file.

Method: 1

While the following example provides a direct SQL query for fetching canceled order details in Magento 2, it’s essential to note that this method is not considered best practice and may not adhere to Magento coding standards. It is recommended to explore alternative, more Magento-friendly approaches for optimal code quality and maintainability.

Path: pub/script/index.php


if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['from_date'], $_POST['to_date'])) {
    // Connect to the database
    $dbHost = 'Your_hostname';
    $dbUser = 'Your_username';
    $dbPass = 'Your_Password';
    $dbName = 'Your_Database_Name';

    $conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
    if ($conn->connect_error) {
        die('Connection failed: ' . $conn->connect_error);

$fromDate = date('Y-m-d', strtotime($_POST['from_date'])) . ' 00:00:00';
$toDate = date('Y-m-d', strtotime($_POST['to_date'])) . ' 23:59:59';

    // Prepare the SQL query to fetch canceled orders
    $sql = "SELECT so.*, GROUP_CONCAT(sosh.comment SEPARATOR ' | ') AS order_comments
        FROM `sales_order` AS so
        LEFT JOIN `sales_order_status_history` AS sosh ON so.entity_id = sosh.parent_id
        WHERE so.`status` = 'canceled' AND so.`created_at` >= ? AND so.`updated_at` <= ?
        GROUP BY so.entity_id";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param('ss', $fromDate, $toDate);
    $result = $stmt->get_result();

    $orders = [];
    while ($row = $result->fetch_assoc()) {
        $orders[] = $row;


    // Check if any orders were found
    if (empty($orders)) {
        echo "No canceled orders found for the specified date range.";
    } else {
        // Proceed with CSV export
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="canceled_orders.csv"');

        $output = fopen('php://output', 'w');
        fputcsv($output, array_keys($orders[0])); // Column headers

        foreach ($orders as $order) {
            fputcsv($output, $order); // Output each order as a CSV row

<!DOCTYPE html>
<html lang="en">
    <meta charset="UTF-8">
    <title>Export Canceled Orders</title>
    <form method="POST" action="">
        <label for="from_date">From Date:</label>
        <input type="date" id="from_date" name="from_date" required>
        <label for="to_date">To Date:</label>
        <input type="date" id="to_date" name="to_date" required>
        <input type="submit" value="Export">

That’s it, Don’t forget to change hostname, database name, username and password.

Method: 2

If you don’t know how to create basic module in Magento 2 – Read this article

Path: app/code/Lokesh/CancelorderExport/etc/frontend/routes.xml

<?xml version="1.0"?>
<config xmlns:xsi="" xsi:noNamespaceSchemaLocation="urn:magento:framework:App/etc/routes.xsd">
    <router id="standard">
        <route id="exportorders" frontName="exportorders">
            <module name="Lokesh_CancelorderExport"/>

Path: app/code/Lokesh/CancelorderExport/Controller/Index/Index.php


namespace Lokesh\CancelorderExport\Controller\Index;

use Magento\Framework\App\Action\Action;
use Magento\Framework\App\Action\Context;
use Magento\Framework\App\ResponseInterface;
use Lokesh\CancelorderExport\Model\Exporter;

class Index extends Action
    protected $exporter;

    public function __construct(
        Context $context,
        Exporter $exporter
    ) {
        $this->exporter = $exporter;

    public function execute()
        $fromDate = $this->getRequest()->getParam('from_date');
        $toDate = $this->getRequest()->getParam('to_date');
        // Check if both from_date and to_date parameters are provided
        if ($fromDate && $toDate) {
            // If parameters are provided, proceed with export
            $this->exporter->exportOrders($fromDate, $toDate);
        } else {
            // If parameters are not provided, load and display the form

Path: app/code/Lokesh/CancelorderExport/Model/Exporter.php


namespace Lokesh\CancelorderExport\Model;

use Magento\Framework\App\ResourceConnection;

class Exporter
    protected $resourceConnection;

    public function __construct(
        ResourceConnection $resourceConnection
    ) {
        $this->resourceConnection = $resourceConnection;

    public function exportOrders($fromDate, $toDate)
    $connection = $this->resourceConnection->getConnection();

    // Prepare the query
    $select = $connection->select()
            ['so' => $this->resourceConnection->getTableName('sales_order')],
            ['entity_id', 'increment_id', 'created_at', 'status'] // Add other fields as needed
            ['sosh' => $this->resourceConnection->getTableName('sales_order_status_history')],
            'so.entity_id = sosh.parent_id',
            ['comments' => new \Zend_Db_Expr('GROUP_CONCAT(sosh.comment SEPARATOR "|")')] // Concatenate all comments
        ->where('so.status = ?', 'canceled')
        ->where('so.created_at >= ?', $fromDate)
        ->where('so.updated_at <= ?', $toDate)

    $orders = $connection->fetchAll($select);

    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="canceled_orders.csv"');

    $fp = fopen('php://output', 'w');

    // Write the header row to the CSV
    fputcsv($fp, ['Order ID', 'Increment ID', 'Created At', 'Status', 'Comments']); // Add other headers as needed

    // Write each order's data to the CSV
    foreach ($orders as $row) {
        fputcsv($fp, [
            $row['comments'] // Ensure to handle any necessary sanitization or formatting
             // Add more data according to your requirement. 


If you want to gain a deep understanding of how controllers and routes work – Read this article

Path: code/Lokesh/CancelorderExport/view/frontend/layout/exportorders_index_index.xml

<?xml version="1.0"?>
<page xmlns:xsi="" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
        <referenceContainer name="content">
            <block class="Lokesh\CancelorderExport\Block\ExportForm" name="export.form" template="Lokesh_CancelorderExport::export_form.phtml"/>

Path: app/code/Lokesh/CancelorderExport/Block/ExportForm.php


namespace Lokesh\CancelorderExport\Block;

use Magento\Framework\View\Element\Template;

class ExportForm extends Template
    protected function _prepareLayout()

Path: app/code/Lokesh/CancelorderExport/view/frontend/templates/export_form.phtml

<form action="<?= $block->getUrl('exportorders/index/index') ?>" method="GET">
    <div class="field">
        <label for="from_date">From Date:</label>
        <input type="date" id="from_date" name="from_date" required />
    <div class="field">
        <label for="to_date">To Date:</label>
        <input type="date" id="to_date" name="to_date" required />
    <div class="actions">
        <button type="submit" class="action submit primary">Export Orders</button>

Enable the module and update the database schema:
Run the following Magento CLI commands:

php bin/magento module:enable Lokesh_CancelorderExport

php bin/magento setup:upgrade

With this setup, when you navigates to the URL that corresponds to your route (for example,, it will be presented with the form. Upon filling out the form and submitting it, the data is sent via GET to your controller’s execute method, which then calls the exportOrders method with the provided dates.

Thank you for reading this Article! Feel free to share your thoughts or ask any questions in the comments section below and spread the word by sharing. Your engagement is appreciated!

Leave a reply

Your email address will not be published. Required fields are marked *

Cookies Notice

Our website use cookies. If you continue to use this site we will assume that you are happy with this.