Magento 2 – create a table in the database & handle data

Magento 2 – create a table in the database & handle data

thumbnail
Want to talk about your project?

In this topic, we will show you how to create a table in the database and add a model, resource, and collection of it.

Notice: Creating the module tutorial is here.

STEP 1. Create a database table.

In this step, we will create a database table. Firstly we need to create a file, where we can add a specific table with specific columns, types, etc.

The file should be like this example path:

app/code/[your_namespace]/[your_module]/Setup/InstallSchema.php

In our case it’s:

app/code/PandaGroup/MyAdminController/Setup/InstallSchema.php

The code inside:

<?php
namespace PandaGroup\MyAdminController\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\DB\Ddl\Table;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;


/**
 * Class InstallSchema
 * @package PandaGroup\MyAdminController\Setup
 */
class InstallSchema implements InstallSchemaInterface
{
    /**
     * @param SchemaSetupInterface $setup
     * @param ModuleContextInterface $context
     * @throws \Zend_Db_Exception
     */
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup;
        $installer->startSetup();
        if (!$installer->tableExists('pandagroup_myadmincontroller_blog')) {
            $table = $installer->getConnection()->newTable(
                $installer->getTable('pandagroup_myadmincontroller_blog')
            )
                ->addColumn(
                    'post_id',
                    Table::TYPE_INTEGER,
                    null,
                    [
                        'identity' => true,
                        'nullable' => false,
                        'primary'  => true,
                        'unsigned' => true,
                    ],
                    'Blog post id'
                )
                ->addColumn(
                    'title',
                    Table::TYPE_TEXT,
                    255,
                    ['nullable => false'],
                    'Blog post title'
                )
                ->addColumn(
                    'content',
                    Table::TYPE_TEXT,
                    '64k',
                    [],
                    'Blog post content'
                )
                ->addColumn(
                    'status',
                    Table::TYPE_INTEGER,
                    1,
                    [],
                    'Blog post status'
                )
                ->addColumn(
                    'slug',
                    Table::TYPE_TEXT,
                    255,
                    [],
                    'Blog post slug'
                )
                ->addColumn(
                    'tags',
                    Table::TYPE_TEXT,
                    255,
                    [],
                    'Blog post tags'
                )
                ->addColumn(
                    'image',
                    Table::TYPE_TEXT,
                    255,
                    [],
                    'Blog post image'
                )
                ->addColumn(
                    'created_at',
                    Table::TYPE_TIMESTAMP,
                    null,
                    ['nullable' => false, 'default' => Table::TIMESTAMP_INIT],
                    'Blog created at'
                )->addColumn(
                    'updated_at',
                    Table::TYPE_TIMESTAMP,
                    null,
                    ['nullable' => false, 'default' => Table::TIMESTAMP_INIT_UPDATE],
                    'Blog updated at')
                ->setComment('Blog post table');
            $installer->getConnection()->createTable($table);

            $installer->getConnection()->addIndex(
                $installer->getTable('pandagroup_myadmincontroller_blog'),
                $setup->getIdxName(
                    $installer->getTable('pandagroup_myadmincontroller_blog'),
                    ['title', 'content', 'slug', 'tags', 'image'],
                    \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
                ),
                ['title', 'content', 'slug', 'tags', 'image'],
                \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
            );
        }
        $installer->endSetup();
    }
}

As you can see the table name is pandagroup_myadmincontroller_blog with columns:

  • post_id
  • title
  • content
  • status
  • slug
  • tags
  • image
  • created_at
  • updated_at

STEP 2. Create Blog model.

Ok, so we need to create a model of our blog to manage data of it. We need to create model file with the path like:

app/code/[your_namespace]/[your_module]/Model/[entity_type].php

In our case it’s:

app/code/PandaGroup/MyAdminController/Model/Blog.php

The code inside:

<?php
namespace PandaGroup\MyAdminController\Model;

use Magento\Framework\Model\AbstractModel;
use Magento\Framework\DataObject\IdentityInterface;

class Blog extends AbstractModel implements IdentityInterface
{
    const CACHE_TAG = 'pandagroup_myadmincontroller_blog';

    protected $_cacheTag = 'pandagroup_myadmincontroller_blog';

    protected $_eventPrefix = 'pandagroup_myadmincontroller_blog';

    protected function _construct()
    {
        $this->_init('PandaGroup\MyAdminController\Model\ResourceModel\Blog');
    }

    public function getIdentities()
    {
        return [self::CACHE_TAG . '_' . $this->getId()];
    }

    public function getDefaultValues()
    {
        $values = [];

        return $values;
    }
}

STEP 3. Create Blog Resource Model

Resource model extends class which can manage, fetch all information from the database. We can here add our functions which e.g. we can use in model etc.

The path of this file should be like:
app/code/[your_namespace]/[your_module]/Model/ResourceModel/[entity_type].php

In our case it’s:

app/code/PandaGroup/MyAdminController/Model/ResourceModel/Blog.php

The code inside:

<?php
namespace PandaGroup\MyAdminController\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
use Magento\Framework\Model\ResourceModel\Db\Context;


/**
 * Class Blog
 * @package PandaGroup\MyAdminController\Model\ResourceModel
 */
class Blog extends AbstractDb
{
    /**
     * Blog constructor.
     * @param Context $context
     */
    public function __construct(
        Context $context
    ) {
        parent::__construct($context);
    }

    protected function _construct()
    {
        $this->_init('pandagroup_myadmincontroller_blog', 'post_id');
    }
}

STEP 4. Create Blog Resource Model Collection

We need this file to filter and fetch a collection table data. 

The path of this file should be like:
app/code/[your_namespace]/[your_module]/Model/ResourceModel/[entity_type]/Collection.php

In our case it’s:

app/code/PandaGroup/MyAdminController/Model/ResourceModel/Blog/Collection.php

The code inside:

<?php
namespace PandaGroup\MyAdminController\Model\ResourceModel\Blog;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;

class Collection extends AbstractCollection
{
    protected $_idFieldName = 'post_id';
    protected $_eventPrefix = 'pandagroup_myadmincontroller_blog_collection';
    protected $_eventObject = 'blog_collection';

    /**
     * Define resource model
     *
     * @return void
     */
    protected function _construct()
    {
        $this->_init('PandaGroup\MyAdminController\Model\Blog', 'PandaGroup\MyAdminController\Model\ResourceModel\Blog');
    }
}

And that’s all. Now you can update your project by typing in a command line:

php bin/magento setup:upgrade

After that, the database will be created. It makes it only once, so if you want to change something in your database (locally of course) before pushing it to the production, delete the table, remove from setup_module table row with your module and run above command again. If you push your changes to production, you can only make an upgrade of your table in another file named UpgradeSchema.php.

...