I am trying to get the customers last order id and customer related data on frontend in a block.
Basically I will have a parent customer who will connect to its child customers with an ERP Customer ID (which I saved as an attribute). I want to load all those child customers and need to fetch their data along with last order id.
Below is what I have done so far (Edit code):
$currentCustomer = Mage::getSingleton('customer/session')->getCustomer();
$customers = Mage::getResourceModel('customer/customer_collection')
->addNameToSelect()
->addAttributeToSelect('entity_id')
->addAttributeToSelect('email')
->addAttributeToFilter('billing_customer', $currentCustomer->externalERPIdId())
->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
->joinTable(
array('order' => 'sales_flat_order'), 'customer_email = email',
array('order_id' => 'order.entity_id', 'createdAt' => 'order.created_at'),
null,
'left'
)
->groupByAttribute('entity_id');
Although I do get some customer data along with the orders id but I do not know how to filter the above collection so that I only get customer_id, customer name, email, LAST order id. Also the ORDER BY condition is not applying to query.
Here is the final query (EDIT):
SELECT `e`.*, `at_prefix`.`value` AS `prefix`, `at_firstname`.`value` AS `firstname`, `at_middlename`.`value` AS `middlename`, `at_lastname`.`value` AS `lastname`, `at_suffix`.`value` AS `suffix`, CONCAT(IF(at_prefix.value IS NOT NULL AND at_prefix.value != '', CONCAT(LTRIM(RTRIM(at_prefix.value)), ' '), ''), LTRIM(RTRIM(at_firstname.value)), ' ', IF(at_middlename.value IS NOT NULL AND at_middlename.value != '', CONCAT(LTRIM(RTRIM(at_middlename.value)), ' '), ''), LTRIM(RTRIM(at_lastname.value)), IF(at_suffix.value IS NOT NULL AND at_suffix.value != '', CONCAT(' ', LTRIM(RTRIM(at_suffix.value))), '')) AS `name`, `at_billing_customer`.`value` AS `billing_customer`, `at_default_billing`.`value` AS `default_billing`, `at_billing_telephone`.`value` AS `billing_telephone`, `order`.`entity_id` AS `order_id`, `order`.`created_at` AS `createdAt` FROM `customer_entity` AS `e`
LEFT JOIN `customer_entity_varchar` AS `at_prefix`ON (`at_prefix`.`entity_id` = `e`.`entity_id`) AND (`at_prefix`.`attribute_id` = '4')
LEFT JOIN `customer_entity_varchar` AS `at_firstname` ON (`at_firstname`.`entity_id` = `e`.`entity_id`) AND (`at_firstname`.`attribute_id` = '5')
LEFT JOIN `customer_entity_varchar` AS `at_middlename` ON (`at_middlename`.`entity_id` = `e`.`entity_id`) AND (`at_middlename`.`attribute_id` = '6')
LEFT JOIN `customer_entity_varchar` AS `at_lastname` ON (`at_lastname`.`entity_id` = `e`.`entity_id`) AND (`at_lastname`.`attribute_id` = '7')
LEFT JOIN `customer_entity_varchar` AS `at_suffix` ON (`at_suffix`.`entity_id` = `e`.`entity_id`) AND (`at_suffix`.`attribute_id` = '8')
INNER JOIN `customer_entity_int` AS `at_billing_customer` ON (`at_billing_customer`.`entity_id` = `e`.`entity_id`) AND (`at_billing_customer`.`attribute_id` = '213')
LEFT JOIN `customer_entity_int` AS `at_default_billing` ON (`at_default_billing`.`entity_id` = `e`.`entity_id`) AND (`at_default_billing`.`attribute_id` = '13')
LEFT JOIN `customer_address_entity_varchar` AS `at_billing_telephone` ON (`at_billing_telephone`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_telephone`.`attribute_id` = '31')
LEFT JOIN `sales_flat_order` AS `order` ON (order.customer_email =e.email) WHERE (`e`.`entity_type_id` = '1') AND (at_billing_customer.value = '1006') GROUP BY `e`.`entity_id`
Adding the GROUP BY
clause saved me from the duplication error but on the other end it does not give me the last/latest Order_id of the user.
It would be nice if someone can tell me what I need to change in the collection or join query to get the required result?