CODEWITHSUNDEEP
phpmagento
Rowi123
Rowi123

Reputation: 95

how to add total number of orders to customer grid in magento

i want to show number of orders in customer grid of magento

i used this as a guide: How to add customer "total number of orders" and "total spent" to order grid in magento 1.7

but this is a different grid

so far i have created: app/code/local/Mage/Adminhtml/Block/Customer/Grid.php

_prepareCollection

i added:

    $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)')
            );
        $collection->groupByAttribute('entity_id');

before: $this->setCollection($collection);

_prepareColumns i added:

$this->addColumn('order_count', array(
            'header'    => Mage::helper('customer')->__('# orders'),
            'index'     => 'order_count',
             'type'  => 'number'
        ));

while it does work in the grid, i have some problems:

Upvotes: 1

Views: 2839

Answers (4)

Maleeha Awan
Maleeha Awan

Reputation: 1

We can create a column of total orders in customer grid table and display in numbers .
Vendor/Module/view/adminhtml/ui_component/customerlisting.xml
<?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
   <columns name="customer_columns" class="Magento\Customer\Ui\Component\Listing\Columns" >
       <column name="total_orders" class="Vendor\Module\Ui\Component\Listing\Column\TotalOrders" sortOrder="90">
           <settings>
               <dataType>text</dataType>
               <label translate="true">Total Orders</label>
               <sortable>false</sortable>
               <filter>false</filter>
           </settings>
       </column>
   </columns>
</listing>
And then create a Ui component to fetch orderdata.
Vendor/Module/Ui/Component/Listing/Column/TotalOrders.php

<?php
namespace Vendor\Module\Ui\Component\Listing\Column;

use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Ui\Component\Listing\Columns\Column;

class TotalOrders extends Column
{
   protected $orderCollectionFactory;
   /**
    * 
    * @param ContextInterface   $context           
    * @param UiComponentFactory $uiComponentFactory   
    * @param array              $components        
    * @param array              $data              
    */
   public function __construct(
       ContextInterface $context,
       UiComponentFactory $uiComponentFactory,
       array $components = [],
       array $data = [],
       \Magento\Sales\Model\ResourceModel\Order\CollectionFactory  $orderCollectionFactory
   ) {

       $this->orderCollectionFactory = $orderCollectionFactory;
       parent::__construct($context, $uiComponentFactory, $components, $data);
   }

   /**
    * Prepare Data Source
    *
    * @param array $dataSource
    * @return array
    */
   public function prepareDataSource(array $dataSource)
   {
       if (isset($dataSource['data']['items'])) {
           foreach ($dataSource['data']['items'] as & $item) {
               $customerOrder = $this->orderCollectionFactory->create()->addFieldToFilter('customer_id', $item['entity_id']);
               $item[$this->getData('name')] = count($customerOrder);//Value which you want to display**strong text**
           }
       }
       return $dataSource;
   }
}

Upvotes: 0

Sandip Baviskar
Sandip Baviskar

Reputation: 1

It's working fine over there. just follow the following steps .

add code in the following file app\code\core\Mage\Adminhtml\Block\Customer\Grid.php

 add this code in _prepareCollection() fucntion only 

$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));

and also add this code in _prepareColumns() function with same file

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

Upvotes: 0

Hung Tran
Hung Tran

Reputation: 26

Just remove:

$collection->groupByAttribute('entity_id');

And add this:

$collection->group('e.entity_id');

Overview we have:

$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)')
        );
   $collection->group('e.entity_id');

OR

$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)')
        )
        ->group('e.entity_id');

Upvotes: 1

blmage
blmage

Reputation: 4214

You have a GROUP BY clause in your collection, and the grid pager uses $collection->getSize() to determine the number of pages. The problem is that getSize() applies a SELECT COUNT(*) to the collection, and fetches the first column of the first row to get the number of results. With the GROUP BY still applied, the pager then considers that there is only one result.

To prevent this problem, you should either use your own customers collection with a relevant getSize(), or use sub-queries to retrieve the totals you need.

Upvotes: 0

Related Questions