Magento Collection – How to Use Group By with Join Query

collection;customer-groupjoin;magento-1

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.

Best Answer

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

joinTable()

But magento collections have a method joinTable() it took me 45min to fizzle out how it is used. To avoid this for you, I share it.

$productCollection->joinTable(
    array('bonus' => 'mycompany/bonus'), 'product_id=entity_id',
    array('bonus_id' => 'bonus_id')
);

The parameters are:

public function joinTable($table, $bind, $fields = null, $cond = null, $joinType = 'inner')

Table is easy, it is the magento namespace/entity format, which you use in your configuration, resource models and the collection. You can use an array of the format array('alias' => 'namespace/entity')
Bind means, the ON statement in your SQL. This was the hardest part and I will explain it in details later. It is important to have your flat table BEFORE and your EAV table AFTER the equal sign. Don't use main_table. before the attribute. Magento will do this for you. More on this later.
Fields is an array. If you use a string instead, you get this: Warning: Invalid argument supplied for foreach() in /var/www/magento-1.6.2.0/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php on line 775. You can use an array of the format array('field1', 'field2', '...') or array('alias' => 'field1', '...')
Condition is a ** ~~WHERE~~ ON condition** in the SQL.
joinType. I hope you know what it is. But you have only the choice between LEFT and INNER. app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php:793

Magento-1.9 Collection – Magento Join Collection Query

In your query main_table.entity_id is the ID of an invoice whereas sales_flat_order_address.parent_id is the ID of an order. It doesn't make sense to join on those two columns.

Instead you need to use the order ID of each invoice:

$collection->getSelect()->join(
    'sales_flat_order_address',
// note this join clause!
    'main_table.order_id = sales_flat_order_address.parent_id',
    array('telephone', 'city', 'postcode', 'country_id')
)
->where("sales_flat_order_address.address_type = 'billing'");

Best Answer

Related Solutions

Magento1 Collection Query – How to Perform a Left Join

joinTable()

Magento-1.9 Collection – Magento Join Collection Query

Related Topic