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. 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
  • What are those "latest records"? – user487772 Jul 3 '14 at 7:15
  • latest records based on entity_id – Deepak Mallah Jul 3 '14 at 7:16
  • Sorry, but I still don't understand what are you trying to achieve. – user487772 Jul 3 '14 at 7:38
  • @Tim please refer edit for explanation – Deepak Mallah Jul 3 '14 at 7:50
  • You could potentially add an order by entity id DESC, but I'm not convinced it would work in every case? – Douglas Radburn Jul 3 '14 at 8:01
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, privacy policy and cookie policy

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