Relationships are the heart of relational databases, relationships are a set of rules that describe how the tables in the database are related to each other; and hence the relation name. In a relational database, data is organized into tables, with each table representing an entity or object; but many times they are related to each other, if we have a car relationship, the cars have brands, therefore, we can have a table for the cars and another for the brands and we relate them to each other through the unique identifier that is assigned to each table , that is, the PK; this primary key when it is established in the table to be related (in this example, we must register the PK of the models table in the cars table) is known as a FOREIGN KEY type restriction or foreign key in Spanish. Let's see how to handle it in Codeigniter 4.
Migration of the table to relate
In CodeIgniter 4, as it happens with Laravel, in order to structure this type of relationship, we have to define it from the migrations; In this example, I am going to take a couple of tables from my entire course, those of movies and categories in which a movie or film belongs to a category; first the migration of the table to relate, that is, the one of movies:
<?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');
}
}Typically, an id and name field for the categories and in the down function we do the inverse operations to that of one, that if in the up we create a table, we destroy it with the down; remember that the issue of migrations in CodeIgniter has already been discussed.
Migration of the FK or foreign key
Finally the table that we are going to relate by means of the foreign key:
<?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');
}
}A migration with an ID field to keep it simple and the rest of the fields that in an exemplary way we only have the one of the title; the important thing here is the column called category_id that we use with it later to define the foreign key through the addForeignKey function, which receives:
- The name of the column in movies, i.e. the table that will hold the FK relationship
- The name of the table to relate, categories in our case
- The column of the table to relate, that is, the PK of categories that is called id
Models
Finally, the models of the previous migrations:
<?php namespace App\Models;
use CodeIgniter\Model;
class MovieModel extends Model
{
protected $table = 'movies';
protected $primaryKey = 'id';
protected $allowedFields = ['title','category_id'];
}
<?php namespace App\Models;
use CodeIgniter\Model;
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: Create a record with foreign relationship
To create a record you don't have to define some additional rules and structure; the FK relation to the final is an integer column with an extra constraint that is the FK; so, we directly pass the value of the key to relate; In this example, we are assuming that this data is received 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'),
]);
Develop with Laravel, Django, Flask, CodeIgniter, HTML5, CSS3, MySQL, JavaScript, Vue, Android, iOS, Flutter