This solution shows how to add a custom column to the sales order grid, but to add a custom column you need to add the column via XML, the problem is i need to read data collection to get available column data and add it as a custom column in sales order grid, in conclusion, I want to add multiple columns which will be generated dynamically from database collection data, is there any way to do this?
Magento – Add custom dynamic column in sales order grid Magento 2
dynamic rowsgridmagento2order-grid
Related Solutions
This is a duplicate of the question Add Column to a grid (observer) - Column ‘store_id’ in where clause is ambiguous issue, here I explained how to add a column to the sales/order grid the right way (without file copy or a rewrite).
The sales/order grid uses the table sales_flat_order_grid to display its information, this is done to list all orders without adding joins and speeding-up the loading. In order to add a column to the grid you would need to add a field to this table and update the information in that field when an order is created/updated. Below the steps needed to add this to the grid:
I called the extension Example_SalesGrid, but you can change it to your own needs.
Let's start by creating the module init xml in /app/etc/modules/Example_SalesGrid.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!--
Module bootstrap file
-->
<config>
<modules>
<Example_SalesGrid>
<active>true</active>
<codePool>community</codePool>
<depends>
<Mage_Sales />
</depends>
</Example_SalesGrid>
</modules>
</config>
Next we create our module config xml in /app/code/community/Example/SalesGrid/etc/config.xml:
<?xml version="1.0" encoding="UTF-8"?>
<config>
<modules>
<Example_SalesGrid>
<version>0.1.0</version> <!-- define version for sql upgrade -->
</Example_SalesGrid>
</modules>
<global>
<models>
<example_salesgrid>
<class>Example_SalesGrid_Model</class>
</example_salesgrid>
</models>
<blocks>
<example_salesgrid>
<class>Example_SalesGrid_Block</class>
</example_salesgrid>
</blocks>
<events>
<!-- Add observer configuration -->
<sales_order_resource_init_virtual_grid_columns>
<observers>
<example_salesgrid>
<model>example_salesgrid/observer</model>
<method>addColumnToResource</method>
</example_salesgrid>
</observers>
</sales_order_resource_init_virtual_grid_columns>
</events>
<resources>
<!-- initialize sql upgrade setup -->
<example_salesgrid_setup>
<setup>
<module>Example_SalesGrid</module>
<class>Mage_Sales_Model_Mysql4_Setup</class>
</setup>
</example_salesgrid_setup>
</resources>
</global>
<adminhtml>
<layout>
<!-- layout upgrade configuration -->
<updates>
<example_salesgrid>
<file>example/salesgrid.xml</file>
</example_salesgrid>
</updates>
</layout>
</adminhtml>
</config>
Now we create the sql upgrade script in /app/code/community/Example/SalesGrid/sql/example_salesgrid_setup/install-0.1.0.php:
<?php
/**
* Setup scripts, add new column and fulfills
* its values to existing rows
*
*/
$this->startSetup();
// Add column to grid table
$this->getConnection()->addColumn(
$this->getTable('sales/order_grid'),
'customer_city',
'varchar(255) DEFAULT NULL'
);
// Add key to table for this field,
// it will improve the speed of searching & sorting by the field
$this->getConnection()->addKey(
$this->getTable('sales/order_grid'),
'customer_city',
'customer_city'
);
// Now you need to fullfill existing rows with data from address table
$select = $this->getConnection()->select();
$select->join(
array('address'=>$this->getTable('sales/order_address')),
$this->getConnection()->quoteInto(
'address.parent_id = order_grid.entity_id AND address.address_type = ?',
Mage_Sales_Model_Quote_Address::TYPE_BILLING
),
array('customer_city' => 'city')
);
$this->getConnection()->query(
$select->crossUpdateFromSelect(
array('order_grid' => $this->getTable('sales/order_grid'))
)
);
$this->endSetup();
Next we create the layout update file in /app/design/adminhtml/default/default/layout/example/salesgrid.xml:
<?xml version="1.0"?>
<layout>
<!-- main layout definition that adds the column -->
<add_order_grid_column_handle>
<reference name="sales_order.grid">
<action method="addColumnAfter">
<columnId>customer_city</columnId>
<arguments module="sales" translate="header">
<header>Customer City</header>
<index>customer_city</index>
<type>text</type>
<width>200</width>
</arguments>
<after>shipping_name</after>
</action>
</reference>
</add_order_grid_column_handle>
<!-- order grid action -->
<adminhtml_sales_order_grid>
<!-- apply the layout handle defined above -->
<update handle="add_order_grid_column_handle" />
</adminhtml_sales_order_grid>
<!-- order grid view action -->
<adminhtml_sales_order_index>
<!-- apply the layout handle defined above -->
<update handle="add_order_grid_column_handle" />
</adminhtml_sales_order_index>
</layout>
The last file needed is needed to update the data from a table other than sales/order (sales_flat_order). All fields in sales/order_grid matching the column name from sales/order is automatically updated in the sales/order_grid table.
/app/code/community/Example/SalesGrid/Model/Observer.php:
<?php
/**
* Event observer model
*
*
*/
class Example_SalesGrid_Model_Observer {
public function addColumnToResource(Varien_Event_Observer $observer) {
// Only needed if you use a table other than sales/order (sales_flat_order)
$resource = $observer->getEvent()->getResource();
$resource->addVirtualGridColumn(
'customer_city',
'sales/order_address',
array('billing_address_id' => 'entity_id'),
'city'
);
}
}
This code is based on the example from http://www.ecomdev.org/2010/07/27/adding-order-attribute-to-orders-grid-in-magento-1-4-1.html
Hope the example above solves your problem.
As Magento save coupon code
on Sales_flat_Order table so you need to MySQL join with Sales_flat_order
table
Add sales_flat_order table to collection.In magento, sales_flat_order and sales_flat_order_grid table related with Flat Grid order table entity_id and sales_flat_order table entity_id
.
$select = $collection->getSelect();
$select->joinLeft(array('order' => Mage::getModel('core/resource')->getTableName('sales/order')), 'order.entity_id=main_table.entity_id',
array('coupon_code' => 'coupon_code'))
Rewrite class then add columns to show Coupon:
<?php
/**
* Sales Order Class
*
* @author Amit Bera
*/
class Amit_CustomOrderGrid_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid
{
protected function _prepareColumns()
{
$this->addColumn('coupon_code', array(
'header' => Mage::helper('sales')->__('Color Name'),
'align' =>'left',
'width' => '50px',
'index' => 'coupon_code',
'filter_index' => 'order.coupon_code'
));
parent::_prepareColumns();
return Mage_Adminhtml_Block_Widget_Grid::_prepareColumns();
}
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass());
$collection->getSelect()->joinLeft(array('order' => Mage::getModel('core/resource')->getTableName('sales/order')),
'order.entity_id=main_table.entity_id',array('coupon_code' => 'coupon_code'))
$this->setCollection($collection);
// return parent::_prepareCollection();
return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();
}
}
?>
Best Answer
Make new sales_order_grid.xml file in
app/code/YourVendor/YourModule/view/adminhtml/ui_component/
and try to do there something like this:That
class
property inside column tag allows you to write your very own renderer that will take care of the custom column. You can do it simply like that:Let me know if it's what you've wanted.