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?

Improve this question

1 Answer 1

Reset to default
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();
Improve this answer
3
  • 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, 2016 at 22:20
  • Edited to get to get last order from admin.
    – Bill Murray
    May 30, 2016 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, 2016 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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