Magento Customer Grid - Last Order Date

Question

I'm trying to add some extra columns into Magento's customer grid. I've got an order count working successfully (Total amount of orders a customer has placed)

I'm trying to get the Last Order date in, but I'm unable to get it working. Has anyone done this before or can someone point me in the right direction as to how to do this?

This is my current code, but it errors and just displays a SQL query.

    $orderTableName = Mage::getSingleton('core/resource')
        ->getTableName('sales/order');
    $collection->getSelect()
        ->joinLeft(
            array('orders' => $orderTableName),
            'orders.customer_id=e.entity_id',
            array(
                'order_count' => 'COUNT(customer_id)',
                'last_order' => 'MAX(created_at)'
            )
        );
    $collection->groupByAttribute('entity_id');

small advice: upvote good answers and downvote poor answers. this encourage frequent site visitors to help you from one side and helps the whole community to grow in the right direction. — Amasty, Sep 26 '14 at 16:46
Thanks. Very helpful :-) I'm also using your Customer Attributes Extension - Is it possible to get attributes onto the Invoice Grid? — Christopher Thrower, Sep 29 '14 at 15:03
probably yes, but not sure. For amasty.com/customer-attributes.html you'd better email us directly and me or my colleagues will help you. Reference to this question even if your free support period is over. — Amasty, Sep 29 '14 at 20:55

Amasty · Accepted Answer · 2014-09-29 14:38:48Z

Here is working example for CE 1.8.1:

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel('customer/customer_collection')
        ->addNameToSelect()
        ->addAttributeToSelect('email')
        ->addAttributeToSelect('created_at')
        ->addAttributeToSelect('group_id')
        ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
        ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
        ->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
        ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
        ->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left');



    // add 2 new fields as sub queries      
    $sql ='SELECT MAX(o.created_at)'
        . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
        . ' WHERE o.customer_id = e.entity_id ';
    $expr = new Zend_Db_Expr('(' . $sql . ')'); 

    $collection->getSelect()->from(null, array('last_order_date'=>$expr));

    $sql ='SELECT COUNT(*)'
        . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
        . ' WHERE o.customer_id = e.entity_id ';
    $expr = new Zend_Db_Expr('(' . $sql . ')'); 

    $collection->getSelect()->from(null, array('orders_count'=>$expr));

    //echo $collection->getSelect(); exit;      

    $this->setCollection($collection);

    return parent::_prepareCollection();
}

and

protected function _prepareColumns()
{
    $this->addColumn('entity_id', array(
        'header'    => Mage::helper('customer')->__('ID'),
        'width'     => '50px',
        'index'     => 'entity_id',
        'type'  => 'number',
    ));

    $this->addColumn('last_order_date', array(
        'header'    => Mage::helper('customer')->__('Last Order Date'),
        'type'      => 'datetime',
        'align'     => 'center',
        'index'     => 'last_order_date',
        'gmtoffset' => true,        
    ));

    $this->addColumn('orders_count', array(
        'header'    => Mage::helper('customer')->__('Orders Count'),
        'index'     => 'orders_count',
    ));

    ........
    ........

The output is

orders

I had already tried MAX(created_at) Sorry - I'll update my Question — Christopher Thrower, Sep 26 '14 at 14:36
I've added this code before $this->setCollection($collection); Then set "last_oder" in my addColumn code but it seems to be blank :( — Christopher Thrower, Sep 28 '14 at 21:05
Found the solution, sharing it here in case someone else needs the same thing (I don't see why we should take into account anything else than the completed orders). I added ` . ' AND o.status LIKE "complete" ' ` after ` . ' WHERE o.customer_id = e.entity_id '; ` — Hiboomedia, Aug 24 '16 at 19:15
For search you can create custom call back function see newsinfo-blog.blogspot.com/2016/10/… — himansu, Oct 23 '16 at 17:46

Community · Accepted Answer · 2017-04-13 12:55:02Z

Here is how I would do this.
I would create 2 attributes called last_order_date and orders_count on the customer entity that are only visible in the backend. Or not visible at all. Here is one possible way to add them.

The I would create an observer on the checkout_submit_all_after event that would change the values of these attributes.
Something like this:

public function checkoutSubmitAllAfter($observer) {
    $order = $observer->getEvent()->getOrder();
    if ($order) { //if on onepage checkout;
        $customer = $order->getCustomer();
        $lastOrderDate = $order->getCreatedAt();
        $increment = 1;
    }
    else { //if on multishipping checkout
       $orders = $observer->getEvent()->getOrders();
       //get last order that's the important one;
       $order = $orders[count($orders) - 1];
       $customer = $order->getCustomer();
       $lastOrderDate = $order->getCreatedAt();
       $increment = count($orders);
    }
    if ($customer && $customer->getId()) { //check if the customer is logged in
       //make sure you have a clean instance of the customer
       //this line may not be needed. Test with it and without it.
       $customer = Mage::getModel('customer/customer')->load($customer->getId());
       //set the new values on the customer entity
       $customer->setLastOrderDate($lastOrderDate);
       $customer->setOrdersCount((int)$customer->getOrdersCount() + $increment);
       $customer->save();
    }
}

I haven't tested the code, so I may have missed something.

Now you have the values you need on the customer object and you don't need any crazy join or sort order and specially no group by.
You can add your 2 new columns in the customer grid and you can even make them sortable and filterable easily.

If you already have customers in your db, then you will need a one time only script that sets the values for the existing customers.
But you can identify these values easily.

Small side note: checkout_submit_all_after works great for visitors, but I think for admin orders you need one more event. — Amasty, Sep 29 '14 at 14:11
@Amasty. Nope. That's all you need. The event is there in the backend also. Check Mage_Adminhtml_Model_Sales_Order_Create::createOrder. It's the last line before the return. — Marius, Sep 29 '14 at 14:13

Diego Castro · Accepted Answer · 2017-05-26 13:34:11Z

Here is how I solved the problem with filter and ordering working, _prepareCollection:

$collection = Mage::getResourceModel('customer/customer_collection')
    ->addNameToSelect()
    ->joinAttribute('billing_street', 'customer_address/street', 'default_billing', null, 'left')
    ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
    ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
    ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
;

$sql = sprintf('SELECT MAX(o.created_at) AS created_at FROM %s AS o GROUP BY o.customer_id', Mage::getSingleton('core/resource')->getTableName('sales/order_grid'));
$expr = new Zend_Db_Expr('(' . $sql . ')');

$collection->joinTable(array('order' => 'sales/order_grid'), 'customer_id=entity_id', array('order_created_at' => 'created_at'), "order.created_at IN ($expr)", 'left');

Then on _prepareColumns:

$this->addColumn('order_created_at', array(
    'header'    => Mage::helper('sales')->__('Purchased On'),
    'type'      => 'datetime',
    'index'     => 'order_created_at',
    'gmtoffset' => true,
));

Hiboomedia · Accepted Answer · 2016-08-24 19:30:58Z

If you need only "complete" status like me, then just add . ' AND o.status LIKE "complete" ' after . ' WHERE o.customer_id = e.entity_id ';

I've also added the total Money spent by the customer (so the total of all of it's complete orders)

    $total_pricesql ='SELECT sum(o.base_grand_total)'
        . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
        . ' WHERE o.customer_id = e.entity_id ' . ' AND o.status LIKE  "complete" ';
    $expr = new Zend_Db_Expr('(' . $total_pricesql . ')'); 

    $collection->getSelect()->from(null, array('total_price'=>$expr));

and later in the same file

    $this->addColumn('total_price', array(
        'header' => Mage::helper('sales')->__('Total'),
        'index' => 'total_price',
        'type'  => 'number',
        'filter'    => false,
    ));

so change @Amasty's first codes:

protected function _prepareCollection() {
$collection = Mage::getResourceModel('customer/customer_collection')
    ->addNameToSelect()
    ->addAttributeToSelect('email')
    ->addAttributeToSelect('created_at')
    ->addAttributeToSelect('group_id')
    ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
    ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
    ->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
    ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
    ->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left');



// add 3 new fields as sub queries      
$sql ='SELECT MAX(o.created_at)'
    . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
    . ' WHERE o.customer_id = e.entity_id ' . ' AND o.status LIKE  "complete" ';
$expr = new Zend_Db_Expr('(' . $sql . ')'); 

$collection->getSelect()->from(null, array('last_order_date'=>$expr));

$sql ='SELECT COUNT(*)'
    . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
    . ' WHERE o.customer_id = e.entity_id ' . ' AND o.status LIKE  "complete" ';
$expr = new Zend_Db_Expr('(' . $sql . ')'); 

$collection->getSelect()->from(null, array('orders_count'=>$expr));

$total_pricesql ='SELECT sum(o.base_grand_total)'
    . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
    . ' WHERE o.customer_id = e.entity_id ' . ' AND o.status LIKE  "complete" ';
$expr = new Zend_Db_Expr('(' . $total_pricesql . ')'); 

$collection->getSelect()->from(null, array('total_price'=>$expr));

//echo $collection->getSelect(); exit;      

$this->setCollection($collection); return parent::_prepareCollection();}

and the second code:

protected function _prepareColumns() {
$this->addColumn('entity_id', array(
    'header'    => Mage::helper('customer')->__('ID'),
    'width'     => '50px',
    'index'     => 'entity_id',
    'type'  => 'number',
));

$this->addColumn('last_order_date', array(
    'header'    => Mage::helper('customer')->__('Last Order Date'),
    'type'      => 'datetime',
    'align'     => 'center',
    'index'     => 'last_order_date',
    'gmtoffset' => true,        
));

$this->addColumn('orders_count', array(
    'header'    => Mage::helper('customer')->__('Orders Count'),
    'index'     => 'orders_count',
));

$this->addColumn('total_price', array(
    'header' => Mage::helper('sales')->__('Total'),
    'index' => 'total_price',
    'type'  => 'number',
    'filter'    => false,
));

........
........

Alex · Accepted Answer · 2021-09-01 11:35:47Z

In my case - Added 3 new columns to Admin Customer Grid

orders total
orders count
last order date

Added following changes to app/code/local/Mage/Adminhtml/Block/Customer/Grid.php

Changed method _setCollectionOrder

protected function _setCollectionOrder($column)
{
    if (!$dir = $column->getDir()) {
        return $this;
    }

    if ($column->getIndex() == 'orders_count') {
        $collection = $this->getCollection();
        $collection->getSelect()
            ->order("orders_count " . strtoupper($column->getDir()));
        return $this;
    }

    if ($column->getIndex() == 'orders_total') {
        $collection = $this->getCollection();
        $collection->getSelect()
            ->order("orders_total " . strtoupper($column->getDir()));
        return $this;
    }

    if ($column->getIndex() == 'last_order_date') {
        $collection = $this->getCollection();
        $collection->getSelect()
            ->order("last_order_date " . strtoupper($column->getDir()))
            ->order("last_order_date_filter " . strtoupper($column->getDir()));

        return $this;
    }

    return parent::_setCollectionOrder($column);
}

Changed method _prepareCollection

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel('customer/customer_collection')
        ->addNameToSelect()
        ->addAttributeToSelect('email')
        ->addAttributeToSelect('created_at')
        ->addAttributeToSelect('group_id')
        ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
        ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
        ->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
        ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
        ->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left');


    /**
     *
     */
    $readAdapter = Mage::getSingleton('core/resource')->getConnection('core_read');
    $select = $readAdapter->select()
        ->from('sales_flat_order_grid')
        ->where('status=?', 'complete')
        ->columns(array(
                'orders_total' => new Zend_Db_Expr('CEIL(SUM(grand_total))'),
                'orders_count' => new Zend_Db_Expr('COUNT(*)'),
                'last_order_date' => new Zend_Db_Expr('MAX(created_at)'),
                'last_order_date_filter' => new Zend_Db_Expr('MAX(created_at)')
            )
        )->group('customer_id');

    $collection->getSelect()->joinLeft(
        array('orders_count' => new Zend_Db_Expr('(' . $select . ')')),
        'e.entity_id = orders_count.customer_id',
        array(
            'orders_count.orders_count',
            'orders_count.orders_total',
            'orders_count.last_order_date',
            'orders_count.last_order_date_filter'
        )
    );
    $collection->addFilterToMap('orders_count', 'orders_count.orders_count');
    $collection->addFilterToMap('orders_total', 'orders_count.orders_total');
    $collection->addFilterToMap('last_order_date', 'orders_count.last_order_date');
    /**
     *
     */


    $this->setCollection($collection);

    return parent::_prepareCollection();
}

Added new columns to method _prepareColumns

$this->addColumn('last_order_date', array(
        'header' => Mage::helper('customer')->__('Last Order Date'),
        'type' => 'datetime',
        'align' => 'center',
        'index' => 'last_order_date',
        'gmtoffset' => true,
        'filter_condition_callback' => array($this, '_ordersLastOrderDateFilterCallBack'),
    ));

    $this->addColumn('orders_count', array(
        'header' => Mage::helper('customer')->__('Total No. of Orders'),
        'align' => 'left',
        'width' => '40px',
        'index' => 'orders_count',
        'type' => 'number',
        'filter_condition_callback' => array($this, '_ordersCountFilterCallBack'),
        'sortable' => true,
    ));

    $this->addColumn('orders_total', array(
        'header' => Mage::helper('customer')->__('Total Sum. of Orders'),
        'align' => 'left',
        'width' => '40px',
        'index' => 'orders_total',
        'filter_condition_callback' => array($this, '_ordersTotalFilterCallBack'),
        'sortable' => true,
        'type' => 'currency',
        'currency_code' => 'UAH',
    ));

And added 3 new Callback methods, need for filters

protected function _ordersCountFilterCallBack($collection, $column)
{
    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }

    if (!isset($value['from'])) {
        $value['from'] = '';
    }

    if (!isset($value['to'])) {
        $value['to'] = '';
    }

    $from = ($value['from']) ? $value['from'] : 0;
    $to = ($value['to']) ? $value['to'] : 999999;
    $collection->getSelect()
        ->where("orders_count.orders_count >= $from AND  orders_count.orders_count <= $to");
    return $this;
}

protected function _ordersTotalFilterCallBack($collection, $column)
{
    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }

    if (!isset($value['from'])) {
        $value['from'] = '';
    }

    if (!isset($value['to'])) {
        $value['to'] = '';
    }

    $from = ($value['from']) ? $value['from'] : 0;
    $to = ($value['to']) ? $value['to'] : 999999;
    $collection->getSelect()
        ->where("orders_count.orders_total >= $from AND  orders_count.orders_total <= $to");
    return $this;
}

protected function _ordersLastOrderDateFilterCallBack($collection, $column)
{
    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }

    if (!isset($value['from'])) {
        $value['from'] = '';
    }

    if (!isset($value['to'])) {
        $value['to'] = '';
    }

    $from = ($value['from']) ? $value['from']->get('yyyy-MM-dd HH:mm:ss') : $from = '2000-11-11 22:22:22';
    $to = ($value['to']) ? $value['to']->get('yyyy-MM-dd HH:mm:ss') : $to = '2050-11-11 22:22:22';
    $collection->getSelect()
        ->where("orders_count.last_order_date BETWEEN '$from' AND '$to'");

    return $this;
}

Stack Exchange Network

current community

your communities

more stack exchange communities

Magento Customer Grid - Last Order Date

5 Answers 5

Your Answer

Not the answer you're looking for? Browse other questions tagged magento-1.9 customer customer-grid or ask your own question.

Linked

Hot Network Questions

Magento Customer Grid - Last Order Date

5 Answers 5

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged magento-1.9 customer customer-grid or ask your own question.

Linked

Related

Hot Network Questions