Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 30 ยท ObjectModel and Data

ObjectModel and Entities

Define your own database entities with PrestaShop's ObjectModel: the $definition array, field types, validation, multilingual fields, CRUD methods, Db and pSQL.

Most useful modules store data: wishlists, delivery rules, reviews, export logs. PrestaShop's classic way to map a PHP class to a database table is ObjectModel. You describe a table in a static $definition array, and the base class gives you add(), update(), delete(), loading by id, validation, multilingual fields and multistore support. Core entities such as Product, Cart, Customer and Order are ObjectModels, so learning it also teaches you to read the core.

This lesson covers the $definition array, the field types and validation, the CRUD methods, multilingual and multistore tables, and the lower-level Db class with pSQL() for custom queries. You will build a runnable stand-in of ObjectModel on SQLite to see the mechanism, then use the escaping tools in a second experiment. Tables are created in the module installer, covered in Tables, Installers and Upgrades.

The PrestaShop classes below need a running shop and appear as read-only code. The runnable PHP is a stand-in: a small ObjectModel look-alike on SQLite written for this lesson. It copies the visible behavior (definition array, validation, add/update/delete, language rows) but is not the real implementation, and it uses prepared statements where the real class builds escaped SQL strings.

The $definition array

An ObjectModel class declares its table and fields in public static $definition, and a public property for each field:

PHP
<?php
// modules/acmewishlist/classes/AcmeWishlist.php (read-only)
class AcmeWishlist extends ObjectModel
{
    public $id_customer;
    public $name;
    public $is_public;
    public $date_add;
    public $date_upd;
    public $description; // multilingual

    public static $definition = [
        'table' => 'acme_wishlist',
        'primary' => 'id_acme_wishlist',
        'multilang' => true,
        'fields' => [
            'id_customer' => [
                'type' => self::TYPE_INT,
                'validate' => 'isUnsignedId',
                'required' => true,
            ],
            'name' => [
                'type' => self::TYPE_STRING,
                'validate' => 'isGenericName',
                'required' => true,
                'size' => 64,
            ],
            'is_public' => [
                'type' => self::TYPE_BOOL,
                'validate' => 'isBool',
            ],
            'date_add' => ['type' => self::TYPE_DATE, 'validate' => 'isDate'],
            'date_upd' => ['type' => self::TYPE_DATE, 'validate' => 'isDate'],
            // Language fields: stored in acme_wishlist_lang
            'description' => [
                'type' => self::TYPE_STRING,
                'lang' => true,
                'validate' => 'isCleanHtml',
                'size' => 255,
            ],
        ],
    ];
}

The keys of the definition:

  • table is the table name without the shop's prefix (ps_ by default). PrestaShop adds _DB_PREFIX_ itself.
  • primary is the primary key column. The object's $id property holds its value.
  • fields maps each column (and the property of the same name) to its rules.
  • multilang and multishop switch on language and shop tables; see below.

Each field can have:

Key Meaning
type TYPE_INT, TYPE_BOOL, TYPE_STRING, TYPE_FLOAT, TYPE_DATE, TYPE_HTML (HTML allowed), TYPE_NOTHING, TYPE_SQL
validate the name of a static method of the Validate class, such as isGenericName or isUnsignedId
required the value must not be empty
size maximum length
lang the field is translated and lives in the _lang table
shop the field is stored per shop (with multishop)

The type decides how the value is cast and escaped when it is written. The validate rule decides whether the value is acceptable. They are different jobs: TYPE_STRING makes sure a string is treated as text; isGenericName rejects text containing characters such as < and >.

Creating, loading, updating, deleting

PHP
<?php
// read-only
$list = new AcmeWishlist();
$list->id_customer = (int) $this->context->customer->id;
$list->name = 'Birthday';
$list->is_public = false;
$list->description = [1 => 'Things I like', 2 => 'Choses que j\'aime']; // id_lang => text
$list->add();            // INSERT; sets $list->id; fills date_add and date_upd

$same = new AcmeWishlist($list->id);        // load: language fields are arrays
$onlyEnglish = new AcmeWishlist($list->id, 1); // load one language: strings

$same->name = 'Birthday 2026';
$same->update();         // UPDATE; refreshes date_upd

$same->delete();         // DELETE the row and its language rows

Useful details:

  • add($auto_date = true, $null_values = false) sets date_add and date_upd when those fields exist. Pass false to keep your own dates.
  • update($null_values = false) ignores fields that are null unless you pass true; this is a common surprise when you want to clear a column.
  • save() calls add() when the object has no id and update() otherwise.
  • new AcmeWishlist($id) with an id that does not exist leaves $object->id empty. Always check Validate::isLoadedObject($object) before using a loaded object.
  • add() and update() call validateFields() first. By default an invalid object makes it throw a PrestaShopException; you can call $obj->validateFields(false, true) yourself to get an error message string instead of an exception, which is handy for showing form errors.
  • Because ObjectModel triggers hooks for each operation (such as actionObjectAcmeWishlistAddAfter for your class), other modules can react to your entity's changes without extra code.

Multilanguage and multishop

A shop can have several languages. ObjectModel stores language-dependent fields in a second table named like the first with _lang added, for example ps_acme_wishlist_lang, with the primary key, id_lang and the translated columns:

SQL
CREATE TABLE `PREFIX_acme_wishlist` (
  `id_acme_wishlist` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_customer` INT UNSIGNED NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `is_public` TINYINT(1) NOT NULL DEFAULT 0,
  `date_add` DATETIME NOT NULL,
  `date_upd` DATETIME NOT NULL,
  PRIMARY KEY (`id_acme_wishlist`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `PREFIX_acme_wishlist_lang` (
  `id_acme_wishlist` INT UNSIGNED NOT NULL,
  `id_lang` INT UNSIGNED NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id_acme_wishlist`, `id_lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

(PREFIX_ is a placeholder that installers replace with the real prefix.)

When you load an object without a language id, every language field is an array [id_lang => value]. When you pass a language id, the field is a plain string for that language. For forms you normally want the arrays.

For multistore shops, set 'multishop' => true in the definition and add a <table>_shop table holding the primary key, id_shop and the per-shop fields; with 'multilang_shop' => true the language table also carries an id_shop column. Multistore is a large topic. If you do not need it, leave it out, and look at a core class like Product for a complete example before you add it.

Stand-in: a mini ObjectModel on SQLite

The program below defines a Validate class, a small ObjectModel base class and an AcmeWishlist model with the same $definition shape as above. Read the output carefully: you will see what add(), loading with and without a language, update(), validation errors and delete() do to the two tables.

PHP Stand-in: ObjectModel clone with SQLite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Notice that invalid objects never reach the database (the row count stays at 1 after four failed attempts), and that deleting the object removes the language rows too. That is the value of going through ObjectModel instead of writing SQL by hand.

The Db class

ObjectModel covers simple entities. For reports, joins and bulk updates you query the database directly with the Db class. It is a singleton:

PHP
<?php
// read-only
$db = Db::getInstance();

// A list of rows (array of arrays)
$rows = $db->executeS('SELECT id_acme_wishlist, name FROM `' . _DB_PREFIX_ . 'acme_wishlist` WHERE id_customer = ' . (int) $idCustomer);

// One row, one value
$row = $db->getRow('SELECT * FROM `' . _DB_PREFIX_ . 'acme_wishlist` WHERE id_acme_wishlist = ' . (int) $id);
$count = (int) $db->getValue('SELECT COUNT(*) FROM `' . _DB_PREFIX_ . 'acme_wishlist`');

// Writes
$db->execute('DELETE FROM `' . _DB_PREFIX_ . 'acme_wishlist` WHERE date_add < "' . pSQL($cutoff) . '"');
$db->insert('acme_wishlist', ['id_customer' => (int) $id, 'name' => pSQL($name), 'date_add' => date('Y-m-d H:i:s'), 'date_upd' => date('Y-m-d H:i:s')]);
$db->update('acme_wishlist', ['name' => pSQL($name)], 'id_acme_wishlist = ' . (int) $id);
$db->delete('acme_wishlist', 'id_acme_wishlist = ' . (int) $id);

Several rules apply:

  • Always add _DB_PREFIX_ to table names in raw SQL. The helper methods insert(), update() and delete() add it for you, so they take the name without the prefix.
  • The helpers do not escape your values. Escaping is your job, every time.
  • DbQuery is a small builder that reads better than concatenated strings: (new DbQuery())->select('*')->from('acme_wishlist', 'w')->where('w.id_customer = ' . (int) $id)->orderBy('w.date_add DESC')->limit(10). It adds the prefix to the from() table, but you must still escape values in where().
  • In a loop, never run one query per item when one query for all items works (see Performance and Caching).

pSQL, bqSQL and casting

pSQL($string, $htmlOk = false) escapes a string so it can be placed inside quotes in a query. It also strips HTML tags unless you pass true. bqSQL($identifier) escapes a name placed in backticks. Integers and floats are not escaped but cast: (int) $id, (float) $price.

The most important limit of pSQL(): it only protects values that are inside quotes. A number placed unquoted in the SQL can be attacked even after pSQL(). The second stand-in demonstrates this. It runs in SQLite, so its pSQL() doubles single quotes instead of using MySQL's backslash escaping, but the lessons are the same.

PHP Stand-in: pSQL, casting and DbQuery

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Read the four results. Raw concatenation returns every list; pSQL() inside quotes returns none; pSQL() on an unquoted number is as vulnerable as no protection at all (because 5 OR 1=1 contains no quote to escape); and the (int) cast turns the attack into the harmless number 5. In DbQuery, pSQL() also removed the tags from <b>Garden</b>, so the comparison used Garden. The last example shows the alternative many developers prefer when they can: a prepared statement with placeholders. The legacy Db class has no placeholder API, so with it the rule is simple: quote and pSQL() strings, cast numbers, and never concatenate raw input. Security in Modules returns to this.

flowchart LR
    A["new AcmeWishlist"] --> B["set properties"]
    B --> C["add / update"]
    C --> D["validateFields<br/>required, size, Validate::*"]
    D -->|"invalid"| E["Exception, nothing written"]
    D -->|"valid"| F["INSERT / UPDATE<br/>main table"]
    F --> G["INSERT language rows<br/>table_lang"]
    G --> H["hooks actionObject...After"]

ObjectModel validates first, then writes the main row and the language rows, then lets other modules react through hooks.

Good habits

  • Name tables and fields clearly, with your module name as a prefix (acme_wishlist) so you never collide with other modules or the core.
  • Cast ids to (int) as soon as they enter your code.
  • Create the tables in install() and drop them in uninstall(), and handle upgrades with a version-based script.
  • Prefer ObjectModel for entities and Db with DbQuery for reports and joins.
  • Do not put entity rules (validation, cleanup of related rows) in controllers; keep them in the model so every entry point benefits.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In $definition, what does 'table' => 'acme_wishlist' mean when the shop prefix is ps_?

    The definition holds the table name without the prefix. ObjectModel and the Db helper methods add DB_PREFIX themselves.
  2. 2. How do you load an object so that its multilingual fields are plain strings for one language?

    Without a language id the language fields are arrays keyed by id_lang. With an id_lang they hold the text of that language.
  3. 3. Which statement about pSQL() is true?

    pSQL() escapes quotes, so it helps only inside quoted literals. A number in unquoted position needs a cast such as (int) or (float).
  4. 4. An object with an empty required field is passed to add(). What happens?

    add() validates first. By default an invalid object makes validateFields() throw an exception, so the INSERT never runs.
  5. 5. You call Db::getInstance()->insert('acme_wishlist', [...]). Which is correct?

    The helper adds the prefix, but it does not escape values: use pSQL() for strings and casts for numbers.

Summary

  • An ObjectModel maps a class to a table through public static $definition (table, primary, fields, multilang, multishop) and a public property per field.
  • Field rules: type (how it is cast), validate (a Validate:: method), required, size, lang.
  • add(), update(), delete() and save() validate first, fill dates, and keep language rows in step; loaded objects must be checked with Validate::isLoadedObject().
  • Language fields live in <table>_lang: arrays without a language id, strings with one.
  • Use Db::getInstance() (executeS, getRow, getValue, insert, update, delete) and DbQuery for custom SQL; always add the prefix in raw SQL and escape every value with pSQL() (inside quotes) or a cast (numbers).

Next, create the tables for your entities and keep them up to date: Tables, Installers and Upgrades.