Pull recent records from collection with group clause

Question

I am pulling records from sales_flat_quote using collection and to make the records unique i have applied customer_email group clause on collection. Below is the code.

$collection = Mage::getResourceModel('sales/quote_collection')
        ->addFieldToFilter('is_active', '1')
        ->addFieldToFilter('customer_email', array('notnull' => true));

$collection->getSelect()->group('customer_email');

here the problem is by using group, latest records are getting filter instead i want the older records to get filtered. guys please provide your valuable input on these question.

EDIT [Explanation]

lets say i am getting below records in collection and lets assume email address's column name is email

1. abc@abc.com
2. foo@bar.com
3. xyz@xyz.com
4. foo@bar.com

here when i do $collection->getSelect()->group('email'); i will get filtered records as below

1. abc@abc.com
2. foo@bar.com
3. xyz@xyz.com

instead of that i want

1. abc@abc.com
3. xyz@xyz.com
4. foo@bar.com

Answer 1

For some reason it appears that that collection clears the setOrders when building the sql used. Maybe someone can shed some light onto that for you, sadly I cannot, but the follow code will allow you to do what you need without any expensive looping in php.

/** @var Mage_Sales_Model_Resource_Quote_Collection $collection */
$collection = Mage::getResourceModel('sales/quote_collection')
    ->addFieldToFilter('is_active', '1');

$collection->getSelect()->group('customer_email')->order('entity_id DESC');

Here we get the selection with the correct filter and then add the group by and order directly on the select object and not onto the collection.

Answer 2

Adding ->order() to select should do the trick (if I understand your requirements right):

$quoteCollection = Mage::getResourceModel('sales/quote_collection')
    ->addFieldToFilter('is_active', '1')
    ->addFieldToFilter('customer_email', array('notnull' => true));

$quoteCollection->getSelect()->group('customer_email')
    ->order('customer_email');

This will produce the following query:

  SELECT `main_table`.* 
    FROM `sales_flat_quote` AS `main_table` 
   WHERE (is_active = '1') AND (customer_email IS NOT NULL)
GROUP BY `customer_email`
ORDER BY `customer_email` ASC

Answer 3

[My Solution]

First fetch the collection along with order by

$collection = Mage::getResourceModel('sales/quote_collection')->setOrder('entity_id', 'DESC');

than apply group by

$collection->getSelect()->group('customer_email');

and after that add all the filters

$collection->addFieldToFilter('is_active', '1');

[END]

can any one please clarify why below code dint worked.

$collection = Mage::getResourceModel('sales/quote_collection')
               ->addFieldToFilter('is_active', '1')
               ->setOrder('entity_id', 'DESC');

$collection->getSelect()->group('customer_email');