As in any MVC framework, the models are another of the MVC layers and it is the layer that allows connecting to the database. A model is used to interact with the database and perform management operations on it, that is, create, update, delete and drill down records, each model is tied to a table in the database, the models are the only way to access the database.

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

$ php spark make:model NameModel

This command will create a new class for the model in app/Models. You can also indicate properties to customize the connection such as the name of the PK, table, custom functions and much more.

Once a model has been created, it can be used in a controller or anywhere else in the application to interact with the database.

Key functions in the models

Apart from the operations that we have already performed, there are many combinations that you can perform according to your needs; Let's look at some of what we can do in CodeIgniter 4.

Joins:

The joins are used to combine results between different tables looking for the relational field; in CodeIgniter we have different types of joins (left, right, inner…) just like we have in SQL; as the first parameter of the join() function we pass the table to relate, and as the next parameter, equality.

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

Paginate:

This function can also be simulated in SQL, although in CodeIgniter we go a step further; later we will see how to use this function in detail; but in essence it is to use the paginate() function indicating the pagination level as a parameter.

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

where:

The wheres are essential in most of the queries we make and therefore you have to keep them in mind; receives two parameters corresponding to the column to compare and its value.

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

Nested with orWhere conditions:

Similar to the previous case, but applying the or conditions instead of the where conditions:

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

Conditions Like:

Likes are widely used when we are doing partial searches; its use is similar to the where:

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

Grouped:

In this example we see how to use the groupBy function to group by similar fields; (depending on the version of MySQL you are using, the fields to be grouped may have to match the columns specified in the select) whose function in CodeIgniter allows you to return exactly the columns you specify, indicating aliases in the process.

$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()

See compiled query:

Finally, when you are building your queries, you often want to see the generated SQL to know exactly how you are building the query, since, with the structure based on functions to create a query, it is often not entirely clear.

To see the query, we need to get an instance of the 'CodeIgniter Query Builder' class with which we have a generic instance to connect to any table without using a model:

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

With the query, instead of using final functions like get() or first() we use the getCompiledSelect() function instead:

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

And we get:

SELECT * FROM `peliculas` LIMIT 20, 10

The most important thing is to note that there are equivalences between the query types that we can use in SQL, with a set of functions that we have in CodeIgniter; these functions are learned with practice, knowing which parameters you must pass, which ones are optional and how you can customize them.

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