The Model System

It should come as no surprise when I say that your application code certainly should not use a strategy of raw MySQL query strings to deal with the database. We have an abstraction layer for that, which eliminates the potential for injection attacks with automatic escaping and use of prepared statements.

Magento's lowest level DB layer uses an expressive syntax that looks like this, in a nutshell:

...
use Magento\Framework\App\ResourceConnection;

class MyClass
{
    private ResourceConnection $resource;

    public function __construct(
        ResourceConnection $resource
    ) {
        $this->resource = $resource;
    }

    public function execute() : array
    {
        $connection = $this->resource->getConnection();
        $select = $connection->select()
            ->from(
                $connection->getTableName('productquestions_post'),
                ['id', 'customer_nickname', 'content']
            )
            ->where('product_id IN (?)', [1, 2])
            ->limit(10);

        return $connection->fetchAll($select);
    }
}

We start with Magento\Framework\App\ResourceConnection, and the getConnection method of this class returns an instance of Magento\Framework\DB\Adapter\AdapterInterface. The select method of this adapter object returns a Magento\Framework\DB\Select instance that, as you can see, supports chaining several methods matching various MySQL expressions. Note the use of a "?" placeholder in the where call, with the values to bind being passed as successive arguments. The fetchAll method of AdapterInterface performs the query and returns a multidimensional array of results.

You can see that a Select instance is a stateful object storing up the relevant manipulations that will affect the final query. For logging and debugging purposes, it's useful to be aware of the method on Select that will assemble and output the final SQL statement as a string:

$select->assemble();

There's plenty more to explore about the capabilities of these database classes. There are strategies for WHERE clauses combined with OR or AND. The Select class also has a join method for more complex queries. AdapterInterface defines insert, update and delete methods in addition to select.

Yet this is as far as we're going to delve into this layer in this beginner course, because in reality, most of your application code managing database entities will be done with another layer instead: relational models. As you get into more and more complex requirements, you might well find yourself modifying the underlying queries of your resource models (a term we'll define shortly) with elements of this syntax, but rarely will you be constructing select statements from scratch as we see above.

The Model Triad

Working with database records in Magento centers around three types of "model" classes that exist for any given entity:

• The Model, or "Data Model" as we'll call it, is a stateful transport object representing a unique entity record. Inspecting data fetched from the database and preparing data to be written to it is a matter of getting and setting values on these relational objects.
• The Resource Model is a singleton responsible for constructing and executing the actual SQL queries using the components and expressions we saw above. The resource model hydrates instances of the data model or consumes those instances to persist their data.
• The Collection Model manages multi-record queries, performing the SQL operations necessary to load, filter and sort lists of records and using them to hydrate multiple data model objects.

When you create a new entity, you must bootstrap all three of these classes with some minimal code to wire them up to each other and to the right database table. Each of them extends a core abstract class providing the necessary built-in logic.

The Resource Model

We'll create the resource model for our Post entity first, as it's the class that doesn't depend on either of the others. This will demonstrate exactly how simple the minimum code is. Create the file for SwiftOtter\ProductQuestions\Model\ResourceModel\Post with this definition:

<?php
declare(strict_types=1);

namespace SwiftOtter\ProductQuestions\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class Post extends AbstractDb
{
    /**
     * {@inheritdoc}
     */
    protected function _construct()
    {
        $this->_init('productquestions_post', 'id');
    }
}

Look very carefully at the above code and note that we're not looking at a PHP native constructor, but rather a custom protected method prefixed with a single instead of double underscore! Within this _construct method, the call to _init passes in the name of the table for this entity, then the column name of the primary key.

The class name is purely convention, but one used absolutely universally: the singular name of the entity, in the directory Model/ResourceModel.

That's all we need!

The Data Model

The definition of the data model doesn't look phenomenally different from the resource model. Create the file for SwiftOtter\ProductQuestions\Model\Post with these contents:

<?php
declare(strict_types=1);

namespace SwiftOtter\ProductQuestions\Model;

use Magento\Framework\Model\AbstractModel;

class Post extends AbstractModel
{
    /**
     * {@inheritdoc}
     */
    protected function _construct()
    {
        $this->_init(
            \SwiftOtter\ProductQuestions\Model\ResourceModel\Post::class
        );
    }
}

We've defined a similar _construct method (single underscore!) with an _init call, this time simply passing in the class name of the resource model. (This remains a standard part of defining a model, though the internal methods that use the resource model directly from within such classes have mostly been deprecated.)

Note the class has the same name as the resource model, but directly in Model.

An important thing to note is that data models extend Magento\Framework\DataObject. We last encountered this generic class as an ancestor of blocks used in layout rendering. Recall that DataObject allows getting and setting properties with arbitrary names. This is central to how the underlying functionality of models works. A model loaded from the database will automatically be hydrated with properties matching the names of all table columns. So for our Post model, we would expect to call getData('customer_nickname') or getCustomerNickname() to access the value of the nickname column. Likewise, setting data in the same way will result in values being stored in the matching columns when the model is saved.

Simple CRUD Operations

We'll create the collection class next, but for now the data model and resource model are enough to get a feel for how basic CRUD operations work with this system.

The resource model is injected and used as a singleton, while a factory is used for instantiating data model objects. When loading records, we instantiate an empty object and then allow the resource model to hydrate it. Here's a simple example:

...

use SwiftOtter\ProductQuestions\Model\Post;
use SwiftOtter\ProductQuestions\Model\PostFactory;
use SwiftOtter\ProductQuestions\Model\ResourceModel\Post 
    as PostResource;

class MyClass
{
    private PostFactory $postFactory;
    private PostResource $postResource;

    public function __construct(
        PostFactory $postFactory,
        PostResource $postResource
    ) {
        $this->postFactory = $postFactory;
        $this->postResource = $postResource;
    }

    public function execute(int $id) : string
    {
        /** @var Post $post */
        $post = $this->postFactory->create();
        $this->postResource->load($post, $id);

        return $post->getCustomerNickname() 
            . ' says: ' 
            . $post->getContent();
    }
}

load accepts the model to hydrate and the value of the column declared as the primary key in the resource model. A different column name can be passed in as a third argument if you want to load a record based on a different value.

Note that the load method will not throw an exception or otherwise fail if a record matching the ID is not found. You should make a check for whether the model has an ID value after the operation.

DataObject Shenanigans

You might note that the above example uses the "magic methods" getCustomerNickname and getContent. Specifically when dealing with data models, we will continue to do so in this course. This despite our admonishment to avoid them in favor of the explicitly defined getData! This is because this syntax more closely mimics the syntax you'll be using when you advance to including a service layer with service contracts and explicit accessor methods for your entities. More on this topic later, but we want that eventual transition to feel as natural as possible.

The technique for persisting data is no surprise:

$post->setCustomerNickname('Otis');
$this->postResource->save($post);

It doesn't matter whether the $post object above was newly created or previously loaded by the resource model. The save operation will either update an existing record or insert a new one as appropriate.

Deleting works the same way, also accepting a full data model instance:

$this->postResource->delete($post);

These are the essential building blocks of loading and persisting your entity data. That leaves collection models as the missing member of the triad, and we'll see shortly how they function both similarly and uniquely.

Tip

n98-magerun is a CLI tool that enhances the capabilities of bin/magento with a number of other useful commands. It's installed automatically if you're using Warden and can be run from your webroot with the command n98-magerun.

A very useful command available with this tool is n98-magerun dev:console, which starts an interactive PHP console where the Magento application is bootstrapped and your own custom code can be executed in real time. The $di variable that's automatically available within the console is an instance of the Magento Object Manager, which you can use to fetch dependencies with get that you would normally inject in a class constructor. From there, you can write any code you'd normally write in PHP!

This tool can be useful in our current scenario, if you want to practice basic CRUD techniques with our new models before we get into a practical scenario. The GitHub linked above has more details, and we'll walk through using this tool in the accompanying video.

SEE THE CODE