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" ); | |
| } | |
| } |
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; | |
| } | |
| } |
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…
