Show data from two different tables in Magento Grid.php

Question

I am a bit stuck trying to show data from two different tables in a Magento Grid module.

I have created the Grid to show order information and have add a join in order to show data from sales_flat_order_payment table. However, since adding the join I now get a error

Fatal error: Allowed memory size of 524288000 bytes exhausted (tried to allocate 37531648 bytes)

I only wish to display payment type and Cc Last4 in the Grid

Here is what I have:

<?php

class Company_Reports_Block_Adminhtml_Order_Grid extends Mage_Adminhtml_Block_Widget_Grid
{
    public function __construct()
    {
        parent::__construct();
        $this->setId('sales_order_grid');
        $this->setUseAjax(true);
        $this->setDefaultSort('created_at');
        $this->setDefaultDir('DESC');
        $this->setSaveParametersInSession(true);
    }

    /**
     * Retrieve collection class
     *
     * @return string
     */
    protected function _getCollectionClass()
    {
        return 'sales/order_grid_collection';
    }

    protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel($this->_getCollectionClass());
        $collection->getSelect()->joinLeft(
            'sales_flat_order_payment',
            'sales_flat_order_payment.parent_id = main_table.entity_id',
            array('method')
        );

        // this is custom function that retrieves an array
        // with payment option and its label
        $metode = $this->getActivePaymentMethods();

        // we get labels of payment options
        foreach ($collection as $afn) {
            foreach ($metode as $method) {
                if ($method['value'] == $afn['method']) {
                    $afn->setData('method', $method['label']);
                }
            }
        }

        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

    protected function _prepareColumns()
    {
        $this->addColumn('increment_id', array(
            'header' => Mage::helper('reports')->__('Order #'),
            'align' =>'right',
            'width' => '50px',
            'type' => 'number',
            'index' => 'increment_id',
        ));

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

        $this->addColumn('method', array(
            'header' => Mage::helper('reports')->__('Payment Type'),
            'index' => 'method',
        ));

        $this->addColumn('pseudo_account_or_cc_no', array(
            'header' => Mage::helper('reports')->__('Cc Last 4'),
            'index' => 'pseudo_account_or_cc_no',
        ));

        $this->addColumn('ops_alias', array(
            'header' => Mage::helper('reports')->__('Alias'),
            'index' => 'ops_alias',
        ));

        $this->addColumn('grand_total', array(
            'header' => Mage::helper('reports')->__('Total'),
            'index' => 'grand_total',
        ));

        $this->addColumn('total_paid', array(
            'header' => Mage::helper('reports')->__('Total Paid'),
            'index' => 'total_paid',
        ));

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

        $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'=>'adminhtml/sales_order/view'),
                        'field'   => 'order_id'
                    )
                ),
                'filter'    => false,
                'sortable'  => false,
                'index'     => 'stores',
                'is_system' => true,
            ));
        }

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

        return parent::_prepareColumns();
    }
}

Can someone suggest what I have done incorrectly please?

Answer 1

You can try with following code.

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $collection->getSelect()->joinLeft(
        'sales_flat_order_payment',
        'sales_flat_order_payment.parent_id = main_table.entity_id',
        array('method','cc_last4')
    );
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

protected function _prepareColumns()
{
    -------

    $this->addColumn('method', array(
        'header'=> Mage::helper('sales')->__('Method'),
        'width' => '80px',
        'type'  => 'text',
        'index' => 'method',
    ));

    $this->addColumn('cc_last4', array(
        'header'=> Mage::helper('sales')->__('cc_last4'),
        'width' => '80px',
        'type'  => 'text',
        'index' => 'cc_last4',
    ));

    -------
}

and please increase your memory size from php.ini file.

like memory_limit = 1024M.

Thanks.