Models, Migrations và Query Builder

Database Configuration

# .env
database.default.hostname = localhost
database.default.database = myapp
database.default.username = root
database.default.password = 
database.default.DBDriver = MySQLi
database.default.port = 3306

Migrations

Tạo migration

php spark make:migration CreateUsersTable

Migration file

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateUsersTable extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'name' => [
                'type' => 'VARCHAR',
                'constraint' => 100,
            ],
            'email' => [
                'type' => 'VARCHAR',
                'constraint' => 180,
            ],
            'password' => [
                'type' => 'VARCHAR',
                'constraint' => 255,
            ],
            'created_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'updated_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
        ]);

        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('email');
        $this->forge->createTable('users');
    }

    public function down()
    {
        $this->forge->dropTable('users');
    }
}

Chạy migrations

php spark migrate
php spark migrate:rollback
php spark migrate:status

Models

Model cơ bản

<?php

namespace App\Models;

use CodeIgniter\Model;

class UserModel extends Model
{
    protected $table         = 'users';
    protected $primaryKey    = 'id';
    protected $returnType    = 'array';
    protected $useTimestamps = true;
    protected $allowedFields = ['name', 'email', 'password'];

    protected $validationRules = [
        'name' => 'required|min_length[2]|max_length[100]',
        'email' => 'required|valid_email|is_unique[users.email,id,{id}]',
        'password' => 'required|min_length[8]',
    ];

    protected $validationMessages = [
        'email' => [
            'is_unique' => 'Email already registered',
        ],
    ];

    public function findByEmail(string $email): ?array
    {
        return $this->where('email', $email)->first();
    }

    public function search(string $term): array
    {
        return $this->like('name', $term)
            ->orLike('email', $term)
            ->findAll();
    }
}

Entity Model

<?php

namespace App\Models;

use CodeIgniter\Entity\Entity;

class User extends Entity
{
    protected $casts = [
        'id' => 'integer',
        'created_at' => 'datetime',
    ];

    protected $hidden = ['password'];

    protected $datamap = [
        'fullName' => 'name',
    ];

    public function setPassword(string $password): self
    {
        $this->attributes['password'] = password_hash($password, PASSWORD_DEFAULT);
        return $this;
    }
}

Query Builder

CRUD với Model

// Insert
$model->insert(['name' => 'John', 'email' => 'john@example.com']);
$id = $model->getInsertID();

// Insert batch
$model->insertBatch([
    ['name' => 'Alice', 'email' => 'alice@example.com'],
    ['name' => 'Bob', 'email' => 'bob@example.com'],
]);

// Find
$user = $model->find(1);
$users = $model->findAll();
$users = $model->where('active', 1)->orderBy('name')->findAll(10, 0);
$user = $model->where('email', 'john@example.com')->first();

// Update
$model->update(1, ['name' => 'Jane']);
$model->where('active', 0)->set('status', 'inactive')->update();

// Delete
$model->delete(1);
$model->where('active', 0)->delete();

// Soft delete
class PostModel extends Model
{
    protected $useSoftDeletes = true;
    protected $deletedField    = 'deleted_at';
}

Query Builder API

$db = \Config\Database::connect();

// Basic query
$users = $db->table('users')
    ->where('active', 1)
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->get()
    ->getResultArray();

// Joins
$posts = $db->table('posts')
    ->select('posts.*, users.name as author_name')
    ->join('users', 'users.id = posts.user_id')
    ->where('posts.published', 1)
    ->get()
    ->getResult();

// Aggregations
$count = $db->table('users')->countAll();
$sum = $db->table('orders')->selectSum('total')->get()->getRow();

// Raw queries
$db->query('SELECT * FROM users WHERE id = ?', [1]);
$db->query('SELECT * FROM users WHERE id = :id:', ['id' => 1]);

Seeds

<?php

namespace App\Database\Seeds;

use CodeIgniter\Database\Seeder;

class UserSeeder extends Seeder
{
    public function run()
    {
        $data = [
            [
                'name' => 'Admin',
                'email' => 'admin@example.com',
                'password' => password_hash('password', PASSWORD_DEFAULT),
            ],
        ];

        $this->db->table('users')->insertBatch($data);
    }
}

php spark db:seed UserSeeder

Pagination

<?php

namespace App\Controllers;

class Blog extends BaseController
{
    public function index(): string
    {
        $model = new \App\Models\PostModel();
        $data = [
            'posts' => $model->paginate(10),
            'pager' => $model->pager,
        ];
        return view('blog/index', $data);
    }
}

// View
<?= $pager->links() ?>

Bài tập thực hành

Hãy tạo model Product với đầy đủ CRUD!