Magento Custom Grid Left Join ID Conflict with Custom Table

Question

I've got the following left join in my _prepareCollection(); method:

$collection->getSelect()->joinLeft(
        array('views_index' => "my_views_table"),
        'views_index.entity_id = main_table.entity_id',
        array('views'=>'count')
);

And then in my _prepareColumns() method I have:

$this->addColumn('views',
        array(
            'header'=> $this->__('Views'),
            'index' => 'views',
            'width' => '5%',
            'filter_index'  => 'views_index.count'
        )
);

When I load this page though, I get an error:

Item (Namespace_Module_Model_Mymodel) with the same id "{ID}" already exist

How is this conflict occurring?

Some background: Magento Custom Grid Redirect Error (On Join Attributes ONLY)

Would appreciate any help

Edit 1 The my_views_table consists of 3 columns, namely:

1. id (primary key)
2. entity_id
3. count

Answer 1

You are joining one table to another with 1-N relationship.

It means for one row from the table A you get N rows from the table B

At the same time you fetch values to the magento collection, that use unique row ID and it throws the error.

Probably you need to group values, so if it is page views you can group by page_id and calculate count with aggregate function.

Note, that with the group by statement in the original SQL query you need custom approach for the pager as the default wont work. So sometimes it easier (but not faster or better) to use subqueries.