1

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?

1

You need to

Mage::getSingleton('checkout/session')->getLastOrderId(); this will get you last order and what you missing to call.

I haven't verified this method though, however, it should work.

$order = Mage::getModel('sales/order')
          ->load(Mage::getSingleton('sales/order')->getLastOrderId());
$getLastOrderIncrementId = $order->getIncrementId();
  • Thanks for the reply Anthony, correct me if I am wrong but this solution may not work once the user is logged out. Because when he/she will login again checkout session will lost. – Capri82 May 30 '16 at 22:20
  • Edited to get to get last order from admin. – Anthony Cicchelli May 30 '16 at 22:26
  • I would like to do this in one query instead of looping through another collection. Right now I get the order id but it is not the last one. And this is due to the group by clause in the query. – Capri82 May 31 '16 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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