0

How to add sales_flat_order to the collection ?

     {
        $collection = Mage::getResourceModel($this->_getCollectionClass());
  $collection->getSelect()->join('sales_flat_order_address',
            'main_table.entity_id = sales_flat_order_address.parent_id',
        array('telephone','city','postcode','country_id'))
        ->where("sales_flat_order_address.address_type =  'billing'");
        $this->setCollection($collection);
        return parent::_prepareCollection();

    }

        $this->addColumn('total_refunded', array(
    'header' => Mage::helper('sales')->__('Total rembourse'),
    'index' => 'total_refunded',
    'width' => '25px',
    'align' => 'center',
));

I want to have total_refunded column in my grid from sales_flat_order

2
  • what you are trying to achive? Nov 27, 2019 at 10:03
  • Add the amount refunded on the b / o sales orders
    – Gaet
    Nov 27, 2019 at 10:11

2 Answers 2

1

Replace with below collection code:

protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel($this->_getCollectionClass());
  $collection->getSelect()
            ->join('sales_flat_order_address',
            'main_table.entity_id = sales_flat_order_address.parent_id',
        array('telephone','city','postcode','country_id'))
            ->join('sales_flat_order',
            'main_table.entity_id = sales_flat_order.entity_id',
        array('total_refunded'))
        ->where("sales_flat_order_address.address_type =  'billing'");
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

Also, this column code:

$this->addColumn('total_refunded', array(
        'header' => Mage::helper('sales')->__('Total remboursé'),
        'index' => 'total_refunded',
        'filter_index'=>'main_table.increment_id',
        'width' => '25px',
        'align' => 'center',
    ));

EDITED ANSWER:

There is an issue while filtering your grid, and the issue is with increment_id. compare your add column code with this below code:

$this->addColumn('increment_id', array(
            'header'    => Mage::helper('sales')->__('Credit Memo #'),
            'index'     => 'increment_id',
            'type'      => 'text',
            'filter_index'=>'main_table.increment_id',
        ));
8
  • There has been an error processing your request SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'increment_id' in where clause is ambiguous, query was: SELECT COUNT(*) FROM sales_flat_order_grid AS main_table INNER JOIN sales_flat_order_address ON main_table.entity_id = sales_flat_order_address.parent_id INNER JOIN sales_flat_order ON main_table.entity_id = sales_flat_order.entity_id WHERE (sales_flat_order_address.address_type = 'billing') AND (increment_id LIKE '%33425%')
    – Gaet
    Nov 27, 2019 at 10:15
  • Check my edited answer section Nov 27, 2019 at 10:24
  • Thanks, I do not have an increment column id in block / sales / order / grid.php
    – Gaet
    Nov 27, 2019 at 10:29
  • add this line filter_index'=>'main_table.increment_id', in your primary column. Nov 27, 2019 at 11:31
  • Thanks. As soon as I put a filter "date of purchase" I have a query error. I copy the complete file below
    – Gaet
    Nov 27, 2019 at 13:07
0

protected function _prepareCollection() { $collection = Mage::getResourceModel($this->_getCollectionClass()); $collection->getSelect() ->join('sales_flat_order_address', 'main_table.entity_id = sales_flat_order_address.parent_id', array('telephone','city','postcode','country_id')) ->join('sales_flat_order', 'main_table.entity_id = sales_flat_order.entity_id', array('total_refunded')) ->where("sales_flat_order_address.address_type = 'billing'"); $this->setCollection($collection); return parent::_prepareCollection(); }

protected function _prepareColumns()
{

    $this->addColumn('real_order_id', array(
        'header'=> Mage::helper('sales')->__('Order #'),
        'width' => '80px',
        'type'  => 'text',
        'index' => 'increment_id',
        'filter_index'=>'main_table.increment_id',
    ));

    if (!Mage::app()->isSingleStoreMode()) {
        $this->addColumn('store_id', array(
            'header'    => Mage::helper('sales')->__('Purchased From (Store)'),
            'index'     => 'store_id',
            'type'      => 'store',
            'store_view'=> true,
            'display_deleted' => true,
        ));
    }

    $this->addColumn('created_at', array(
        'header' => Mage::helper('sales')->__('Purchased On'),
        'index' => 'created_at',
        'type' => 'datetime',
        'width' => '100px',
    ));

    $this->addColumn('billing_name', array(
        'header' => Mage::helper('sales')->__('Bill to Name'),
        'index' => 'billing_name',
    ));

    $this->addColumn('shipping_name', array(
        'header' => Mage::helper('sales')->__('Ship to Name'),
        'index' => 'shipping_name',
    ));

    $this->addColumn('base_grand_total', array(
        'header' => Mage::helper('sales')->__('G.T. (Base)'),
        'index' => 'base_grand_total',
        'type'  => 'currency',
        'currency' => 'base_currency_code',
    ));

    $this->addColumn('grand_total', array(
        'header' => Mage::helper('sales')->__('G.T. (Purchased)'),
        'index' => 'grand_total',
        'type'  => 'currency',
        'currency' => 'order_currency_code',
    ));

    $this->addColumn('status', array(
        'header' => Mage::helper('sales')->__('Status'),
        'index' => 'status',
        'type'  => 'options',
        'width' => '70px',
        'options' => Mage::getSingleton('sales/order_config')->getStatuses(),
    ));

    if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/view')) {
        $this->addColumn('action',
            array(
                'header'    => Mage::helper('sales')->__('Action'),
                'width'     => '50px',
                'type'      => 'action',
                'getter'     => 'getId',
                'actions'   => array(
                    array(
                        'caption' => Mage::helper('sales')->__('View'),
                        'url'     => array('base'=>'*/sales_order/view'),
                        'field'   => 'order_id',
                        'data-column' => 'action',
                    )
                ),
                'filter'    => false,
                'sortable'  => false,
                'index'     => 'stores',
                'is_system' => true,
        ));
    }

$this->addColumn('country_id', array(
    'header' => Mage::helper('sales')->__('Pays'),
    'index' => 'country_id',
    'width' => '25px',
    'align' => 'center',
    'filter_index' => 'sales_flat_order_address.country_id',
));

$this->addColumn('total_refunded', array( 'header' => Mage::helper('sales')->__('Total remboursé'), 'index' => 'total_refunded', 'filter_index'=>'main_table.increment_id', 'width' => '25px', 'align' => 'center', ));

    $this->addRssList('rss/order/new', Mage::helper('sales')->__('New Order RSS'));

    $this->addExportType('*/*/exportCsv', Mage::helper('sales')->__('CSV'));
    $this->addExportType('*/*/exportExcel', Mage::helper('sales')->__('Excel XML'));

    return parent::_prepareColumns();
}``  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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