Email address in Orders grid

[addison74]

I would like to discuss an improvement to the Orders grid in Backend. There are situations when customer have the same name and situations when you want to identify the order history placed by a particular customer.

For the last situation it can be done but it is in several steps. You go to the Manage Customers grid and filter by email address. Once the customer is found you access the page and then open the Orders section on the left side.

For the first situation there is no solution because there is no other information that differentiates the customers with the same name. In addition, searching the order history is easier to do in the grid than what I showed in the previous paragraph.

I am waiting for opinions. If this feature is desired I can implement it.

[addison74]

The interesting part is when customers do not have accounts because checking the order history as described above no longer applies.

It is obvious that a column is needed to differentiate customers with same names and also for checking order history . I consider the email address is the most appropriate field qualified for the grid.

[kiatng]

Easier to add customer_id to the grid, as for email, you would need a join, which defeats the purpose of the table sales_flat_order_grid.

[addison74]

This solution solves partially the issue by allowing filtering buyers with the same name based on customer_id, but not the ability to filter orders placed by an email address.

I talked to the content creators in two stores I manage and they told me that when they check the order history they search into the Inbox for an email address because the Magento interface does not allow this. I'm starting to agree with that.

[kiatng]

In many countries, email is not as common as mobile phone. Many have abandoned emails in favor of Whatsapp, WeChat, or other IM, even in business communication. Grab, the e-hailing and food delivery giant, email doesn't come into play. I find it increasingly necessary to provide login option with phone number. May be new features for ver 21: register, login, and search by phone number, and notification via IM/SMS.

[addison74]

In this case, each store should adapt its grid according to what best defines the customer between Email/Phone/Instant messaging services/Social media accounts/Etc.

[woutersamaey]

which defeats the purpose of the table sales_flat_order_grid.

I've never understood or seen the benefit of this table and we are not using it, even on huge installs with lots of orders. IMHO this table should never have existed to begin with.

[m-overlund]

As a company with multiple webshops, we really use phonenumber and email filtering on a daily basis.

[kiatng]

which defeats the purpose of the table sales_flat_order_grid.

I've never understood or seen the benefit of this table and we are not using it, even on huge installs with lots of orders. IMHO this table should never have existed to begin with.

I can see the grid table has billing_name and shipping_name which is a concat of the first, middle, and last names:

magento-lts/app/code/core/Mage/Sales/Model/Resource/Order.php

Lines 90 to 107 in 895f0d0

	$ifnullFirst = $adapter->getIfNullSql('{{table}}.firstname', $adapter->quote(''));
	$ifnullMiddle = $adapter->getIfNullSql('{{table}}.middlename', $adapter->quote(''));
	$ifnullLast = $adapter->getIfNullSql('{{table}}.lastname', $adapter->quote(''));
	$concatAddress = $adapter->getConcatSql(array(
	$ifnullFirst,
	$adapter->quote(' '),
	$ifnullMiddle,
	$adapter->quote(' '),
	$ifnullLast
	));
	$concatAddress = new Zend_Db_Expr("TRIM(REPLACE($concatAddress,' ', ' '))");
	$this->addVirtualGridColumn(
	'billing_name',
	'sales/order_address',
	array('billing_address_id' => 'entity_id'),
	$concatAddress
	)

Also, we needed to create a custom grid table based off a very large table (in terms of number of rows) in order to add new columns (for filtering purposes in the grid) with the upgrade script. The script failed when we attempted to add cols to the large table. So, one use case for the grid tables is to make it easier to add custom columns when the base tables get very large. Not sure if this is an intended use case. I am not any good with MySQL, there probably is a way to insert columns to a large table.

[Sdfendor]

@woutersamaey Because sales_flat_order exists, where all the information needed and discussed in this issue is present (customer email, customer_id if present (guest orders)), the grid specific flat table sales_flat_order_grid seems redundant to me as well.

@kiatng Do you know whether the grid flat table is used in other parts of the shop or is its sole purpose to hold the data to present in the orders grid?

[kiatng]

@Sdfendor All the grid tables are for the grid view in backend. Interestingly, frontend uses the base table:

magento-lts/app/code/core/Mage/Sales/Block/Order/History.php

Lines 45 to 50 in 895f0d0

	$orders = Mage::getResourceModel('sales/order_collection')
	->addFieldToSelect('*')
	->addFieldToFilter('customer_id', Mage::getSingleton('customer/session')->getCustomer()->getId())
	->addFieldToFilter('state', array('in' => Mage::getSingleton('sales/order_config')->getVisibleOnFrontStates()))
	->setOrder('created_at', 'desc')
	;