Models and the database

A model is a class with database queries. The controller does not write SQL: it calls a model method and gets an array back. OpenCart has no query builder and no objects for records — queries are written as plain SQL strings.

Where it lives

Storefront models are in catalog/model/. The running example of the course is product reviews, so the model goes next to the core catalog models:

catalog/model/catalog/
├── product.php            core model
├── review.php             core model
└── review_summary.php     the new file

Creating the model

Create catalog/model/catalog/review_summary.php by hand:

// catalog/model/catalog/review_summary.php
namespace Opencart\Catalog\Model\Catalog;
 
class ReviewSummary extends \Opencart\System\Engine\Model
{
    public function getTotal(): int
    {
        $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "review`");
 
        return (int) $query->row['total'];
    }
}

The method counts the rows of the reviews table. DB_PREFIX is the table prefix from config.php, usually oc_.

Calling from the controller

// catalog/controller/information/hello.php
$this->load->model('catalog/review_summary');
 
$data['review_total'] = $this->model_catalog_review_summary->getTotal();

After loading, the model is available as a property: the word model and the path, with slashes replaced by underscores. Print {{ review_total }} in the template and open index.php?route=information/hello — the page shows the number of reviews.

The result of a query

$this->db->query() returns an object with three properties:

public function getLatest(int $limit): array
{
    $query = $this->db->query(
        "SELECT `author`, `rating`, `text` FROM `" . DB_PREFIX . "review`
         WHERE `status` = '1' ORDER BY `date_added` DESC LIMIT " . (int) $limit
    );
 
    return $query->rows;
}

The method returns the latest published reviews; in the template they are printed with a for loop.

Values in a query

There are no placeholders. Each value is protected by hand before it is put into the string:

public function getByAuthor(string $author, int $productId): array
{
    $query = $this->db->query(
        "SELECT * FROM `" . DB_PREFIX . "review`
         WHERE `author` = '" . $this->db->escape($author) . "'
         AND `product_id` = '" . (int) $productId . "'"
    );
 
    return $query->rows;
}

Writing data

INSERT, UPDATE and DELETE go through the same query(). After them two more methods are useful:

Common mistake

The table name is written without the prefix: FROM review or FROM oc_review. On the developer's machine it works, and in a shop with another prefix the query fails. Always write DB_PREFIX . "review".