Magento – Magento 2 add new column to existing table on a running webshop

databasemagento2

I know there are few posts regarding how to add new column to existing table in Magento 2. My concern is if I already have a webshop that is running, can I still add a new column to a table?

for example, I want to add a new column to the table sales_order_item in a webshop database that already has a lot of data existing.

Best Answer

You need to use below cod in your setup script of your custom module

<?php

namespace Vendor\Modulename\Setup;


use Magento\Sales\Setup\SalesSetupFactory;
use Magento\Eav\Model\Entity\Attribute\SetFactory as AttributeSetFactory;
use Magento\Framework\Setup\InstallDataInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;

/**
 * InstallData
 *
 * @codeCoverageIgnore
 */
class InstallData implements InstallDataInterface
{


    /**
     * SalesSetupFactory
     *
     * @var SalesSetupFactory
     */
    protected $salesSetupFactory;



    /**
     * Constructor
     * @param SalesSetupFactory $salesSetupFactory
     */
    public function __construct(
        SalesSetupFactory $salesSetupFactory
    ) {
        $this->salesSetupFactory = $salesSetupFactory;
    }


    /**
     * Install
     *
     * @param ModuleDataSetupInterface $setup
     * @param ModuleContextInterface $context
     */
    public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {

        /** @var \Magento\Sales\Setup\SalesSetup $salesInstaller */
        $salesInstaller = $this->salesSetupFactory->create(['resourceName' => 'sales_setup', 'setup' => $setup]);

        $salesInstaller->addAttribute('order_item', 'test', ['type' => \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER, 'length'=> 10, 'visible' => false,'nullable' => true,]);
    }

}

Related Solutions

Magento 1.7 – How to Add a Column to Quote Item Table

In order to add column to sales_flat_quote_item you will have to write an upgrade script for that follow below steps.

Create a module or use your existing custom module.

Modify your config.xml to add a setup resource with class Mage_Sales_Model_Resource_Setup:

<global>
    <resources>
        <your_module_setup>
            <setup>
                <module>Your_Module</module>
                <class>Mage_Sales_Model_Resource_Setup</class>
            </setup>
       </your_module_setup>
     </resources>
 </global>

Create a directory data/your_module_setup in your module

Create a data-install or data-upgrade script containing the following code:

$installer = $this;

$installer->startSetup();

$installer->addAttribute(
    'quote_item',  /* order, quote, order_item, quote_item */
    'your_attribute_code', 
    array(
        'type' => 'varchar', /* int, varchar, text, decimal, datetime */
        'nullable' => false, /* default true */
        'grid' => false, /* or true if you wan't use this attribute on orders grid page */
    )
);

$installer->endSetup();

Magento – How to Add New Column to Existing Table Programmatically

sales_flat_order is the full name of the table and so you have to use alias in $installer->getTable()

In $installer->getTable() parameter like module_alias/table_alias.

In that case try with

$installer->getTable('sales/order')

When you write this it will return table name sales_flat_order

because

module_alias = sales

table_alias = order

EDIT

You can use below script to add new column. It works fine in my system

$installer = $this;
$installer->startSetup();

$installer->getConnection()
->addColumn($installer->getTable('sales/order'),'custom_value', array(
    'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
    'nullable'  => false,
    'length'    => 255,
    'after'     => null, // column name to insert new column after
    'comment'   => 'Title'
    ));   
$installer->endSetup();

I am using Varien_Db_Ddl_Table::TYPE_TEXT insted of Varien_Db_Ddl_Table::TYPE_VARCHAR because TYPE_VARCHAR is deprecated

You can check @ Varien_Db_Adapter_Pdo_Mysql::$_ddlColumnTypes

And If you specify type TYPE_TEXT but set length to lets say 255 Magento will create a MySQL column of VARCHAR type.

Best Answer

Related Solutions

Magento 1.7 – How to Add a Column to Quote Item Table

Magento – How to Add New Column to Existing Table Programmatically

Related Topic