I'm trying to use the CodeIgniter 4 Model for some basic CRUD operation on a database. Now I've come to a point where I'm planning to return the mySQL DATETIME fields as an unix timestamp. What I got so far:
Controller:

<?php namespace App\Controllers\API;

use CodeIgniter\RESTful\ResourceController;
use CodeIgniter\API\ResponseTrait;
use App\Models\ConfigModel;

class Config extends ResourceController
{
    use ResponseTrait;
    public function index()
    {
        $model = new ConfigModel();
        $default = NULL;
        if (null !== $this->request->getVar('config_default')){
            $default = filter_var($this->request->getVar('config_default'), FILTER_VALIDATE_BOOLEAN);
    }
    return $this->respond($model->getConfig($default));
}

Model:

<?php namespace App\Models;

use CodeIgniter\Model;

class ConfigModel extends Model
{
    protected $table = 'configuration';
    protected $primaryKey = 'id';
    protected $allowedFields = ['name','value','is_default'];

    protected function getCurrentConfig($item = NULL){
        if (isset($item)){
            $query = $this->query('SELECT * FROM configuration WHERE id IN (SELECT MAX(id) FROM configuration WHERE name = '.$this->escape($item).')');
        return $query->getResult();
        } else {
            $query = $this->query('SELECT * FROM configuration WHERE id IN (SELECT MAX(id) FROM configuration GROUP BY name)');
            return $query->getResult();
        }
    }

    protected function getDefaultConfig($item = NULL){
        if (isset($item)){
            return $this->where(['is_default' => 1, 'name' => $item])->findAll();
        } else {
            return $this->getWhere(['is_default' => 1])->getResult();
        }
    }

    public function getConfig($default = FALSE){
        if ($default) {
            return $this->getDefaultConfig();
        } else {
            return $this->getCurrentConfig();
        }
    }

Result:

[
    {
        "id": "1",
        "name": "ticket_number_format",
        "value": "default",
        "is_default": "1",
        "create_timestamp": "2021-09-22 09:11:07" // need: 1632294667
    },
    {
        "id": "3",
        "name": "ticket_number_format_default_alphanumeric",
        "value": "true",
        "is_default": "1",
        "create_timestamp": "2021-09-22 09:21:40" // need: 1632295300
    },
    ...
]

I thought about manipulating the result array, but it seems to be too complex for what I'm trying to achieve. I've red about the CodeIgniter Entities, but not sure if this is the right way. Also I didn't find any possibility to alter the SQL statement for single columns with CI tools (i.e. UNIX_TIMESTAMP(create_timestamp))

What would be the best way?

CodePudding user response：

I figured out I was on the right path. The key to this issue are those entity classes from the documentation. I added following entity class:

<?php namespace App\Entities;

use CodeIgniter\Entity\Entity;
use CodeIgniter\I18n\Time;

class Config extends Entity
{
    public function getCreateTimestamp()
    {
        $this->attributes['create_timestamp'] = $this->mutateDate($this->attributes['create_timestamp']);
        return $this->attributes['create_timestamp']->getTimestamp();
    }
}

and modified the model:

<?php namespace App\Models;

use CodeIgniter\Model;

class ConfigModel extends Model
{
    // ...
    // I think this is only needed for those 
    // built-in CRUD function like findAll()
    protected $returnType    = 'App\Entities\Config';

    // ...
    // Use the entity here
    return $query->getResult('App\Entities\Config');

    // ...
    // But can be omitted here
    return $this->where(['is_default' => 1, 'name' => $item])->findAll();

    // ...
}

Gives following result:

[
    {
        "id": "1",
        "name": "ticket_number_format",
        "value": "default",
        "is_default": "1",
        "create_timestamp": 1632319867
    },
    {
        "id": "3",
        "name": "ticket_number_format_default_alphanumeric",
        "value": "true",
        "is_default": "1",
        "create_timestamp": 1632320500
    },
    // ...
}