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

Now you’ve created your custom database table, it needs to be filled with data. In this example we’re using a static array to keep this post nice and tidy. But of course, this array can be replaced with any data source you require for your Magento 2 module.

Write Data to a Custom Database Table in Magento 2

Before we can connect to our database table, we need to instantiate a connection to our data using Magento 2’s ResourceModels.

Create a Data Model

DaanvdB/ProductQty/Model/Data.php
<?php
namespace DaanvdB\ProductQty\Model;
use Magento\Framework\Model\AbstractModel;
class Data extends AbstractModel {
public function _construct() {
$this->_init( "\DaanvdB\ProductQty\Model\ResourceModel\Data" );
}
}
view raw Model-Data.php hosted with ❤ by GitHub

Create a Resource Model

DaanvdB/ProductQty/Model/ResourceModel/Data.php
<?php
namespace DaanvdB\ProductQty\Model\ResourceModel;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
class Data extends AbstractDb
{
public function _construct()
{
$this->_init('daanvdb_product_qty', 'sku');
}
}

The first parameter of the _init-method defines the custom table you created earlier. The second parameter identifies the fieldname of your table’s identifier.

Using Dependency Injection to Establish a Connection to the Resource Model

DaanvdB/ProductQty/Model/Import.php
<?php
namespace DaanvdB\ProductQty\Model;
use DaanvdB\ProductQty\Model\ResourceModel\Data;
class Import {
protected $resourceData;
public function __construct(
Data $resourceData
) {
$this->resourceData = $resourceData;
}
public function importData() {
$connection = $this->resourceData->getConnection();
$data = [
'test_sku_1' => 10,
'test_sku_2' => 20,
'test_sku_3' => 15
];
try {
$connection->beginTransaction();
$connection->insertMultiple( $this->resourceData->getTable( 'daanvdb_product_qty' ), $data );
$connection->commit();
} catch ( \Exception $e ) {
return $e;
}
return true;
}
}
view raw Model-Import.php hosted with ❤ by GitHub

We could’ve used a factory to generate an instance of our Data-model, fill that model with $data and then save it. This would mean writing to the database on each iteration of a foreach-loop through $data.

Magento 2 offers methods from the Magento/Framework/DB/Adapter-class. This jacks up performance by an infinite percentage compared to working with models. Especially if you’re looping through an array.

They allow us to commit our $data-array at once using the insertMultiple-method and as you can see, it’s quite easy. Other methods are available, so be sure to take a look!

Once you call importData anywhere in a Controller or Block, you’ll see that it will write data to the custom database table. Now we need to create a collection in order to use or manipulate the data…

Pages: 1 2 3