0

How to add addition column in sales_order table using db_schema or in Magento2.3.1 best way?

5
  • use db_schema which is the easiest way to add columns or to delete column no need of script upgrade..
    – aravind
    Jul 9 '19 at 9:19
  • but whole sales_order table need to define again in my script? Jul 9 '19 at 9:20
  • Check Ref: codextblog.com/magento-2/… Jul 9 '19 at 9:21
  • @RakeshDonga First try to understand my requirement. I am not asking for grid Jul 9 '19 at 9:22
  • @RutveeSojitra is it working for you? Jul 15 '19 at 4:42
3

Create a new module depending on Magento_Sales and create a db_schema.xml file to declare the to be created a column in it. Once it is declared, generate the db_schema_whitelist.json file and run setup:upgrade. (db_schema.xml file is introduced in magento2.3 to run DB command to create, update tables and columns and keys. There is no need not for setup install scripts for a table but yes to populate columns you still need to use the old methodology of setup upgradeSchema)

etc/module.xml

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
<module name="FirstVendor_FirstModule"  setup_version="1.1.12" />
 <sequence>
        <module name="Magento_Sales"/>
 </sequence>

registration.php

use \Magento\Framework\Component\ComponentRegistrar;

ComponentRegistrar::register(ComponentRegistrar::MODULE, 'FirstVendor_FirstModule', __DIR__);

etc/db_schema.xml

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="sales_order" resource="default" engine="innodb">
        <column xsi:type="varchar" name="custom_column" length="100" unsigned="false" nullable="false" identity="false"
                 comment="Custom Name"/>
    </table>
</schema>

Finally, run this below command to generate whitelist (Even though it's not mandatory. Without this, it will create column too but as a standard approach recommended.)

php bin/magento setup:db-declaration:generate-whitelist

Once above command is executed, run the setup:upgrade to make it in effect.

php bin/magento setup:upgrade

Let me know if this helps.

1
  • Perform the same above steps for change quote column customer_note column type from varchar to text. But I got Invalid Document Element 'column', attribute 'length': The attribute 'length' is not allowed. error while run setup:upgrade command. This error comes because of in magento quote module db_schema.xml file has this customer_note definition and it's type varchar and has a length. If remove length from that definition line then code working without any error Feb 21 '20 at 19:40
1

Try to use this below code :

app/code/RH/SalesCustom/etc/module.xml

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="RH_SalesCustom" >
        <sequence>
            <module name="Magento_Sales"/>
        </sequence>
    </module>
</config>

app/code/RH/SalesCustom/registration.php

<?php

use Magento\Framework\Component\ComponentRegistrar;

ComponentRegistrar::register(
    ComponentRegistrar::MODULE,
    'RH_SalesCustom',
    __DIR__
);

app/code/RH/SalesCustom/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="sales_order">
        <column xsi:type="int" name="custom_sales_id" padding="10" unsigned="true" nullable="false"
                comment="Custom Sales ID"/>
        <constraint xsi:type="foreign" referenceId="SALES_ORDER_REFERRED_BY_SALES_ORDER_ENTITY_ID" table="sales_order"
                    column="custom_sales_id" referenceTable="sales_order" referenceColumn="entity_id" onDelete="CASCADE"/>
    </table>
</schema>
  • custom_sales_id : your custom column name

Then run following command to generate db_schema_whitelist.json

php bin/magento setup:db-declaration:generate-whitelist

Then, upgrade command execute :

php bin/magento s:up

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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