Item (Mage_Core_Model_Store_Group) with the same id “2” already exist

Question

I am joining core_store_group with my custom table called foundation and I am Getting values also. My custom table contain 3 fields ID, STORE_ID, EMAIL Where ID is primary key and store_id is references to group_id of core_store_group table.

$collection = Mage::getModel('core/store_group')->getCollection()
                ->addFilter('website_id', 2);
        $collection->getSelect()->join(
            array('foundation' => $collection->getTable('foundation/foundation')),
            'main_table.group_id = foundation.store_id');
        $this->setCollection($collection);
        parent::_prepareCollection();
        return $this;

But whenever I foundation table contains records with same store_id example

id  store_id    member_email
1   2        man@gmail.com
2   11       man@gmail.com
3   2        man@gmail.com

I am getting error

Item (Mage_Core_Model_Store_Group) with the same id "2" already exist.

How can I get the records without error and I need to fetch the count of email id's present in the same store.

Answer 1

Try to build your collection like this:

    $collection = Mage::getModel('foundation/foundation')->getCollection()
    $collection->getSelect()->join(
        array('store_group' => $collection->getTable('core/store_group')),
        'main_table.store_id = store_group.group_id');
    $collection->getSelect()->where('store_group.website_id = ?' , 2);
    $this->setCollection($collection);
    parent::_prepareCollection();
    return $this;

Answer 2

Magento collections cannot hold an item with the same entity id twice. Since you used the store group collection your query results in two store group objects with entity id 2. You could get rid of it with grouping by store_id but if you need all the data, you should not use the store group collection but rather a collection of your foundation model.

I need to fetch the count of email id's present in the same store

I am not sure what you mean with this. If you just want to count the entries in your foundation table for each store_id, the previously suggested "group by store_id" fits perfectly, if you add COUNT(*) as expression field to your collection.