13

In module's admin grid I am using this code to get collection and group them by customer id

$collection = Mage::getModel('referafriend/statistics')->getCollection();
$collection->getSelect()->group('entity_id');
$this->setCollection($collection);

but here i have to use renderer and filter functions for customer info like name and email against each entity_id. i want to join customer model with my module's table. for this i have written this code

 $collection = Mage::getModel('customer/customer')->getCollection()
 ->addNameToSelect();
$collection->getSelect()->join(array('refer' => 'table_name'),'refer.entity_id = e.entity_id'
          ); 
   $collection->getSelect()->group('entity_id'); 
   $collection->addAttributeToSelect('*');

but it gives me this error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'entity_id' in group statement is ambiguous

any help will be highly appreciated.

3
  • 1
    it should be ->group('e.entity_id');
    – Amit Bera
    Oct 16, 2014 at 11:12
  • You should add this as an answer with some detail on why you need the e. Oct 16, 2014 at 11:24
  • Sorry for this silly mistake. @AmitBera thanks for your help and please add this as answer so that question could be closed.
    – Haris
    Oct 16, 2014 at 11:38

1 Answer 1

27

You need to add table name in group by condition.As you did not added on conditions table name at table group('entity_id') so query did not find columns name

 getSelect()->group('e.entity_id');

Logic is:

$collection->getSelect()->group('TABLE_NAME.FIELD_NAME')
5
  • 1
    In addtion, if you need to group by multiple fields, the just add more -> group() clauses ->group('field1') ->group('field2');
    – GregC
    Mar 24, 2016 at 21:53
  • I want order unique products using group by. I have 2 order with 2 same order item. Currently, it is displayed 4 rows in the grid. But I need 2-row using group by. Dec 17, 2019 at 7:13
  • Share your code
    – Amit Bera
    Dec 17, 2019 at 7:55
  • $collection = $object_manager->create('\Magento\Sales\Model\Order\Item')->getCollection(); $collection->getSelect()->join( ['order' => $this->getTable('sales_order')], 'order.entity_id = main_table.order_id and (if(main_table.parent_item_id IS NULL,main_table.price != 0.0000,main_table.parent_item_id IS NULL))', [ 'order_number' => 'order.increment_id', 'order_store_id' => 'order.store_id', ] ); Dec 17, 2019 at 8:32
  • I want to group by parent_item_id order wise. Dec 17, 2019 at 8:36

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.