1

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. [email protected]
2. [email protected]
3. [email protected]
4. [email protected]

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

1. [email protected]
2. [email protected]
3. [email protected]

instead of that i want

1. [email protected]
3. [email protected]
4. [email protected]
12
  • What are those "latest records"?
    – user487772
    Jul 3, 2014 at 7:15
  • latest records based on entity_id Jul 3, 2014 at 7:16
  • Sorry, but I still don't understand what are you trying to achieve.
    – user487772
    Jul 3, 2014 at 7:38
  • @Tim please refer edit for explanation Jul 3, 2014 at 7:50
  • You could potentially add an order by entity id DESC, but I'm not convinced it would work in every case? Jul 3, 2014 at 8:01

3 Answers 3

2

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.

1

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
0

[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');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.