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()));
}

}

• Sukeshini: In your _prepareCollection() function log the generated SQL query and manually run that query in PhpMyAdmin to see what does it returns
• Sukeshini: Try` return $this;` instead ` return parent::_prepareCollection();` in _prepareCollection() function
• tony09uk: how do I log my query? Mage::log('what goes here')?
• user2045: Mage::log($collection->getSelect())
• Sukeshini: If you want the SQL query from a collection you need to cast it as string. Mage::log((string)$collection->getSelect(),null,'test.log',true);

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!