Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 3
Showing payment method on the Sales->Orders grid in admin
 
Gabriel Queiroz
Sr. Member
 
Total Posts:  135
Joined:  2008-05-28
Brasília, Brazil
 

Hi all,

I’m trying to add a column to the admin orders grid showing which payment method was used by the customer.

This is the collection that creates the query for the grid (file /app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php, method _prepareCollection()):

$collection Mage::getResourceModel('sales/order_collection')
            ->
addAttributeToSelect('*')
            ->
joinAttribute('billing_firstname''order_address/firstname''billing_address_id'null'left')
            ->
joinAttribute('billing_lastname''order_address/lastname''billing_address_id'null'left')
            ->
joinAttribute('shipping_firstname''order_address/firstname''shipping_address_id'null'left')
            ->
joinAttribute('shipping_lastname''order_address/lastname''shipping_address_id'null'left')
            ->
addExpressionAttributeToSelect('billing_name',
                
'CONCAT({{billing_firstname}}, " ", {{billing_lastname}})',
                array(
'billing_firstname''billing_lastname'))
            ->
addExpressionAttributeToSelect('shipping_name',
                
'CONCAT({{shipping_firstname}}, " ", {{shipping_lastname}})',
                array(
'shipping_firstname''shipping_lastname'));

In order to add the payment method, it is necessary to create a join for the order_payment table (this could be absolutely wrong, but as far as i understood the eav table structure this would do it). It would be necessary to create a join between order and order_payment, and after this, use the order_payment/entity_id attribute to get the right value for order_payment/method. At this point, I can’t even get the first value (order_payment/entity_id). This is the code I’m trying to use

$collection Mage::getResourceModel('sales/order_collection')
            ->
addAttributeToSelect('*')
            ->
joinAttribute('billing_firstname''order_address/firstname''billing_address_id'null'left')
            ->
joinAttribute('billing_lastname''order_address/lastname''billing_address_id'null'left')
            ->
joinAttribute('shipping_firstname''order_address/firstname''shipping_address_id'null'left')
            ->
joinAttribute('shipping_lastname''order_address/lastname''shipping_address_id'null'left')
            ->
joinAttribute('order_payment_id_for_join''order_payment/entity_id''entity_id''parent_id''left' )
            
//->joinAttribute('payment_method', 'quote_payment/method', 'quote_id', 'entity_id', 'left' )
            //->joinAttribute('payment_method', 'quote_payment/method', 'quote_id', null, 'left' )
            
->addExpressionAttributeToSelect('billing_name',
                
'CONCAT({{billing_firstname}}, " ", {{billing_lastname}})',
                array(
'billing_firstname''billing_lastname'))
            ->
addExpressionAttributeToSelect('shipping_name',
                
'CONCAT({{shipping_firstname}}, " ", {{shipping_lastname}})',
                array(
'shipping_firstname''shipping_lastname'));

This is returning me the order/entity_id attribute. Where am I wrong here? I’m beginning to think that it might be impossible to get this third level join made using the collections.

Any help would be much appreciated!

Cheers,

 
Magento Community Magento Community
Magento Community
Magento Community
 
Gabriel Queiroz
Sr. Member
 
Total Posts:  135
Joined:  2008-05-28
Brasília, Brazil
 

Hi,

I’ve just got to do it in the most ugly and less elegant way possible. here it is:

Same file and method from the post above:

$quotePaymentWhere "({{table}}.entity_type_id = 10)";
        
$paymentMethodWhere "({{table}}.attribute_id = 218)";
        
        
$collection Mage::getResourceModel('sales/order_collection')
            ->
addAttributeToSelect('*')
            
/*** from here **/

            
->joinAttribute('quote_id_for_join''quote/entity_id''quote_id'null'left' )
            ->
joinTable('sales/quote_entity''parent_id=quote_id', array( 'quote_payment_id_for_join' => 'entity_id' ) , $quotePaymentWhere'left' )
            ->
joinTable('sales_quote_entity_varchar''entity_id=quote_payment_id_for_join', array( 'payment_method' => 'value' ) , $paymentMethodWhere'left' )

/*** to here **/

            
->joinAttribute('billing_firstname''order_address/firstname''billing_address_id'null'left')
            ->
joinAttribute('billing_lastname''order_address/lastname''billing_address_id'null'left')
            ->
joinAttribute('shipping_firstname''order_address/firstname''shipping_address_id'null'left')
            ->
joinAttribute('shipping_lastname''order_address/lastname''shipping_address_id'null'left')
            ->
addExpressionAttributeToSelect('billing_name',
                
'CONCAT({{billing_firstname}}, " ", {{billing_lastname}})',
                array(
'billing_firstname''billing_lastname'))
            ->
addExpressionAttributeToSelect('shipping_name',
                
'CONCAT({{shipping_firstname}}, " ", {{shipping_lastname}})',
                array(
'shipping_firstname''shipping_lastname'));

As you can see, the attribute id is hardcoded. The first joinAttribute call was necessary because the joinTable method doesn’t create the joins for the value tables, in this case personified as sales_order_int or _table_quote_id. You’d also need to add the column so it is displayed properly on the grid. Just add this little chunk of code to your _prepareColumns method on the same file:

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

So, yeah, three days to create this join, and it doesn’t look good at all. It will surely create problems when update comes since we can’t assure that the attribute id’s and table names will remain intacts for ever and ever… Therefore, I’d be much thankful if someone (magento devs needed!) could give me a hint on how to do this properly! =)

One important note: I’m using version 1.0.19870.4. It might work if you’re using another version, but if it doesn’t, try to find the right attribute_ids for the first two lines. First reffers to order/quote_id and second refers to quote_payment/method.

Cheers,

 
Magento Community Magento Community
Magento Community
Magento Community
 
mixwiz
Member
 
Total Posts:  39
Joined:  2008-07-21
 

Hello I’m looking to do the same as you, but show what the customer has ordered and remove the “ship to name”

But I’m struggling.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Gabriel Queiroz
Sr. Member
 
Total Posts:  135
Joined:  2008-05-28
Brasília, Brazil
 

Yes, guess that will be a little more complicated, since the order items are not in an attribute…

 
Magento Community Magento Community
Magento Community
Magento Community
 
mixwiz
Member
 
Total Posts:  39
Joined:  2008-07-21
 

That might be where im going wrong

 
Magento Community Magento Community
Magento Community
Magento Community
 
chantre84
Jr. Member
 
Total Posts:  9
Joined:  2008-06-04
 

I do also need the Payment-Method in the sales overview… but it doesn’t work… I’m using Magento 1.1.4. How do I find out which Attribute-ID I have to use??

 
Magento Community Magento Community
Magento Community
Magento Community
 
redpen
Sr. Member
 
Total Posts:  254
Joined:  2008-01-23
 

Anything like this for 1.1.6?  Code above doesn’t work even with the right attribute IDs.

 
Magento Community Magento Community
Magento Community
Magento Community
 
esnowxu
Jr. Member
 
Total Posts:  19
Joined:  2008-12-28
 

protected function _prepareCollection()
{
//TODO: add full name logic
$collection = Mage::getResourceModel(’sales/order_collection’)
->addAttributeToSelect(’*’)

->joinAttribute(’billing_firstname’, ‘order_address/firstname’, ‘billing_address_id’, null, ‘left’)
->joinAttribute(’billing_lastname’, ‘order_address/lastname’, ‘billing_address_id’, null, ‘left’)
->joinAttribute(’shipping_firstname’, ‘order_address/firstname’, ‘shipping_address_id’, null, ‘left’)
->joinAttribute(’shipping_lastname’, ‘order_address/lastname’, ‘shipping_address_id’, null, ‘left’)
->addExpressionAttributeToSelect(’billing_name’,
‘CONCAT({{billing_firstname}}, “ “, {{billing_lastname}})’,
array(’billing_firstname’, ‘billing_lastname’))
->addExpressionAttributeToSelect(’shipping_name’,
‘CONCAT({{shipping_firstname}}, “ “, {{shipping_lastname}})’,
array(’shipping_firstname’, ‘shipping_lastname’));

$paymentMethodTable = (string)Mage::getConfig()->getTablePrefix() . ‘sales_flat_quote_payment’;

$collection->getSelect()->joinLeft(array(’paymentTable’ => $paymentMethodTable),
‘paymentTable.parent_id=e.entity_id ‘,
array(’method’)
); $this->setCollection($collection);

return parent::_prepareCollection();
}
I see it have a problem ..After I fix it .

 
Magento Community Magento Community
Magento Community
Magento Community
 
subesh
Sr. Member
 
Avatar
Total Posts:  145
Joined:  2009-01-08
 

see //NEW ADDED SECTION

$collection Mage::getResourceModel('sales/order_collection')
            ->
addAttributeToSelect('*')
            ->
joinAttribute('billing_firstname''order_address/firstname''billing_address_id'null'left')
            ->
joinAttribute('billing_lastname''order_address/lastname''billing_address_id'null'left')
            ->
joinAttribute('shipping_firstname''order_address/firstname''shipping_address_id'null'left')
            ->
joinAttribute('shipping_lastname''order_address/lastname''shipping_address_id'null'left')
            
            
//NEW ADDED
            
->joinAttribute('payment_type''order_payment/method''payment_id'null'left')
            
//END NEW ADDED

            
->addExpressionAttributeToSelect('billing_name',
                
'CONCAT({{billing_firstname}}, " ", {{billing_lastname}})',
                array(
'billing_firstname''billing_lastname'))
            ->
addExpressionAttributeToSelect('shipping_name',
                
'CONCAT({{shipping_firstname}}, " ", {{shipping_lastname}})',
                array(
'shipping_firstname''shipping_lastname'));
        
        
$collection->printlogquery(true);        
        exit;

Run the query on phpmyadmin.. you will get what you want.. .. rest is all yours

 
Magento Community Magento Community
Magento Community
Magento Community
 
subesh
Sr. Member
 
Avatar
Total Posts:  145
Joined:  2009-01-08
 

$this->addColumn('payment_method', array(
            
'header' => Mage::helper('sales')->__('Payment Method'),
            
'index' => 'payment_type',
           ));

this willl end good

 
Magento Community Magento Community
Magento Community
Magento Community
 
redpen
Sr. Member
 
Total Posts:  254
Joined:  2008-01-23
 
subesh - 19 January 2009 11:34 PM

see //NEW ADDED SECTION

$collection Mage::getResourceModel('sales/order_collection')
            ->
addAttributeToSelect('*')
            ->
joinAttribute('billing_firstname''order_address/firstname''billing_address_id'null'left')
            ->
joinAttribute('billing_lastname''order_address/lastname''billing_address_id'null'left')
            ->
joinAttribute('shipping_firstname''order_address/firstname''shipping_address_id'null'left')
            ->
joinAttribute('shipping_lastname''order_address/lastname''shipping_address_id'null'left')
            
            
//NEW ADDED
            
->joinAttribute('payment_type''order_payment/method''payment_id'null'left')
            
//END NEW ADDED

            
->addExpressionAttributeToSelect('billing_name',
                
'CONCAT({{billing_firstname}}, " ", {{billing_lastname}})',
                array(
'billing_firstname''billing_lastname'))
            ->
addExpressionAttributeToSelect('shipping_name',
                
'CONCAT({{shipping_firstname}}, " ", {{shipping_lastname}})',
                array(
'shipping_firstname''shipping_lastname'));
        
        
$collection->printlogquery(true);        
        exit;

Run the query on phpmyadmin.. you will get what you want.. .. rest is all yours

I get an invalid foreign key with “payment_id”.

 
Magento Community Magento Community
Magento Community
Magento Community
 
esnowxu
Jr. Member
 
Total Posts:  19
Joined:  2008-12-28
 

Invalid foreign key
No the Attribute.

 
Magento Community Magento Community
Magento Community
Magento Community
 
esnowxu
Jr. Member
 
Total Posts:  19
Joined:  2008-12-28
 

I think magento version is diffrent ,So the attribute is diffrent?

 
Magento Community Magento Community
Magento Community
Magento Community
 
esnowxu
Jr. Member
 
Total Posts:  19
Joined:  2008-12-28
 

Which payment method is saved in sales_order_entity_varchar tables

The table value field saved ‘paypal_standard’

the entity_id is sales_order_entity.entity_id(cond is:parent_id=order.id and entity_type_id = 14 )

I use magento version1.1.6 and 1.1.7 can’t join the attribute.

When I use joinLeft method join sales_order_entity_varchar,It will overwrite the value field....

$paymentMt = (string)Mage::getConfig()->getTablePrefix() . ‘sales_order_entity’;
$paymentSt = (string)Mage::getConfig()->getTablePrefix() . ‘sales_order_entity_varchar’;

$collection->getSelect()->joinLeft(array(’mt’ => $paymentMt),
‘mt.parent_id=e.entity_id and mt.entity_type_id=14’,
array(’entity_id’)
);
$collection->getSelect()->joinLeft(array(’st’ => $paymentSt),
‘st.entity_id =mt.entity_id and st.entity_type_id=14 and st.attribute_id = 259’,
array(’v’ => ‘value’)
);

It will overwrite value field .So the grid’s status column will display none.
:(

How to do it under magento version 1.1.6 or 1.1.7 ?
Who can help us?

 
Magento Community Magento Community
Magento Community
Magento Community
 
subesh
Sr. Member
 
Avatar
Total Posts:  145
Joined:  2009-01-08
 

try with quote_id.... I think .. that may help

 
Magento Community Magento Community
Magento Community
Magento Community
 
esnowxu
Jr. Member
 
Total Posts:  19
Joined:  2008-12-28
 

Under magento 1.1.6 I saw the payment method saved in “sales_flat_quote_payment” table
sales_flat_quote_payment.quote_id = sales_flat_quote.entity_id
But I can’t find relationship for sales_order and sales_flat_quote tables;

sales_flat_order_item can relationship the sales_order.

Please help me .

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 3