admin grid is not displaying all rows from database

Question

I have created an admin grid, but it is only displaying a single row from the database. At the top of the table it displays

Total 1 records found

but there are three rows in my database. I have checked my code and it seems to be okay.

When looking for an answer to this problem I found this but I cannot get his solution to work for me.

What I have

class Namespace_Module_Block_Adminhtml_Module extends Mage_Adminhtml_Block_Widget_Grid_Container
{
    protected function _construct()
    {
        $this->_blockGroup = 'namespace_module';
        $this->_controller = 'adminhtml_namespace';
        $this->_headerText = $this->__('text');
        $this->_addButtonLabel = $this->__('button text');

        parent::_construct();
    }
}

class Namespace_Module_Block_Adminhtml_Module_Grid extends Mage_Adminhtml_Block_Widget_Grid
{
protected function _construct()
{
    parent::_construct();        
    $this->setId('namespace_module_list');
    $this->setDefaultSort('id');        
    $this->setUseAjax(true);
}

protected function _prepareCollection()
{
    $collection = Mage::getModel('namespace_module/shop')->getCollection();
    $collection->getSelect()->join(
            array('la' => 'shop_locator_area'),
            'main_table.area_id = la.area_id',
            array('area' => 'la.name')
        )
        ->join(
            array('lr' => 'shop_locator_region'),
            'main_table.region_id = lr.region_id',
            array('region' => 'lr.name')
        )
        ->join(
            array('lm' => 'shop_locator_media'),
            'main_table.shop_id = lm.shop_id',
            array('media' => 'lm.location')
        );

        $this->setCollection($collection);
    return parent::_prepareCollection();
}

protected function _prepareColumns()
{
    $this->addColumn('shop_id', array(
        'header'   => $this->__('ID'),
        'sortable' => true,
        'width'    => '60px',
        'index'    => 'shop_id'
    ));

    $this->addColumn('area', array(
        'header'   => $this->__('Area'),
        'sortable' => true,
        'width'    => 'auto',
        'index'    => 'area'
    ));

    ...other columns here .....

    $this->addColumn('action', array(
        'header'   => $this->__('Action'), 
        'width'    => '50px',
        'type'     => 'action',
        'getter'   => 'getId',
        'actions'  => array(
                        array('caption' => $this->__('Edit'),
                              'url'     => array('base' => '*/*/edit'),
                              'field'   => 'id'),
                        array('caption' => $this->__('Delete'), 
                              'url'     => array('base' => '*/*/delete'),
                              'field'   => 'id'),
                      ),
        'filter'   => false,
        'sortable' => false,
        )
    );

    return parent::_prepareColumns();
}

public function getGridUrl()
{
    return $this->getUrl('*/*/grid', array('_current' => true));
}

public function getRowUrl($row)
{
    return $this->getUrl('*/*/edit', array('id' => $row->getId()));
}

}

Answer 1

I hope someone can add a more complete answer, but as this has resolved my issue I thought I would share how it was done.

My problem was the join statement

   $collection->getSelect()->join(
        array('la' => 'shop_locator_area'),
        'main_table.area_id = la.area_id',
        array('area' => 'la.name')
    )
    ->join(
        array('lr' => 'shop_locator_region'),
        'main_table.region_id = lr.region_id',
        array('region' => 'lr.name')
    )
    ->join(
        array('lm' => 'shop_locator_media'),
        'main_table.shop_id = lm.shop_id',
        array('media' => 'lm.location')
    );

shop_locator_media was not required for my grid and as a result only the item within the table would be returned. I believe this is something to do with a left join being the default. I cannot be anymore specific and hope someone will offer a more complete answer.

Answer 2

I had a same problem like that! I solved it finding out the configuration in config.xml , I have set a different DB resource!! like that:

    <resources>
        <home_write>
            <connection>
                <use>some_setup</use>
            </connection>
        </home_write>
        <home_read>
            <connection>
                <use>some_setup</use>
            </connection>
        </home_read>
    </resources>

I modified to <use>default_setup</use>, It solved!