Model Class - Database Operations

\Kotchasan\Model is the base class for all models. It provides the createQuery() method which returns a QueryBuilder for creating complex queries.

Creating a Model

namespace Index\Profile;

class Model extends \Kotchasan\Model
{
    // Model methods
}

static::createQuery() - QueryBuilder

Used to create SELECT, INSERT, UPDATE, DELETE queries via a fluent interface.

SELECT - Retrieving Data

From [profile.php] - Retrieving 1 record:

public static function get($id)
{
    return static::createQuery()
        ->select()
        ->from('user')
        ->where([['id', $id]])
        ->first();
}

From [profile.php] - Selecting columns:

public static function view($id)
{
    return static::createQuery()
        ->select('U.*')
        ->from('user U')
        ->where([['U.id', $id]])
        ->first();
}

From [users.php] - Query for DataTable:

public static function toDataTable($params)
{
    // Filters (AND conditions)
    $where = [];
    if (isset($params['status']) && $params['status'] !== '') {
        $where[] = ['U.status', (int) $params['status']];
    }

    // Create query
    $query = static::createQuery()
        ->select(
            'U.id',
            'U.username',
            'U.name',
            'U.phone',
            'U.status',
            'U.active',
            'U.social',
            'U.create_date'
        )
        ->from('user U')
        ->where($where);

    // Search (OR condition)
    if (!empty($params['search'])) {
        $search = '%'.$params['search'].'%';
        $where = [
            ['U.name', 'LIKE', $search],
            ['U.username', 'LIKE', $search],
            ['U.phone', 'LIKE', $search]
        ];
        $query->where($where, 'OR');
    }

    return $query;
}

From [users.php] - For dropdown/select:

public static function toOptions($where = [])
{
    return static::createQuery()
        ->select('id value', 'name text')
        ->from('user')
        ->where($where)
        ->orderBy('name')
        ->execute()
        ->fetchAll();
}

From [category.php] - Fetch and process:

public static function get($type)
{
    $query = static::createQuery()
        ->select('category_id', 'topic', 'language')
        ->from('category')
        ->where([['type', $type]])
        ->execute();

    $data = [];
    foreach ($query->fetchAll() as $item) {
        if (!isset($data[$item->category_id])) {
            $data[$item->category_id]['id'] = $item->category_id;
        }
        $data[$item->category_id][$item->language] = $item->topic;
    }

    return array_values($data);
}

SELECT - With Activation Check

From [register.php]:

$user = static::createQuery()
    ->select('id', 'activatecode', 'active')
    ->from('user')
    ->where([['id', $userId]])
    ->first();

From [register.php]:

$user = static::createQuery()
    ->select('id', 'username', 'name', 'active', 'activatecode')
    ->from('user')
    ->where([['activatecode', $code]])
    ->first();

QueryBuilder Methods

Where Conditions

// 1. Simple: [['field', 'value']]
->where([['id', 1]])

// 2. Multiple conditions (AND)
->where([
    ['status', 1],
    ['active', 1]
])

// 3. Using operator
->where([
    ['U.name', 'LIKE', '%search%'],
    ['price', '>', 100]
])

// 4. OR conditions
->where($conditions, 'OR')

Table Alias

static::createQuery()
    ->select('U.id', 'U.name', 'P.title')
    ->from('user U')
    ->where([['U.id', $id]])
    ->first();

Model vs DB Utility

Example Comparison:

// Model::createQuery() - For complex queries
$users = static::createQuery()
    ->select('U.*', 'R.name role')
    ->from('user U')
    ->leftJoin('roles R', [['R.id', 'U.role_id']])
    ->where([['U.status', 1]])
    ->orderBy('U.name')
    ->execute()
    ->fetchAll();

// DB::create() - For simple CRUD
$user = \Kotchasan\DB::create()->first('user', [['id', $id]]);

Model Method Reference

Static Methods

Instance Methods

Instance Methods Usage

Model::create() - Create Model Instance

$model = \Index\Users\Model::create();
$db = $model->getDB();

Instance select/insert/update/delete

$model = new \Kotchasan\Model();

// Instance-level query builders
$selectBuilder = $model->select('id', 'name')->from('user');
$insertBuilder = $model->insert('user')->values([...]);
$updateBuilder = $model->update('user')->set([...])->where([...]);
$deleteBuilder = $model->delete('user')->where([...]);

raw() - Execute Raw SQL

$model = new \Kotchasan\Model();
$result = $model->raw(
    'SELECT * FROM {prefix}user WHERE status = :status',
    [':status' => 1]
);

Transaction in Model

class OrderModel extends \Kotchasan\Model
{
    public function createOrder($data, $items)
    {
        try {
            $this->beginTransaction();

            $this->insert('orders')
                ->values($data)
                ->execute();
            $orderId = $this->db->lastInsertId();

            foreach ($items as $item) {
                $item['order_id'] = $orderId;
                $this->insert('order_items')
                    ->values($item)
                    ->execute();
            }

            $this->commit();
            return $orderId;

        } catch (\Exception $e) {
            $this->rollback();
            throw $e;
        }
    }
}

getTableName() / getPrefix()

$model = new \Kotchasan\Model();

// Get table name with prefix e.g., "app_user"
$tableName = $model->getTableName('user');

// Get prefix e.g., "app"
$prefix = $model->getPrefix();

Related Classes

• DB - Utility class for simple CRUD
• QueryBuilder - QueryBuilder documentation