in ,

How to Write Data to Custom Database Table and Create a Custom Collection in Magento 2

Write Data to Custom Database Table and Create a Custom Collection

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:

  1. Create a Custom Database Table using an install- or upgradeSchema,
  2. Create a Model and Resource Model,
  3. Write data using that Resource Model,
  4. Generate a Custom Collection using a Factory to read/use the written data.

Prerequisites

  1. You’re familiar with the basic requirements of a Magento 2-module (e.g. registration.php and etc/module.xml) and have them in place already,
  2. 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…

Buy me a beer?

Do you appreciate my work and support? Please consider supporting me by donating, so I can continue to develop and write useful solutions for you.

Choose amount

Personal Message

Thank you! 🙂

Written by Daan van den Bergh

Magento 2 Back-end Developer with a passion for trainlifting, airplane-gliding, hunting trees and creating fake hobbies.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.