Programmatically add a column to the Magento 2 sales order database table

Find out how to add a custom field to the sales_order database in Magento as an order attribute using extension attributes.

Mark Shust

Mark Shust

11 min read

So, you want to add a custom field to the sales_order database table now as an order attribute, eh? This can be a bit confusing to newcomers of Magento 2, because of the need to know multiple advanced concepts.

The answer lies with creating a custom database table, and then linking that table to the desired entity with extension attributes. Let's break this down from step 1 to make this as simple as possible.

The first rule of modifying

The first step of adding a field to a core database table? You don't.

This is because you Never Modify The Core™️, and this not only applies to code, but also to other areas of the platform including the database layer. So what is one to do?

Relationships to core tables

The only natural way is create your own custom database table is to create a relationship between it and the core table.

We will add the vendorname_modulename prefix to all of our database tables to avoid naming collisions with other tables. Since the table we want to create a relationship with is named sales_order, we will also suffix it with that name.

Given a vendor name of Acme and a module name of PoNumber, the name of our custom table becomes:

sales_order -> acme_ponumber_sales_order

Create a custom table

Now that we know the name of the table to create, let's go ahead and create it. The "attribute" we'd like to create will be for PO Numbers, or purchase order numbers, which are used by business customers for their internal purchase tracking needs.

This will be a 1:1 table, meaning that only one record can be created for each order. Put another way, every single order may only have one PO Number assigned to it.

app/code/Acme/PoNumber/etc/db_schema.xml
<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="acme_ponumber_sales_order" engine="innodb" comment="Sales orders">
        <!-- The standard auto-incrementing id of the table. -->
        <column xsi:type="int" name="id" padding="10" unsigned="true" nullable="false" identity="true" comment="Id"/>

        <!-- The reference to the order id. -->
        <column xsi:type="int" name="order_id" padding="10" unsigned="true" nullable="false" comment="Order id"/>

        <!-- The column we want to create is a free-form text field for a PO Number. -->
        <column xsi:type="text" name="po_number" comment="Po number"/>

        <!-- This is the primary key constraint for the database table. -->
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id"/>
        </constraint>

        <!-- Create a foreign key constraint to the related table to be related, which in this case is sales_order. -->
        <constraint xsi:type="foreign" referenceId="ACME_PONUMBER_SALES_ORDER_ORDER_ID_SALES_ORDER_ENTITY_ID" table="acme_ponumber_sales_order" column="order_id" referenceTable="sales_order" referenceColumn="entity_id" onDelete="CASCADE"/>
    </table>
</schema>

What's up with the long referenceId value of ACME_PONUMBER_SALES_ORDER_ORDER_ID_SALES_ORDER_ENTITY_ID?

  • ACME_PONUMBER_SALES_ORDER = current table
  • ORDER_ID = local column name of related foreign column
  • SALES_ORDER = foreign table reference
  • ENTITY_ID = foreign column to reference

This standard convention helps us find & debug indexes.

If we then inspect the table after running bin/magento setup:upgrade, we'll see the table has been successfully created with the proper indexes.

acme_ponumber_sales_order database table

Let's also create some quick placeholder data here to test out our newly found functionality:

PO Number populated

Remember to also generate your DB declaration whitelist file by running:

bin/magento setup:db-declaration:generate-whitelist --module-name=Acme_PoNumber

Running this command creates a file next to your db_schema.xml file named db_schema_whitelist.json. This JSON file ensures that whenever your db_schema.xml file is changed, the database schema will be dynamically updated along with it whenever bin/magento setup:upgrade is executed.

Create a model, resource model & collection

We have now populated the structure of our new database table, but the PHP files need to be setup so we can communicate with it. At the very least, this involves the creation of a model, resource model & collection.

Model

app/code/Acme/PoNumber/Model/SalesOrder.php
<?php declare(strict_types=1);

namespace Acme\PoNumber\Model;

use Magento\Framework\Model\AbstractModel;

class SalesOrder extends AbstractModel
{
    protected function _construct(): void
    {
        $this->_init(ResourceModel\SalesOrder::class);
    }
}

Resource Model

app/code/Acme/PoNumber/Model/ResourceModel/SalesOrder.php
<?php declare(strict_types=1);

namespace Acme\PoNumber\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class SalesOrder extends AbstractDb
{
    /** @var string Main table name */
    const MAIN_TABLE = 'acme_ponumber_sales_order';

    /** @var string Main table primary key field name */
    const ID_FIELD_NAME = 'id';

    protected function _construct(): void
    {
        $this->_init(self::MAIN_TABLE, self::ID_FIELD_NAME);
    }
}

Collection

app/code/Acme/PoNumber/Model/ResourceModel/SalesOrder/Collection.php
<?php declare(strict_types=1);

namespace Acme\PoNumber\Model\ResourceModel\SalesOrder;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
use Acme\PoNumber\Model\SalesOrder;

class Collection extends AbstractCollection
{
    protected function _construct(): void
    {
        $this->_init(SalesOrder::class, \Acme\PoNumber\Model\ResourceModel\SalesOrder::class);
    }
}

Now that our data model is complete, let's talk about how we can connect this database table to the core sales_order table.

About extension attributes

Now that we have created our custom table, we need a way to "link" the data within that table to the core table. This is done through a feature in Magento 2 known as extension attributes.

When we query the Magento API to get order details, we'll get something like this:

{
  "applied_rule_ids": "1",
  "base_currency_code": "USD",
  "base_discount_amount": 0,
  "base_discount_invoiced": 0,
  "base_grand_total": 36.39,
  "base_discount_tax_compensation_amount": 0,
  "base_discount_tax_compensation_invoiced": 0,
  "base_shipping_amount": 5,
  "base_shipping_discount_amount": 0,
  "base_shipping_discount_tax_compensation_amnt": 0,
  "base_shipping_incl_tax": 5,
  "base_shipping_invoiced": 5,
  ...
  "extension_attributes": {
    ...
    "payment_additional_info": [
      {
        "key": "method_title",
        "value": "Check \/ Money order"
      }
    ],
    "applied_taxes": [
      {
        "code": "US-MI-*-Rate 1",
        "title": "US-MI-*-Rate 1",
        "percent": 8.25,
        "amount": 2.39,
        "base_amount": 2.39
      }
    ],
    ...
  }
}

All of this info is in a basic structure which we do not want to tamper with, because there may be third-party tools that rely on this schema. That said, notice the last root property named extension_attributes. This is a place that Magento created for third-parties, including us, to add our own custom properties.

The internal Magento API also works similarly. Since the data will be placed within an extension_attributes property, rather than calling $order->getPoNumber() we would call $order->getExtensionAttributes()->getPoNumber().

In order to be able to make this work though, we must first add the PO Number data to the Order model by using a plugin.

Create a plugin

Let's create a plugin, which will allow us to easily add data to queries made to the order repository.

app/code/Acme/PoNumber/etc/di.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Sales\Api\OrderRepositoryInterface">
        <plugin name="acme_ponumber_add_po_number_to_sales_order" type="Acme\PoNumber\Plugin\AddPoNumberToSalesOrder"/>
    </type>
</config>
app/code/Acme/PoNumber/Plugin/AddPoNumberToSalesOrder.php
<?php declare(strict_types=1);

namespace Acme\PoNumber\Plugin;

use Acme\PoNumber\Model\ResourceModel\SalesOrder\Collection;
use Acme\PoNumber\Model\ResourceModel\SalesOrder\CollectionFactory;
use Magento\Sales\Api\OrderRepositoryInterface;

class AddPoNumberToSalesOrder
{
    private CollectionFactory $acmeSalesOrderCollectionFactory;

    /**
     * AddPoNumberToSalesOrder constructor.
     * @param CollectionFactory $acmeSalesOrderCollectionFactory
     */
    public function __construct(
        CollectionFactory $acmeSalesOrderCollectionFactory
    ) {
        $this->acmeSalesOrderCollectionFactory = $acmeSalesOrderCollectionFactory;
    }

    /**
     * @param OrderRepositoryInterface $subject
     * @param $result
     * @return mixed
     */
    public function afterGet(
        OrderRepositoryInterface $subject,
        $result
    ) {
        // We must first grab the record from our custom database table by the order id.

        /** @var Collection $acmeSalesOrder */
        $acmeSalesOrderCollection = $this->acmeSalesOrderCollectionFactory->create();
        $acmeSalesOrder = $acmeSalesOrderCollection
            ->addFieldToFilter('order_id', $result->getId())
            ->getFirstItem();

        // Then, we get the extension attributes that are currently assigned to this order.
        $extensionAttributes = $result->getExtensionAttributes();

        // We then call "setData" on the property we want to set, wtih the value from our custom table.
        $extensionAttributes->setData('po_number', $acmeSalesOrder->getData('po_number'));

        // Then, just re-set the extension attributes containing the newly added data...
        $result->setExtensionAttributes($extensionAttributes);

        // ...and finally, return the result.
        return $result;
    }

    /**
     * @param OrderRepositoryInterface $subject
     * @param $result
     * @return mixed
     */
    public function afterGetList(
        OrderRepositoryInterface $subject,
        $result
    ) {
        // We do the same thing here, and can save some time by passing the logic to afterGet.
        foreach ($result->getItems() as $order) {
            $this->afterGet($subject, $order);
        }

        return $result;
    }
}

After setting this up, let's make another curl request.

We didn't touch base on how to make CURL requests to the Magento API, but put simply:

Get a token by passing in your username/password as Basic Auth:

  • POST https://magento.test/rest/V1/integration/admin/token

Then get the order details by passing in the Bearer Token to:

  • GET https://magento.test/rest/V1/orders/1

The call will succeed, but the po_number will still not show in the resulting JSON object. Why? This happens because data added to extension attributes must also be defined within an XML file.

Create an extension_attributes.xml file

We're nearing the last step here, and that is to define our newly created attribute inside an extension_attributes.xml file.

This is quite simple. The value for "for" will be the interface we wish to add data to, in this case OrderInterface, and then we define the attribute code and type of attribute to add:

app/code/Acme/PoNumber/etc/extension_attributes.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Api/etc/extension_attributes.xsd">
    <extension_attributes for="Magento\Sales\Api\Data\OrderInterface">
        <attribute code="po_number" type="string"/>
    </extension_attributes>
</config>

Upon adding this file, we will now see the po_number added to the resulting JSON object after making our API call.

Result of REST API call to get Magento order details

Now that the extension_attributes.xml has been created, we can also change setData() to use a magic method if we wish, which would be setPoNumber(). We don't need to do this, but this is also to show how the extensibility works in Magento.

$extensionAttributes->setPoNumber($acmeSalesOrder->getData('po_number');

You will now be able to use the following line in any other custom code as you wish, and the related PO Number will always be returned.

$order->getExtensionAttributes()->getPoNumber();

What next?

Now that you have your custom extension attribute setup, try experimenting with new things. For example, you can add a new column to an admin grid, create an admin form which saves the value of this extension attribute, or create a data repository for your data model and update your plugin to use that repository instead.

Build something interesting? Join the M.academy Slack group and show it off. I'm very interested to see what you come up with.

Want to reference the code in this article? Check it out on GitHub.

Like to learn about Magento?

Get notified about new Magento blog posts.

...or use a blog feed reader? Grab the RSS Feed URL