How can I add a column to an existing model in Magento after an existing field?

Question

I am creating a Magento module that makes use of a new column in sales/quote.

I know how to use addAttribute and addColumn respectively, but I can't figure out how to put the SQL equivalent of AFTER column from the entity model classes.

Also, do I need to always addAttribute to the corresponding model after addColumn to a table?

Answer 1

To add a new column to an existing table in the database, we will be dealing with 1 file and changing 2 database tables. In my example, I’m going to add a new column called my_new_column to the “Sales Order” object.

Let’s get started!

First open the following file:

app/code/local/Mage/Sales/Model/Entity/Setup.php - This an array full of attributes to use to keep track of sale data

You’ll see something like this:

public function getDefaultEntities(){

  return array(
      'quote'=>array(
          'entity_model'  => 'sales/quote',
          'table'         => 'sales/quote',
          'attributes' => array(
              'entity_id'         => array('type'=>'static'),
              'is_active'         => array('type'=>'static'),
              'store_id'          => array('type'=>'static'),
     ...
      'order' => array(
          'entity_model'      => 'sales/order',
          'table'=>'sales/order', //The table name, in this case sales_order
          'increment_model'=>'eav/entity_increment_numeric',
          'increment_per_store'=>true,
          'backend_prefix'=>'sales_entity/order_attribute_backend',
          'attributes' => array(
              'entity_id' => array(
                  'type'=>'static',
                 'backend'=>'sales_entity/order_attribute_backend_parent'
              ),
              'store_id'  => array('type'=>'static'),
              'store_name' => array('type'=>'varchar'),
              'remote_ip' => array(),
              'status'    => array('type'=>'varchar'),
              'state'     => array('type'=>'varchar'),
              'hold_before_status' => array('type'=>'varchar'),
              'hold_before_state'  => array('type'=>'varchar'),
              'my_new_column'      => array('type'=>'static'),  //**Add this**

This file contains the variables each table should contain. Do a search for ‘table’⇒’ to find which file you’re dealing with. In my case I want to add it to the ‘sales_order’ table, so I’ll search for ‘sales/order’. I’ll simply add ‘my_new_column’ to the list below.

Now, our code is all set up and ready to rock. However, we still need to add this attribute to the eav_attribute table in your mysql database.

Log into your database and enter the 2 commands:

alter table sales_order add my_new_column decimal(12,4) DEFAULT NULL;

insert into eav_attribute('entity_type_id','attribute_code','attribute_model','backend_model','backend_type','backend_table','frontend_model','frontend_input','frontend_input_renderer','frontend_label','frontend_class','source_model','is_global','is_visible','is_required','is_user_defined','default_value','is_searchable','is_filterable','is_comparable','is_visible_on_front','is_html_allowed_on_front','is_unique','is_used_for_price_rules','is_filterable_in_search','used_in_product_listing','used_for_sort_by','is_configurable','apply_to','position','note','is_visible_in_advanced_search' ) values(11, 'my_new_column', null, '', 'static', '', '', 'text', '','',null, '', 1,1,1,0,'',0,0,0,0,0,0,1,0,0,0,1,'',0,'',0);

Be sure to change ‘my_new_column’ to whatever name you’d like. In the alter table, be sure to set the appropriate column type. Remember, it’s varchar for strings.

And you’re finished!

Now to retrieve the data you call getMyNewColumn(). To save data to this column you call setMyNewColumn( $new_data_to_set ).