1

I am working on a custom Magento Extension.

I am wondering how can i select all users who ever paid an order. I mean users which have one order with status complete.

Here is the code that has been given to me for this purpose, but it is not working as intended:

$customers = Mage::getModel('customer/customer')
    ->getCollection()
    ->addAttributeToSelect('*');

    $customers->joinTable(
            array('sales/order'),
            'customer_email=email',
            array('*'),
            null,
            'right'
        ); 


$data = $customers->getData();
foreach($data as $d)
{
    //print_r($d); // print this to see the available fields 
    echo $d[customer_email]; //get the desired information
    echo "<br>";
}

Right now i am wondering is it even possible ?

5
  • you don't seem to be testing the status of the order Jul 2 '15 at 12:31
  • I do not know how to test it. This code is suggestion given to me, but i think it is not even close. Jul 2 '15 at 12:36
  • how about you do it the other way around, query the orders which have been completed and join the user table with the emails? Jul 2 '15 at 12:40
  • Hmm.. sounds like it will do the job, but i don know how to make it.. :( Jul 2 '15 at 12:41
  • tony have check my answer
    – Amit Bera
    Jul 2 '15 at 14:13
2

First,you need to decide how define an order as paid.

Basically, We have define an order by order status.

Order with status Complete,invoice,Shipment,processingcalculated as paid order.

So,you need add sales_flat_order table's column status to your collection filler the collection.

EDIT:

As you want to filer customer collection by customer's completed orders count that

I have

  • Create a sub Query using table sales_flat_order table

$subSelect = Mage::getSingleton( 'core/resource' )->getConnection( 'core_read' )->select() ->from(array('o' =>Mage::getSingleton('core/resource')->getTableName('sales/order')), 'COUNT(*)') ->where('o.customer_id = e.entity_id ');

  • filter the table by status =complete

$subSelect->where('o.status = ?', 'complete');

  • Then add this sub select query to customer collection as new columns (order_count) to customer collection.

$collection->getSelect()->columns(array('order_count' => $subSelect))

then using having closure filer by this virtual columns :

`$collection->getSelect()->having('order_count > 0');`

Try this query as you want on basic of complete orders:

$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');
$subSelect =  Mage::getSingleton( 'core/resource' )->getConnection( 'core_read' )->select()
            ->from(array('o' =>Mage::getSingleton('core/resource')->getTableName('sales/order')), 'COUNT(*)')
            ->where('o.customer_id = e.entity_id ')
            ->where('o.status = ?', 'complete');

 echo $collection->getSelect()->columns(array('order_count' => $subSelect))->having('order_count > 0');
-----
2
  • Yes i need orders with status Complete and then somehow i have to get information for all users, but i do not know how.\ Jul 2 '15 at 14:16
  • wait i will give solution..
    – Amit Bera
    Jul 2 '15 at 14:19

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.