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 Commented Jul 2, 2015 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. Commented Jul 2, 2015 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? Commented Jul 2, 2015 at 12:40
  • Hmm.. sounds like it will do the job, but i don know how to make it.. :( Commented Jul 2, 2015 at 12:41
  • tony have check my answer
    – Amit Bera
    Commented Jul 2, 2015 at 14:13

1 Answer 1

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.\ Commented Jul 2, 2015 at 14:16
  • wait i will give solution..
    – Amit Bera
    Commented Jul 2, 2015 at 14:19

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.