How to Write Data to Custom Database Table and Create a Custom Collection in Magento 2
Recently I created a Magento 2 module which imports data from a custom source, writes it to a custom database table and updates existing entities using a Custom Collection. Because I thought it might come in handy for other beginning Magento 2 developers, I’ll share it with you today.
This tutorial consists of a few parts:
- Create a Custom Database Table using an install- or upgradeSchema,
- Create a Model and Resource Model,
- Write data using that Resource Model,
- Generate a Custom Collection using a Factory to read/use the written data.
Prerequisites
- You’re familiar with the basic requirements of a Magento 2-module (e.g.
registration.phpandetc/module.xml) and have them in place already, - You have a basic understanding of the programming fundamentals for Magento 2-modules (e.g. dependency injection, namespaces, OOP and installSchemas).
Create a Custom Database Table in Magento 2 with install/upgrade schemas
Before we can write data to it, we must create a custom database table. That way, we can create a collection that exactly suits our needs.
In this example we are creating a module, which will create a table containing a list of Product SKU’s and quantities.
Assuming you’ve already created your module’s registration.php and etc/module.xml we must do the following:
Create an Install/Upgrade Schema
InstallSchemas run once upon install. UpgradeSchemas runs every time bin/magento setup:upgrade runs. That’s why in this example I’ll create an UpgradeSchema-class. The build up of an InstallSchema is basically the same.
DaanvdB/ProductQty/Setup/UpgradeSchema.php
| <?php | |
| namespace DaanvdB\ProductQty\Setup; | |
| use Magento\Framework\Setup\UpgradeSchemaInterface; | |
| use Magento\Framework\Setup\SchemaSetupInterface; | |
| use Magento\Framework\Setup\ModuleContextInterface; | |
| class UpgradeSchema implements UpgradeSchemaInterface { | |
| public function upgrade( SchemaSetupInterface $setup, ModuleContextInterface $context ) { | |
| $setup->startSetup(); | |
| // Create table and columns. | |
| $table = $setup->getConnection()->newTable( | |
| $setup->getTable( 'daanvdb_product_qty' ) | |
| )->addColumn( | |
| 'sku', | |
| \Magento\Framework\DB\Ddl\Table::TYPE_TEXT, | |
| null, | |
| [ | |
| 'nullable' => false, | |
| 'primary' => false | |
| ], | |
| 'Product SKU' | |
| )->addColumn( | |
| 'qty', | |
| \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER, | |
| null, | |
| [ | |
| 'nullable' => false, | |
| 'primary' => false | |
| ] | |
| ); | |
| // UPDATE OCT 4TH, 2018: And if you want to add a Foreign Key Constraint | |
| $setup->addForeignKey( | |
| $setup->getFkName( 'daanvdb_product_qty', 'quote_id', 'other_table', 'other_column' ), | |
| 'quote_id', | |
| $setup->getTable( 'other_table' ), | |
| 'other_column', | |
| \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE //onDelete | |
| ); | |
| $setup->getConnection()->createTable( $table ); | |
| // And if you want to add a column to an existing table... | |
| if ( version_compare( $context->getVersion(), '2.0' < 0 ) ) { | |
| $table = $setup->getTable( 'daanvdb_product_qty' ); | |
| $setup->getConnection()->addColumn( | |
| $table, | |
| 'custom_column', | |
| [ | |
| 'type' => \Magento\Framework\DB\Ddl\Table::TYPE_BOOLEAN, | |
| 'nullable' => false, | |
| 'comment' => 'Custom Column' | |
| ] | |
| ); | |
| } | |
| $setup->endSetup(); | |
| } | |
| } |
Now you can run bin/magento setup:upgrade and Magento 2 will automatically create a custom database table, containing two columns (sku and qty). Now let’s fill it with data…
