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.

  • The Wait is Over.

    Get the Newsletter you've always wanted, now!

    Sign up to receive Biweekly, Free Optimization Tips for WordPress.

    No spam. I promise.

  • 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…

    Daan van den Bergh

    Daan van den Bergh is a carefully seasoned web developer. His methods consist of thinly slicing your website’s beef and serve you with a platter of the best performance carpaccio on a bed of rocket — the only thing he’s more passionate about is food. Hire him at ffwp.dev.

    Leave a Comment

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