I have created a tab in the admin panel (Add tab to Product Information in admin panel) and now want to create a grid to show, filter, etc the data from a custom table.
How do I extend my module to show a grid driven by a custom table of data?
Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. It only takes a minute to sign up.
Sign up to join this communityI have created a tab in the admin panel (Add tab to Product Information in admin panel) and now want to create a grid to show, filter, etc the data from a custom table.
How do I extend my module to show a grid driven by a custom table of data?
Assuming you already have your entity and a grid for it in the backend here is what you need.
In my example, my entity is called article
and the module is Easylife_Press
. Adjust the names to fit your module.
First you need a new table to remember the relation between products and your entities. For this add in your config.xml
the table declaration inside the resource model tag. (models->press->press_resource->entities
)
<article_product><!-- relation table -->
<table>press_article_product</table>
</article_product>
Now you need to create the table. In one of your upgrade scripts add this:
$this->run("
CREATE TABLE {$this->getTable('press/article_product')} (
`rel_id` int(11) unsigned NOT NULL auto_increment,
`article_id` int(11) unsigned NOT NULL,
`product_id` int(11) unsigned NOT NULL,
`position` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`rel_id`),
UNIQUE KEY `UNIQUE_ARTICLE_PRODUCT` (`article_id`,`product_id`),
CONSTRAINT `PRESS_ARTICLE_ARTICLE_PRODUCT` FOREIGN KEY (`article_id`) REFERENCES {$this->getTable('press/article')} (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `PRESS_ARTICLE_PRODUCT_ARTICLE` FOREIGN KEY (`product_id`) REFERENCES {$this->getTable('catalog_product_entity')} (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
");
If the PK of your entity table is not entity_id
change it. Sorry that I don't have the DDL install script. It's easier to understand this way. If you want I will write one.
Now you need a model to handle this table.
app/code/local/Easylife/Press/Model/Article/Product.php
<?php
class Easylife_Press_Model_Article_Product extends Mage_Core_Model_Abstract{
protected function _construct(){
$this->_init('press/article_product');
}
/**
* Save data for article-product relation
* @access public
* @param Easylife_Press_Model_Article $article
* @return Easylife_Press_Model_Article_Product
*
*/
public function saveArticleRelation($article){
$data = $article->getProductsData();
if (!is_null($data)) {
$this->_getResource()->saveArticleRelation($article, $data);
}
return $this;
}
/**
* get products for article
* @access public
* @param Easylife_Press_Model_Article $article
* @return Easylife_Press_Model_Resource_Article_Product_Collection
*
*/
public function getProductCollection($article){
$collection = Mage::getResourceModel('press/article_product_collection')
->addArticleFilter($article);
return $collection;
}
}
And a model that maps on a table requires a resource model.
app/code/local/Easylife/Press/Model/Resource/Article/Product.php
<?php
class Easylife_Press_Model_Resource_Article_Product extends Mage_Core_Model_Resource_Db_Abstract{
/**
* initialize resource model
* @access protected
* @return void
* @see Mage_Core_Model_Resource_Abstract::_construct()
*
*/
protected function _construct(){
$this->_init('press/article_product', 'rel_id');
}
/**
* Save article - product relations
* @access public
* @param Easylife_Press_Model_Article $article
* @param array $data
* @return Easylife_Press_Model_Resource_Article_Product
*
*/
public function saveArticleRelation($article, $data){
if (!is_array($data)) {
$data = array();
}
$deleteCondition = $this->_getWriteAdapter()->quoteInto('article_id=?', $article->getId());
$this->_getWriteAdapter()->delete($this->getMainTable(), $deleteCondition);
foreach ($data as $productId => $info) {
$this->_getWriteAdapter()->insert($this->getMainTable(), array(
'article_id' => $article->getId(),
'product_id' => $productId,
'position' => @$info['position']
));
}
return $this;
}
/**
* Save product - article relations
* @access public
* @param Mage_Catalog_Model_Product $prooduct
* @param array $data
* @return Easylife_Press_Model_Resource_Article_Product
* @
*/
public function saveProductRelation($product, $data){
if (!is_array($data)) {
$data = array();
}
$deleteCondition = $this->_getWriteAdapter()->quoteInto('product_id=?', $product->getId());
$this->_getWriteAdapter()->delete($this->getMainTable(), $deleteCondition);
foreach ($data as $articleId => $info) {
$this->_getWriteAdapter()->insert($this->getMainTable(), array(
'article_id' => $articleId,
'product_id' => $product->getId(),
'position' => @$info['position']
));
}
return $this;
}
}
...and a collection resource model:
app/code/local/Easylife/Press/Model/Resource/Article/Product/Collection.php
<?php
class Easylife_Press_Model_Resource_Article_Product_Collection extends Mage_Catalog_Model_Resource_Product_Collection{
/**
* remember if fields have been joined
* @var bool
*/
protected $_joinedFields = false;
/**
* join the link table
* @access public
* @return Easylife_Press_Model_Resource_Article_Product_Collection
*
*/
public function joinFields(){
if (!$this->_joinedFields){
$this->getSelect()->join(
array('related' => $this->getTable('press/article_product')),
'related.product_id = e.entity_id',
array('position')
);
$this->_joinedFields = true;
}
return $this;
}
/**
* add article filter
* @access public
* @param Easylife_Press_Model_Article | int $article
* @return Easylife_Press_Model_Resource_Article_Product_Collection
*
*/
public function addArticleFilter($article){
if ($article instanceof Easylife_Press_Model_Article){
$article = $article->getId();
}
if (!$this->_joinedFields){
$this->joinFields();
}
$this->getSelect()->where('related.article_id = ?', $article);
return $this;
}
}
Then you need to add a new tab in the product add/edit form and save the values from it. For this you need to observe the event core_block_abstract_prepare_layout_after
. Add the following to your config.xml
file inside the <adminhtml>
tag.
<events>
<core_block_abstract_prepare_layout_after> <!-- this will add the tab-->
<observers>
<article>
<type>singleton</type>
<class>press/adminhtml_observer</class>
<method>addArticleBlock</method>
</article>
</observers>
</core_block_abstract_prepare_layout_after>
<catalog_product_save_after><!-- this will save the relations -->
<observers>
<article>
<type>singleton</type>
<class>press/adminhtml_observer</class>
<method>saveArticleData</method>
</article>
</observers>
</catalog_product_save_after>
</events>
Now the observer that adds the tab and saves the relations
app/code/local/Easylife/Press/Model/Adminhtml/Observer.php
<?php
class Easylife_Press_Model_Adminhtml_Observer{
/**
* check if tab can be added
* @access protected
* @param Mage_Catalog_Model_Product $product
* @return bool
*
*/
protected function _canAddTab($product){
if ($product->getId()){
return true;
}
if (!$product->getAttributeSetId()){
return false;
}
$request = Mage::app()->getRequest();
if ($request->getParam('type') == 'configurable'){
if ($request->getParam('attribtues')){
return true;
}
}
return false;
}
/**
* add the article tab to products
* @access public
* @param Varien_Event_Observer $observer
* @return Easylife_Press_Model_Adminhtml_Observer
*
*/
public function addArticleBlock($observer){
$block = $observer->getEvent()->getBlock();
$product = Mage::registry('product');
if ($block instanceof Mage_Adminhtml_Block_Catalog_Product_Edit_Tabs && $this->_canAddTab($product)){
$block->addTab('articles', array(
'label' => Mage::helper('press')->__('Articles'),
'url' => Mage::helper('adminhtml')->getUrl('adminhtml/press_article_catalog_product/articles', array('_current' => true)),
'class' => 'ajax',
));
}
return $this;
}
/**
* save article - product relation
* @access public
* @param Varien_Event_Observer $observer
* @return Easylife_Press_Model_Adminhtml_Observer
*
*/
public function saveArticleData($observer){
$post = Mage::app()->getRequest()->getPost('articles', -1);
if ($post != '-1') {
$post = Mage::helper('adminhtml/js')->decodeGridSerializedInput($post);
$product = Mage::registry('product');
$articleProduct = Mage::getResourceSingleton('press/article_product')->saveProductRelation($product, $post);
}
return $this;
}
}
One helper to make it easier to get the related entities
app/code/local/Easylife/Press/Helper/Product.php
:
<?php
class Easylife_Press_Helper_Product extends Easylife_Press_Helper_Data{
/**
* get the selected articles for a product
* @access public
* @param Mage_Catalog_Model_Product $product
* @return array()
*
*/
public function getSelectedArticles(Mage_Catalog_Model_Product $product){
if (!$product->hasSelectedArticles()) {
$articles = array();
foreach ($this->getSelectedArticlesCollection($product) as $article) {
$articles[] = $article;
}
$product->setSelectedArticles($articles);
}
return $product->getData('selected_articles');
}
/**
* get article collection for a product
* @access public
* @param Mage_Catalog_Model_Product $product
* @return Easylife_Press_Model_Resource_Article_Collection
*/
public function getSelectedArticlesCollection(Mage_Catalog_Model_Product $product){
$collection = Mage::getResourceSingleton('press/article_collection')
->addProductFilter($product);
return $collection;
}
}
Now the actual grid block.
app/code/local/Easylife/Press/Block/Adminhtml/Catalog/Product/Edit/Tab/Article.php
<?php
class Easylife_Press_Block_Adminhtml_Catalog_Product_Edit_Tab_Article extends Mage_Adminhtml_Block_Widget_Grid {
/**
* Set grid params
* @access protected
* @return void
*
*/
public function __construct(){
parent::__construct();
$this->setId('article_grid');
$this->setDefaultSort('position');
$this->setDefaultDir('ASC');
$this->setUseAjax(true);
if ($this->getProduct()->getId()) {
$this->setDefaultFilter(array('in_articles'=>1));
}
}
/**
* prepare the article collection
* @access protected
* @return Easylife_Press_Block_Adminhtml_Catalog_Product_Edit_Tab_Article
*
*/
protected function _prepareCollection() {
$collection = Mage::getResourceModel('press/article_collection');
if ($this->getProduct()->getId()){
$constraint = 'related.product_id='.$this->getProduct()->getId();
}
else{
$constraint = 'related.product_id=0';
}
$collection->getSelect()->joinLeft(
array('related'=>$collection->getTable('press/article_product')),
'related.article_id=main_table.entity_id AND '.$constraint,
array('position')
);
$this->setCollection($collection);
parent::_prepareCollection();
return $this;
}
/**
* prepare mass action grid
* @access protected
* @return Easylife_Press_Block_Adminhtml_Catalog_Product_Edit_Tab_Article
*
*/
protected function _prepareMassaction(){
return $this;
}
/**
* prepare the grid columns
* @access protected
* @return Easylife_Press_Block_Adminhtml_Catalog_Product_Edit_Tab_Article
*
*/
protected function _prepareColumns(){
$this->addColumn('in_articles', array(
'header_css_class' => 'a-center',
'type' => 'checkbox',
'name' => 'in_articles',
'values'=> $this->_getSelectedArticles(),
'align' => 'center',
'index' => 'entity_id'
));
$this->addColumn('title', array(
'header'=> Mage::helper('press')->__('Title'),
'align' => 'left',
'index' => 'title',
));
$this->addColumn('position', array(
'header' => Mage::helper('press')->__('Position'),
'name' => 'position',
'width' => 60,
'type' => 'number',
'validate_class'=> 'validate-number',
'index' => 'position',
'editable' => true,
));
}
/**
* Retrieve selected articles
* @access protected
* @return array
*
*/
protected function _getSelectedArticles(){
$articles = $this->getProductArticles();
if (!is_array($articles)) {
$articles = array_keys($this->getSelectedArticles());
}
return $articles;
}
/**
* Retrieve selected articles
* @access protected
* @return array
*
*/
public function getSelectedArticles() {
$articles = array();
//used helper here in order not to override the product model
$selected = Mage::helper('press/product')->getSelectedArticles(Mage::registry('current_product'));
if (!is_array($selected)){
$selected = array();
}
foreach ($selected as $article) {
$articles[$article->getId()] = array('position' => $article->getPosition());
}
return $articles;
}
/**
* get row url
* @access public
* @return string
*
*/
public function getRowUrl($item){
return '#';
}
/**
* get grid url
* @access public
* @return string
*
*/
public function getGridUrl(){
return $this->getUrl('*/*/articlesGrid', array(
'id'=>$this->getProduct()->getId()
));
}
/**
* get the current product
* @access public
* @return Mage_Catalog_Model_Product
*
*/
public function getProduct(){
return Mage::registry('current_product');
}
/**
* Add filter
* @access protected
* @param object $column
* @return Easylife_Press_Block_Adminhtml_Catalog_Product_Edit_Tab_Article
*
*/
protected function _addColumnFilterToCollection($column){
if ($column->getId() == 'in_articles') {
$articleIds = $this->_getSelectedArticles();
if (empty($articleIds)) {
$articleIds = 0;
}
if ($column->getFilter()->getValue()) {
$this->getCollection()->addFieldToFilter('entity_id', array('in'=>$articleIds));
}
else {
if($articleIds) {
$this->getCollection()->addFieldToFilter('entity_id', array('nin'=>$articleIds));
}
}
}
else {
parent::_addColumnFilterToCollection($column);
}
return $this;
}
}
And last, a controller to handle the ajax requests for your custom grid.
app/code/local/Easylife/Press/controllers/Adminhtml/Press/Article/Catalog/ProductController.php
<?php
require_once ("Mage/Adminhtml/controllers/Catalog/ProductController.php");
class Easylife_Press_Adminhtml_Press_Article_Catalog_ProductController extends Mage_Adminhtml_Catalog_ProductController{
/**
* construct
* @access protected
* @return void
*
*/
protected function _construct(){
// Define module dependent translate
$this->setUsedModuleName('Easylife_Press');
}
/**
* articles in the catalog page
* @access public
* @return void
*
*/
public function articlesAction(){
$this->_initProduct();
$this->loadLayout();
$this->getLayout()->getBlock('product.edit.tab.article')
->setProductArticles($this->getRequest()->getPost('product_articles', null));
$this->renderLayout();
}
/**
* articles grid in the catalog page
* @access public
* @return void
*
*/
public function articlesGridAction(){
$this->_initProduct();
$this->loadLayout();
$this->getLayout()->getBlock('product.edit.tab.article')
->setProductArticles($this->getRequest()->getPost('product_articles', null));
$this->renderLayout();
}
}
Just kidding, that wasn't the last thing. You still need to define the layouts for the controller actions. In one of the admin layout files add this:
<adminhtml_press_article_catalog_product_articles>
<block type="core/text_list" name="root" output="toHtml">
<block type="press/adminhtml_catalog_product_edit_tab_article" name="product.edit.tab.article"/>
<block type="adminhtml/widget_grid_serializer" name="article_grid_serializer">
<reference name="article_grid_serializer">
<action method="initSerializerBlock">
<grid_block_name>product.edit.tab.article</grid_block_name>
<data_callback>getSelectedArticles</data_callback>
<hidden_input_name>articles</hidden_input_name>
<reload_param_name>product_articles</reload_param_name>
</action>
<action method="addColumnInputName">
<input_name>position</input_name>
</action>
</reference>
</block>
</block>
</adminhtml_press_article_catalog_product_articles>
<adminhtml_press_article_catalog_product_articlesgrid>
<block type="core/text_list" name="root" output="toHtml">
<block type="press/adminhtml_catalog_product_edit_tab_article" name="product.edit.tab.article"/>
</block>
</adminhtml_press_article_catalog_product_articlesgrid>
I hope I didn't forget any files. Clear the cache and give it a try.
Seams complicated. Well it is. But it can be easy. The code above was auto-generated by the Ultimate Module Creator extension. All you need to do with it is to configure the fields of your entity and say that you want a many to may relation with the products. I hope this is not considered self promotion, because the extension is free, you can get it directly from Magento connect, and it really solves this kind of problems.
I always use this tutorial as the basis for extensions with custom tables. It covers all the steps you need in creating the block
classes required for a custom grid and edit form.
I advise you to start over with the extension instead of adding this tutorial's code to your current adminhtml code to prevent conflicts.