Resource Models - Database Operations

Resource Model Purpose

Resource models handle all database operations for a data model. They know the table name, primary key, and how to interact with the database.

Basic Resource Model

<?php
namespace Amazon\Prep\Model\ResourceModel;

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

class Warranty extends AbstractDb
{
    protected function _construct(): void
    {
        $this->_init('vendor_warranty', 'warranty_id');
        // table name, primary key column
    }
}

The _init Method

protected function _construct(): void
{
    $this->_init($this->getMainTable(), $this->getIdFieldName());
}

// Or with direct values
protected function _construct(): void
{
    $this->_init('vendor_warranty', 'warranty_id');
}

Table Name Resolution

// Use table name directly
$this->_init('vendor_warranty', 'warranty_id');

// Or use resource connection helper
protected function _construct(): void
{
    $this->_init(
        $this->getResourceConnection()->getTableName('vendor_warranty'),
        'warranty_id'
    );
}

Primary Key Column

The second _init parameter is the primary key column. Common patterns:

• id — generic
• entity_id — EAV models
• {table}_id — specific (e.g., warranty_id, order_id)

Load

// Load a single record
$warranty = $this->warrantyFactory->create();
$this->resource->load($warranty, $warrantyId);

// Load by field other than primary key
$this->resource->load($warranty, $sku, 'sku');

// Check if loaded
if ($warranty->getId()) {
    // Record found
} else {
    // Record not found
}

Save

$warranty = $this->warrantyFactory->create();
$warranty->setName('Premium Warranty');
$warranty->setDuration(24);
$warranty->setStatus('active');
$this->resource->save($warranty);

// After save, ID is available
$warrantyId = $warranty->getId();

Delete

$warranty = $this->warrantyFactory->create();
$this->resource->load($warranty, $warrantyId);
$this->resource->delete($warranty);

Save Multiple Records

foreach ($items as $itemData) {
    $item = $this->itemFactory->create();
    $item->setData($itemData);
    $this->resource->save($item);
}

Transaction Example

use Magento\Framework\DB\Transaction;

class WarrantyService
{
    public function __construct(
        private Transaction $transaction,
        private WarrantyResource $warrantyResource,
        private WarrantyFactory $warrantyFactory
    ) {}

    public function createMultiple(array $dataList): void
    {
        $transaction->addObjectToSave($warranty)
            ->addCommitCallback([$this, 'afterCommit'])
            ->rollbackOnException();

        foreach ($dataList as $data) {
            $warranty = $this->warrantyFactory->create();
            $warranty->setData($data);
            $this->warrantyResource->save($warranty);
        }
    }
}

Collection Class

Collections represent a set of models with query building:

<?php
namespace Amazon\Prep\Model\ResourceModel\Warranty;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
use Amazon\Prep\Model\Warranty;
use Amazon\Prep\Model\ResourceModel\Warranty as WarrantyResource;

class Collection extends AbstractCollection
{
    protected function _construct(): void
    {
        $this->_init(Warranty::class, WarrantyResource::class);
    }
}

Querying with Collections

// Get all active warranties
$collection = $this->collectionFactory->create();
$collection->addFieldToFilter('status', 'active');

foreach ($collection as $warranty) {
    echo $warranty->getName();
}

// With conditions
$collection->addFieldToFilter('duration', ['gteq' => 12]);
$collection->addFieldToFilter('name', ['like' => '%Premium%']);

// Ordering
$collection->setOrder('created_at', 'DESC');

// Pagination
$collection->setPageSize(10);
$collection->setCurPage(2);

// Total count
$total = $collection->getSize();

Filter Conditions

// Equal
$collection->addFieldToFilter('status', 'active');

// Not equal
$collection->addFieldToFilter('status', ['neq' => 'inactive']);

// Greater than or equal
$collection->addFieldToFilter('duration', ['gteq' => 12]);

// In array
$collection->addFieldToFilter('status', ['in' => ['active', 'pending']]);

// Like
$collection->addFieldToFilter('name', ['like' => '%warranty%']);

// Is null
$collection->addFieldToFilter('deleted_at', ['null' => true]);

// Multiple conditions
$collection->addFieldToFilter([
    ['attribute' => 'status', 'eq' => 'active'],
    ['attribute' => 'duration', 'gteq' => 12],
]);

Custom Collection Methods

class Collection extends AbstractCollection
{
    protected function _construct(): void
    {
        $this->_init(Warranty::class, WarrantyResource::class);
    }

    public function addActiveFilter(): Collection
    {
        $this->addFieldToFilter('status', 'active');
        return $this;
    }

    public function addDurationFilter(int $minDuration): Collection
    {
        $this->addFieldToFilter('duration', ['gteq' => $minDuration]);
        return $this;
    }

    public function filterByDateRange(string $from, string $to): Collection
    {
        $this->addFieldToFilter('created_at', [
            'from' => $from,
            'to' => $to,
        ]);
        return $this;
    }
}

Usage:

$collection = $this->collectionFactory->create()
    ->addActiveFilter()
    ->addDurationFilter(12)
    ->setOrder('created_at', 'DESC');

Direct SQL Queries

namespace Amazon\Prep\Model\ResourceModel;

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

class Warranty extends AbstractDb
{
    protected function _construct(): void
    {
        $this->_init('vendor_warranty', 'warranty_id');
    }

    public function getActiveCount(): int
    {
        $connection = $this->getConnection();
        $select = $connection->select()
            ->from($this->getTable('vendor_warranty'), 'COUNT(*)')
            ->where('status = ?', 'active');

        return (int)$connection->fetchOne($select);
    }

    public function getByName(string $name): ?array
    {
        $connection = $this->getConnection();
        $select = $connection->select()
            ->from($this->getTable('vendor_warranty'))
            ->where('name = ?', $name)
            ->limit(1);

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

    public function updateStatus(int $id, string $status): bool
    {
        $connection = $this->getConnection();
        $table = $this->getTable('vendor_warranty');

        return $connection->update($table, [
            'status' => $status,
            'updated_at' => date('Y-m-d H:i:s'),
        ], ['warranty_id = ?' => $id]);
    }
}

Table Name Helper

// Get table name with prefix
$tableName = $this->getTable('vendor_warranty');
// Returns: prefixed_vendor_warranty

// Get connection
$connection = $this->getConnection();

// Get table name without prefix
$realName = $this->getResourceConnection()->getTableName('vendor_warranty');

Data Types

Collection Factory

// In your service class
class WarrantyService
{
    public function __construct(
        private CollectionFactory $collectionFactory
    ) {}

    public function getActiveWarranties(): Collection
    {
        return $this->collectionFactory->create()
            ->addFieldToFilter('status', 'active')
            ->setOrder('created_at', 'DESC');
    }
}