CodeIgniter 4 Model: find(), allowedFields, Timestamps and Foreign Keys

In this chapter, we are going to create one of the fundamental components of the MVC pattern: the model. This component is what allows us to work with the data layer, connecting directly with the migrations in CodeIgniter 4 that we created earlier. A model in CodeIgniter 4 is nothing more than a PHP class that extends from CodeIgniter\Model, just as it happens in frameworks like Laravel. Unlike version 3, in CI4 it is necessary to define several properties for the model to be functional and correctly linked to its table.

The primary function of a model is to connect a PHP class with a specific table in the database, centralizing all data access and manipulation logic there.

We have to indicate the insertable fields, that is, those columns that we are going to manage from the application. To illustrate this, we will start from the following database table:

Since we want to manage all columns of that table, we need to reflect them in our model. Furthermore, we must indicate which table our model matches using the $table property:

<?php

namespace App\Models;

use CodeIgniter\Model;

class MovieModel extends Model
{
    /**
     * @var string Database table name.
     */
    protected $table = 'movies';

    /**
     * @var string Table primary key.
     */
    protected $primaryKey = 'id';

    /**
     * @var string[] Allowed fields for insert/update (allowedFields).
     */
    protected $allowedFields = [
        'title',
        'description',
        'category_id',
    ];
}

Main Properties of the Model in CodeIgniter 4

The model layer in the MVC (Model-View-Controller) pattern is responsible for business logic and communication with the database: creating, reading, updating, and deleting records (CRUD). Usually, each model governs a single table. Below, let's look at the most common properties you can define in a model in CodeIgniter 4 and what each one is for.

The following are the most important properties. You can view the full list in the official CI4 models documentation.

$table

Specifies the name of the database table that this model works with. If not defined, CodeIgniter will try to infer it from the class name.

$primaryKey

The name of the column that uniquely identifies records in the table. It does not necessarily have to match the primary key defined at the database level; it is mainly used with the find() method to know which column to compare the provided value against. For example, $model->find(5) will search for the record where $primaryKey equals 5.

$useAutoIncrement

Specifies whether the table uses auto-increment for the $primaryKey. If set to false, you are responsible for providing the primary key value manually on every insertion.

$returnType

Defines the data type returned by SELECT queries. Valid values are 'array' (default value), 'object', or the fully qualified name of a class to use with the getCustomResultObject() method of the Result object. You can override this behavior at runtime with the asArray() or asObject() methods chained in your query.

$useSoftDeletes

If set to true, any call to the delete() method will not physically remove the record; instead, it will set the deleted_at column with the current date. This is useful for preserving data that might be referenced elsewhere or for implementing a "recycle bin". When active, find*() methods only return records whose deleted_at is NULL, unless you call withDeleted() beforehand.

$allowedFields

This array contains the names of the columns that can be modified using the save(), insert(), or update() methods. Any field not on this list will be silently discarded. This acts as a security layer against mass assignment attacks, ensuring that only the columns you explicitly permit can be written from the application.

$useTimestamps

This boolean value determines whether the current date and time is automatically added on all insertions and updates. If set to true, the model will automatically manage the columns defined in $createdField and $updatedField, setting the time in the format specified by $dateFormat. This requires the table to have those columns with the appropriate data type (DATETIME or INT, depending on your configuration).

$createdField

Specifies the name of the database column that will store the record creation timestamp. By default, its value is 'created_at'. It is written only once, when the record is inserted for the first time.

$updatedField

Specifies the name of the column that will store the timestamp of the record's last update. By default, its value is 'updated_at'. It is automatically updated on every call to save() or update().

$validationRules

With this property, you can define the validation rules directly in the model that will be applied during creation and update operations. It is an alternative to defining rules in the controller or in separate configuration files. For example: $validationRules = ['title' => 'required|min_length[3]|max_length[255]'].

You can view the full list of available properties in the official CI4 documentation.

Common Operations with the Model in CodeIgniter 4

To create a model in CodeIgniter 4, you can use the Spark CLI with the following command:

$ php spark make:model ModelName

This command will create a new class in app/Models/ with the base structure already defined. Once created, the model is available to be injected into any controller or service within the application.

Apart from the configuration properties, the model inherits a set of ready-to-use methods from CodeIgniter\Model. Below are the most relevant ones:

• find($id) — Returns the record whose $primaryKey matches the provided ID.
• findAll() — Returns all records from the table.
• first() — Returns the first record from the query result.
• insert($data) — Inserts a new record. Returns the generated ID or false on failure.
• update($id,$data) — Updates the specified record. Returns true or false.
• save($data) — Inserts or updates depending on whether the array contains the $primaryKey.
• delete($id) — Deletes the record (or applies soft delete if $useSoftDeletes is active).

getInsertID()

After executing an insert(), you can obtain the auto-generated ID of the last inserted record by calling $model->getInsertID(). This is especially useful when you need that ID for subsequent operations, such as creating related records.

$movieModel = new MovieModel();$movieModel->insert(['title' => 'Inception', 'category_id' => 3]);
$nuevoId =$movieModel->getInsertID(); // Gets the ID of the newly inserted record

Joins in CodeIgniter 4 Query Builder

Joins are used to combine results from different tables using the relational field. CodeIgniter 4 supports the same types of joins as SQL: INNER, LEFT, RIGHT, among others. The join() function receives the table to relate as its first parameter and the equality condition as its second:

$peliculaModel->asObject()
    ->select('peliculas.*, categorias.titulo as categoria')
    ->join('categorias', 'categorias.id = peliculas.categoria_id')
    ->find()

Pagination

CodeIgniter 4 includes native pagination that goes beyond a simple LIMIT/OFFSET in SQL; it automatically generates pagination links. Essentially, you replace the final find() method with paginate(), specifying the number of records per page:

$peliculaModel->asObject()
    ->select('peliculas.*, categorias.titulo as categoria')
    ->join('categorias', 'categorias.id = peliculas.categoria_id')
    ->paginate(10)

where Conditions

The where() method is fundamental in most queries. It receives two parameters: the column to compare and its value. If you chain multiple where() calls, they are joined with AND by default. You can also use comparison operators directly in the field name: ->where('precio >', 100) or ->where('estado !=', 'inactivo'):

$peliculaModel->asObject()
    ->select('peliculas.*, categorias.titulo as categoria')
    ->join('categorias', 'categorias.id = peliculas.categoria_id')
    ->where('categorias.id', $categoria_id)
    ->find()

orWhere Conditions

Similar to the previous case, but joining conditions with OR instead of AND. It is very useful, for example, to search for a user by email or by username interchangeably:

$usuarioModel->orWhere('email', $email)->orWhere('usuario',$usuario)->first();

like Conditions

The like() method is used when you need partial searches, equivalent to SQL's LIKE '%value%'. You also have orLike() to combine partial conditions with OR, and groupStart() / groupEnd() to group complex conditions:

$peliculaModel->asObject()
    ->select('peliculas.*, categorias.titulo as categoria')
    ->join('categorias', 'categorias.id = peliculas.categoria_id')
    ->like('peliculas.titulo', $buscar)
    ->find()

Grouping with groupBy

The groupBy() function groups results by the specified field, equivalent to SQL's GROUP BY. Keep in mind that, depending on the MySQL version you use, non-aggregate SELECT columns may also need to be included in the GROUP BY clause:

$peliculaModel->asObject()
    ->select('peliculas.*, categorias.titulo as categoria')
    ->join('categorias', 'categorias.id = peliculas.categoria_id')
    ->join('imagenes', 'peliculas.id = pelicula_id', 'left')
    ->groupBy('peliculas.id')
    ->find()

View Compiled Query

When you are building chained queries, you often want to see the generated SQL before executing it to debug or verify that the query is correct. To do this, you need an instance of CodeIgniter's Query Builder, which allows you to connect to any table without needing a model:

$db = \Config\Database::connect();
$builder =$db->table('peliculas');

Once you have the $builder, instead of using final methods like get() or first(), you use getCompiledSelect(), which returns the generated SQL as a string without executing it:

return $builder->limit(10, 20)->getCompiledSelect();

And you would get something like:

SELECT * FROM `peliculas` LIMIT 20, 10

The most important thing to note is that there is a direct equivalence between SQL operations and CodeIgniter 4 Query Builder functions. You learn these functions through practice: knowing what parameters they accept, which ones are optional, and how to chain them is what will make you productive quickly.

This material is part of my complete course and book on CodeIgniter 4.

Timestamps Configuration: created_at and updated_at in CodeIgniter 4

Having columns that fill automatically with the creation date and last modification date is standard practice in modern frameworks like Laravel, and in CodeIgniter 4 we can configure it just as easily. You only need to define three properties in your model:

1. $useTimestamps — Boolean (true/false) that enables automatic timestamp handling.
2. $createdField — Name of the column that will store the creation date (default is 'created_at').
3. $updatedField — Name of the column that will store the last update date (default is 'updated_at').

An important detail: if you have timestamps enabled ($useTimestamps = true), you must include the created_at and updated_at fields in your $allowedFields as well; otherwise, the model will filter them out and timestamps will not be saved. Let's see a complete example:

<?php
namespace App\Models;
use CodeIgniter\Model;

class ProductsControlModel extends Model
{
    protected $table         = 'products_control';
    protected $primaryKey    = 'id';
    protected $allowedFields = ['product_id', 'type', 'count', 'created_at', 'updated_at'];
    protected $useTimestamps = true;
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
}

How to Add Foreign Keys in CodeIgniter 4 Migrations

Relationships are the core of any relational database. They describe how tables link to each other through a unique identifier: the primary key (PK). For example, if we have a table of movies (movies) and another of categories (categories), each movie belongs to a category. To express that relationship, we store the category's identifier in the movies table.

When that primary key from one table is recorded in another table to create the relationship, it becomes a Foreign Key (FK). FKs at the database level guarantee referential integrity: you won't be able to insert a category_id into movies if that ID does not exist in categories. Let's see how to define them in CodeIgniter 4 from migrations:

// Signature: $this->forge->addForeignKey(field, related_table, related_field, on_delete, on_update);$this->forge->addForeignKey('category_id', 'categories', 'id', 'CASCADE', 'CASCADE');

Parent Table Migration (Table to Relate)

In CodeIgniter 4, just like in Laravel, relationships between tables are defined in migrations. We must always create the parent table (the one with the PK) first before creating the child table (the one that will have the FK). In this example, categories is the parent table:

<?php namespace App\Database\Migrations;
use CodeIgniter\Database\Migration;
class Categories extends Migration
{
    public function up() {
        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 5,
                'unsigned'       => TRUE,
                'auto_increment' => TRUE
            ],
            'title' => [
                'type'       => 'VARCHAR',
                'constraint' => '255',
            ],
        ]);
        $this->forge->addKey('id', TRUE);$this->forge->createTable('categories');
    }
    //--------------------------------------------------------------------
    public function down()
    {
        $this->forge->dropTable('categories');
    }
}

The standard setup: an auto-incrementing id field and a title field. In the down() method, we perform the inverse operation: if in up() we created the table, in down() we drop it. This allows rolling back the migration cleanly using php spark migrate:rollback.

Child Table Migration with the FK

Now we define the table that will hold the foreign key. The category_id field must be of the same type and size as the PK it references (in this case, INT(5) UNSIGNED):

<?php namespace App\Database\Migrations;
use CodeIgniter\Database\Migration;
class Movies extends Migration
{
    public function up()
    {
        $this->forge->addField([             'id' => [                 'type'           => 'INT',                 'constraint'     => 5,                 'unsigned'       => TRUE,                 'auto_increment' => TRUE             ],             'title' => [                 'type'       => 'VARCHAR',                 'constraint' => 255             ],             'category_id' => [                 'type'       => 'INT',                 'constraint' => 5,                 'unsigned'   => TRUE             ],         ]);$this->forge->addKey('id', TRUE);
        $this->forge->addForeignKey('category_id', 'categories', 'id', 'CASCADE', 'CASCADE');$this->forge->createTable('movies');
    }
    //--------------------------------------------------------------------
    public function down()
    {
        $this->forge->dropTable('movies');
    }
}

The key part here is the category_id column and the call to addForeignKey(), which receives the following parameters:

1. The column name in the child table (movies) that will act as the FK: 'category_id'.
2. The parent table name to relate: 'categories'.
3. The column name in the parent table that is the PK: 'id'.
4. Action upon deleting a parent record: 'CASCADE' (deletes children as well), 'SET NULL', 'NO ACTION', or 'RESTRICT'.
5. Action upon updating the parent record's PK: same options as the previous parameter.

Models for Tables with FK Relationship

The models corresponding to the previous migrations are simple: in $allowedFields you include the FK field (category_id) just like any other permitted field:

<?php namespace App\Models;
use CodeIgniter\Model;

class MovieModel extends Model
{
    protected $table         = 'movies';
    protected $primaryKey    = 'id';
    protected $allowedFields = ['title', 'category_id'];
}

class CategoryModel extends Model
{
    protected $table         = 'categories';
    protected $primaryKey    = 'id';
    protected $allowedFields = ['title'];

    public function get($id = null)
    {
        if ($id === null) {
            return $this->findAll();
        }
        return $this->asArray()
            ->where(['id' => $id])
            ->first();
    }
}

Extra: Creating a Record with a Foreign Relationship

To insert a record containing an FK, you don't need any additional configuration in the model; for practical purposes, the FK is an integer column with an integrity constraint. You simply pass the related ID value in the data array. In this example, the data comes from a form:

$movie = new MovieModel();
if ($this->validate('movies')) {
    $id =$movie->insert([
        'title'       => $this->request->getPost('title'),
        'description' => $this->request->getPost('description'),
        'category_id' => $this->request->getPost('category_id'),
    ]);
    // Get the ID of the newly inserted record
    $insertedId =$movie->getInsertID();
}

Other Important Facts About Models in CodeIgniter 4

• Business logic goes in models: application-specific functions, such as fetching all active records, searching by composite criteria, or applying data transformations before returning results to the controller. Each process usually has its own method within the model.
• The framework provides inherited methods for free: find(), findAll(), first(), insert(), save(), update(), and delete(). You don't have to write them; you simply use them.
• Models go hand in hand with migrations, but they are independent components. Unlike frameworks such as Django or Flask (where migrations are generated from the model), in CodeIgniter 4 both are separate files. It is customary to maintain a one-to-one correspondence: one migration and one model per table.

Queries with JOIN in CodeIgniter 4 Query Builder

In SQL, a JOIN combines rows from two or more related tables based on a common column, usually FK columns. They are used to retrieve data from multiple tables in a single query, presenting the information completely. For example: if you have posts and categories tables, a JOIN allows you to fetch the post alongside its category name in a single query, without making two trips to the database.

There are different types of JOIN in SQL, and CodeIgniter 4 supports the main ones:

• INNER JOIN: Returns only rows that have a match in both tables. If a record in the left table has no correspondence in the right table, it is excluded from the result.
• LEFT JOIN: Returns all rows from the left table, and columns from the right table only when there is a match; where there is no match, they appear as NULL. It is useful when you want to retrieve all records from the main table even if they lack a relation in the other table.
• RIGHT JOIN: The opposite of LEFT JOIN: returns all rows from the right table, with values from the left table as NULL where there is no match.

Using the join() method in CodeIgniter 4 is straightforward. First, you build the base SELECT with the fields you need (using aliases to avoid ambiguity between tables):

$query =$productModel->asObject()->select("pc.*, u.email, puc.description, puc.direction")

And then you chain the necessary join() calls. You can nest as many as your relationships allow:

$query =$productModel->asObject()->select("pc.*, u.email, puc.description, puc.direction")
    ->join('products_control as pc', 'pc.product_id = products.id')
    ->join('users as u', 'pc.user_id = u.id')
    ->join('products_users_control as puc', 'pc.id = puc.product_control_id');

In this example, a product relates to products_control, which in turn relates to users, and finally to products_users_control. Notice the use of aliases (as pc, as u, as puc) to write more concise conditions.

Parameters of the join() Method

The join() function accepts two required parameters and an optional third one:

1. Table to join — The name of the table. Optionally, you can add an alias using AS to simplify references: 'products_control as pc'.
2. Join condition — The equality condition that defines the relationship, usually based on the FK: 'pc.product_id = products.id'.
3. Join type (optional) — Can be 'inner', 'left', 'right', 'outer', 'left outer', or 'right outer'. Defaults to INNER JOIN. Example: ->join('products_control as pc', 'pc.product_id = products.id', 'left').

In this chapter, we are going to create one of the fundamental components of the MVC pattern: the model. This component is what allows us to work with the data layer, connecting directly with the migrations in CodeIgniter 4 that we created earlier. A model in CodeIgniter 4 is nothing more than a PHP class that extends from CodeIgniter\Model, just as it happens in frameworks like Laravel. Unlike version 3, in CI4 it is necessary to define several properties for the model to be functional and correctly linked to its table.

The primary function of a model is to connect a PHP class with a specific table in the database, centralizing all data access and manipulation logic there.

We have to indicate the insertable fields, that is, those columns that we are going to manage from the application. To illustrate this, we will start from the following database table:

Since we want to manage all columns of that table, we need to reflect them in our model. Furthermore, we must indicate which table our model matches using the $table property:

<?php

namespace App\Models;

use CodeIgniter\Model;

class MovieModel extends Model
{
    /**
     * @var string Database table name.
     */
    protected $table = 'movies';

    /**
     * @var string Table primary key.
     */
    protected $primaryKey = 'id';

    /**
     * @var string[] Allowed fields for insert/update (allowedFields).
     */
    protected $allowedFields = [
        'title',
        'description',
        'category_id',
    ];
}

Main Properties of the Model in CodeIgniter 4

The model layer in the MVC (Model-View-Controller) pattern is responsible for business logic and communication with the database: creating, reading, updating, and deleting records (CRUD). Usually, each model governs a single table. Below, let's look at the most common properties you can define in a model in CodeIgniter 4 and what each one is for.

The following are the most important properties. You can view the full list in the official CI4 models documentation.

$table

Specifies the name of the database table that this model works with. If not defined, CodeIgniter will try to infer it from the class name.

$primaryKey

The name of the column that uniquely identifies records in the table. It does not necessarily have to match the primary key defined at the database level; it is mainly used with the find() method to know which column to compare the provided value against. For example, $model->find(5) will search for the record where $primaryKey equals 5.

$useAutoIncrement

Specifies whether the table uses auto-increment for the $primaryKey. If set to false, you are responsible for providing the primary key value manually on every insertion.

$returnType

Defines the data type returned by SELECT queries. Valid values are 'array' (default value), 'object', or the fully qualified name of a class to use with the getCustomResultObject() method of the Result object. You can override this behavior at runtime with the asArray() or asObject() methods chained in your query.

$useSoftDeletes

If set to true, any call to the delete() method will not physically remove the record; instead, it will set the deleted_at column with the current date. This is useful for preserving data that might be referenced elsewhere or for implementing a "recycle bin". When active, find*() methods only return records whose deleted_at is NULL, unless you call withDeleted() beforehand.

$allowedFields

This array contains the names of the columns that can be modified using the save(), insert(), or update() methods. Any field not on this list will be silently discarded. This acts as a security layer against mass assignment attacks, ensuring that only the columns you explicitly permit can be written from the application.

$useTimestamps

This boolean value determines whether the current date and time is automatically added on all insertions and updates. If set to true, the model will automatically manage the columns defined in $createdField and $updatedField, setting the time in the format specified by $dateFormat. This requires the table to have those columns with the appropriate data type (DATETIME or INT, depending on your configuration).

$createdField

Specifies the name of the database column that will store the record creation timestamp. By default, its value is 'created_at'. It is written only once, when the record is inserted for the first time.

$updatedField

Specifies the name of the column that will store the timestamp of the record's last update. By default, its value is 'updated_at'. It is automatically updated on every call to save() or update().

$validationRules

With this property, you can define the validation rules directly in the model that will be applied during creation and update operations. It is an alternative to defining rules in the controller or in separate configuration files. For example: $validationRules = ['title' => 'required|min_length[3]|max_length[255]'].

You can view the full list of available properties in the official CI4 documentation.

Common Operations with the Model in CodeIgniter 4

To create a model in CodeIgniter 4, you can use the Spark CLI with the following command:

$ php spark make:model ModelName

This command will create a new class in app/Models/ with the base structure already defined. Once created, the model is available to be injected into any controller or service within the application.

Apart from the configuration properties, the model inherits a set of ready-to-use methods from CodeIgniter\Model. Below are the most relevant ones:

• find($id) — Returns the record whose $primaryKey matches the provided ID.
• findAll() — Returns all records from the table.
• first() — Returns the first record from the query result.
• insert($data) — Inserts a new record. Returns the generated ID or false on failure.
• update($id,$data) — Updates the specified record. Returns true or false.
• save($data) — Inserts or updates depending on whether the array contains the $primaryKey.
• delete($id) — Deletes the record (or applies soft delete if $useSoftDeletes is active).

getInsertID()

After executing an insert(), you can obtain the auto-generated ID of the last inserted record by calling $model->getInsertID(). This is especially useful when you need that ID for subsequent operations, such as creating related records.

$movieModel = new MovieModel();$movieModel->insert(['title' => 'Inception', 'category_id' => 3]);
$nuevoId =$movieModel->getInsertID(); // Gets the ID of the newly inserted record

Joins in CodeIgniter 4 Query Builder

Joins are used to combine results from different tables using the relational field. CodeIgniter 4 supports the same types of joins as SQL: INNER, LEFT, RIGHT, among others. The join() function receives the table to relate as its first parameter and the equality condition as its second:

$peliculaModel->asObject()
    ->select('peliculas.*, categorias.titulo as categoria')
    ->join('categorias', 'categorias.id = peliculas.categoria_id')
    ->find()

Pagination

CodeIgniter 4 includes native pagination that goes beyond a simple LIMIT/OFFSET in SQL; it automatically generates pagination links. Essentially, you replace the final find() method with paginate(), specifying the number of records per page:

$peliculaModel->asObject()
    ->select('peliculas.*, categorias.titulo as categoria')
    ->join('categorias', 'categorias.id = peliculas.categoria_id')
    ->paginate(10)

where Conditions

The where() method is fundamental in most queries. It receives two parameters: the column to compare and its value. If you chain multiple where() calls, they are joined with AND by default. You can also use comparison operators directly in the field name: ->where('precio >', 100) or ->where('estado !=', 'inactivo'):

$peliculaModel->asObject()
    ->select('peliculas.*, categorias.titulo as categoria')
    ->join('categorias', 'categorias.id = peliculas.categoria_id')
    ->where('categorias.id', $categoria_id)
    ->find()

orWhere Conditions

Similar to the previous case, but joining conditions with OR instead of AND. It is very useful, for example, to search for a user by email or by username interchangeably:

$usuarioModel->orWhere('email', $email)->orWhere('usuario',$usuario)->first();

like Conditions

The like() method is used when you need partial searches, equivalent to SQL's LIKE '%value%'. You also have orLike() to combine partial conditions with OR, and groupStart() / groupEnd() to group complex conditions:

$peliculaModel->asObject()
    ->select('peliculas.*, categorias.titulo as categoria')
    ->join('categorias', 'categorias.id = peliculas.categoria_id')
    ->like('peliculas.titulo', $buscar)
    ->find()

Grouping with groupBy

The groupBy() function groups results by the specified field, equivalent to SQL's GROUP BY. Keep in mind that, depending on the MySQL version you use, non-aggregate SELECT columns may also need to be included in the GROUP BY clause:

$peliculaModel->asObject()
    ->select('peliculas.*, categorias.titulo as categoria')
    ->join('categorias', 'categorias.id = peliculas.categoria_id')
    ->join('imagenes', 'peliculas.id = pelicula_id', 'left')
    ->groupBy('peliculas.id')
    ->find()

View Compiled Query

When you are building chained queries, you often want to see the generated SQL before executing it to debug or verify that the query is correct. To do this, you need an instance of CodeIgniter's Query Builder, which allows you to connect to any table without needing a model:

$db = \Config\Database::connect();
$builder =$db->table('peliculas');

Once you have the $builder, instead of using final methods like get() or first(), you use getCompiledSelect(), which returns the generated SQL as a string without executing it:

return $builder->limit(10, 20)->getCompiledSelect();

And you would get something like:

SELECT * FROM `peliculas` LIMIT 20, 10

The most important thing to note is that there is a direct equivalence between SQL operations and CodeIgniter 4 Query Builder functions. You learn these functions through practice: knowing what parameters they accept, which ones are optional, and how to chain them is what will make you productive quickly.

This material is part of my complete course and book on CodeIgniter 4.

Timestamps Configuration: created_at and updated_at in CodeIgniter 4

Having columns that fill automatically with the creation date and last modification date is standard practice in modern frameworks like Laravel, and in CodeIgniter 4 we can configure it just as easily. You only need to define three properties in your model:

1. $useTimestamps — Boolean (true/false) that enables automatic timestamp handling.
2. $createdField — Name of the column that will store the creation date (default is 'created_at').
3. $updatedField — Name of the column that will store the last update date (default is 'updated_at').

An important detail: if you have timestamps enabled ($useTimestamps = true), you must include the created_at and updated_at fields in your $allowedFields as well; otherwise, the model will filter them out and timestamps will not be saved. Let's see a complete example:

<?php
namespace App\Models;
use CodeIgniter\Model;

class ProductsControlModel extends Model
{
    protected $table         = 'products_control';
    protected $primaryKey    = 'id';
    protected $allowedFields = ['product_id', 'type', 'count', 'created_at', 'updated_at'];
    protected $useTimestamps = true;
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
}

How to Add Foreign Keys in CodeIgniter 4 Migrations

Relationships are the core of any relational database. They describe how tables link to each other through a unique identifier: the primary key (PK). For example, if we have a table of movies (movies) and another of categories (categories), each movie belongs to a category. To express that relationship, we store the category's identifier in the movies table.

When that primary key from one table is recorded in another table to create the relationship, it becomes a Foreign Key (FK). FKs at the database level guarantee referential integrity: you won't be able to insert a category_id into movies if that ID does not exist in categories. Let's see how to define them in CodeIgniter 4 from migrations:

// Signature: $this->forge->addForeignKey(field, related_table, related_field, on_delete, on_update);$this->forge->addForeignKey('category_id', 'categories', 'id', 'CASCADE', 'CASCADE');

Parent Table Migration (Table to Relate)

In CodeIgniter 4, just like in Laravel, relationships between tables are defined in migrations. We must always create the parent table (the one with the PK) first before creating the child table (the one that will have the FK). In this example, categories is the parent table:

<?php namespace App\Database\Migrations;
use CodeIgniter\Database\Migration;
class Categories extends Migration
{
    public function up() {
        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 5,
                'unsigned'       => TRUE,
                'auto_increment' => TRUE
            ],
            'title' => [
                'type'       => 'VARCHAR',
                'constraint' => '255',
            ],
        ]);
        $this->forge->addKey('id', TRUE);$this->forge->createTable('categories');
    }
    //--------------------------------------------------------------------
    public function down()
    {
        $this->forge->dropTable('categories');
    }
}

The standard setup: an auto-incrementing id field and a title field. In the down() method, we perform the inverse operation: if in up() we created the table, in down() we drop it. This allows rolling back the migration cleanly using php spark migrate:rollback.

Child Table Migration with the FK

Now we define the table that will hold the foreign key. The category_id field must be of the same type and size as the PK it references (in this case, INT(5) UNSIGNED):

<?php namespace App\Database\Migrations;
use CodeIgniter\Database\Migration;
class Movies extends Migration
{
    public function up()
    {
        $this->forge->addField([             'id' => [                 'type'           => 'INT',                 'constraint'     => 5,                 'unsigned'       => TRUE,                 'auto_increment' => TRUE             ],             'title' => [                 'type'       => 'VARCHAR',                 'constraint' => 255             ],             'category_id' => [                 'type'       => 'INT',                 'constraint' => 5,                 'unsigned'   => TRUE             ],         ]);$this->forge->addKey('id', TRUE);
        $this->forge->addForeignKey('category_id', 'categories', 'id', 'CASCADE', 'CASCADE');$this->forge->createTable('movies');
    }
    //--------------------------------------------------------------------
    public function down()
    {
        $this->forge->dropTable('movies');
    }
}

The key part here is the category_id column and the call to addForeignKey(), which receives the following parameters:

1. The column name in the child table (movies) that will act as the FK: 'category_id'.
2. The parent table name to relate: 'categories'.
3. The column name in the parent table that is the PK: 'id'.
4. Action upon deleting a parent record: 'CASCADE' (deletes children as well), 'SET NULL', 'NO ACTION', or 'RESTRICT'.
5. Action upon updating the parent record's PK: same options as the previous parameter.

Models for Tables with FK Relationship

The models corresponding to the previous migrations are simple: in $allowedFields you include the FK field (category_id) just like any other permitted field:

<?php namespace App\Models;
use CodeIgniter\Model;

class MovieModel extends Model
{
    protected $table         = 'movies';
    protected $primaryKey    = 'id';
    protected $allowedFields = ['title', 'category_id'];
}

class CategoryModel extends Model
{
    protected $table         = 'categories';
    protected $primaryKey    = 'id';
    protected $allowedFields = ['title'];

    public function get($id = null)
    {
        if ($id === null) {
            return $this->findAll();
        }
        return $this->asArray()
            ->where(['id' => $id])
            ->first();
    }
}

Extra: Creating a Record with a Foreign Relationship

To insert a record containing an FK, you don't need any additional configuration in the model; for practical purposes, the FK is an integer column with an integrity constraint. You simply pass the related ID value in the data array. In this example, the data comes from a form:

$movie = new MovieModel();
if ($this->validate('movies')) {
    $id =$movie->insert([
        'title'       => $this->request->getPost('title'),
        'description' => $this->request->getPost('description'),
        'category_id' => $this->request->getPost('category_id'),
    ]);
    // Get the ID of the newly inserted record
    $insertedId =$movie->getInsertID();
}

Other Important Facts About Models in CodeIgniter 4

• Business logic goes in models: application-specific functions, such as fetching all active records, searching by composite criteria, or applying data transformations before returning results to the controller. Each process usually has its own method within the model.
• The framework provides inherited methods for free: find(), findAll(), first(), insert(), save(), update(), and delete(). You don't have to write them; you simply use them.
• Models go hand in hand with migrations, but they are independent components. Unlike frameworks such as Django or Flask (where migrations are generated from the model), in CodeIgniter 4 both are separate files. It is customary to maintain a one-to-one correspondence: one migration and one model per table.

Queries with JOIN in CodeIgniter 4 Query Builder

In SQL, a JOIN combines rows from two or more related tables based on a common column, usually FK columns. They are used to retrieve data from multiple tables in a single query, presenting the information completely. For example: if you have posts and categories tables, a JOIN allows you to fetch the post alongside its category name in a single query, without making two trips to the database.

There are different types of JOIN in SQL, and CodeIgniter 4 supports the main ones:

• INNER JOIN: Returns only rows that have a match in both tables. If a record in the left table has no correspondence in the right table, it is excluded from the result.
• LEFT JOIN: Returns all rows from the left table, and columns from the right table only when there is a match; where there is no match, they appear as NULL. It is useful when you want to retrieve all records from the main table even if they lack a relation in the other table.
• RIGHT JOIN: The opposite of LEFT JOIN: returns all rows from the right table, with values from the left table as NULL where there is no match.

Using the join() method in CodeIgniter 4 is straightforward. First, you build the base SELECT with the fields you need (using aliases to avoid ambiguity between tables):

$query =$productModel->asObject()->select("pc.*, u.email, puc.description, puc.direction")

And then you chain the necessary join() calls. You can nest as many as your relationships allow:

$query =$productModel->asObject()->select("pc.*, u.email, puc.description, puc.direction")
    ->join('products_control as pc', 'pc.product_id = products.id')
    ->join('users as u', 'pc.user_id = u.id')
    ->join('products_users_control as puc', 'pc.id = puc.product_control_id');

In this example, a product relates to products_control, which in turn relates to users, and finally to products_users_control. Notice the use of aliases (as pc, as u, as puc) to write more concise conditions.

Parameters of the join() Method

The join() function accepts two required parameters and an optional third one:

1. Table to join — The name of the table. Optionally, you can add an alias using AS to simplify references: 'products_control as pc'.
2. Join condition — The equality condition that defines the relationship, usually based on the FK: 'pc.product_id = products.id'.
3. Join type (optional) — Can be 'inner', 'left', 'right', 'outer', 'left outer', or 'right outer'. Defaults to INNER JOIN. Example: ->join('products_control as pc', 'pc.product_id = products.id', 'left').