Using a sql alias within a model's find() function

I have a table that containes both Engligh and Spanish translations. The Spanish translation is a bit of an afterthough... so I am looking for an easy way to implement a language change accross my sight. What I am doing now is performing a callback after a model's find() function to unset all the translated columns and return only the sessions language:
test_en ---> test
unset(test_en)
unset(test_es)

Is there a way to implement an alias in the beforeFind callback? Can I modify the find query to select 'test_'.$_SESSION['lang'] AS test

Essentually, I would like to not fetch both columns just to unset the ones I don't want.

Here is my test model I am trying to play with:

PHP Code:

<?php 
namespace App\Models;
use CodeIgniter\Model;
class TestModel extends Model
{
    protected $table      = 'tblzz';
    protected $primaryKey = 'id';
    protected $useAutoIncrement = true;
    protected $returnType    = 'array';
//TODO: $useSoftDeletes = true; causes ->find() to return null
    protected $useSoftDeletes = false;
    protected $allowedFields = ['id',
                                'test_en',
                                'test_es',
                                'date'];
    // Callbacks
    protected $allowCallbacks = true;
    protected $beforeInsert  = [];
    protected $afterInsert    = [];
    protected $beforeUpdate  = [];
    protected $afterUpdate    = [];
    protected $beforeFind    = ['pickLang'];
    protected $afterFind      = ['parseLang'];
    protected $beforeDelete  = [];
    protected $afterDelete    = [];
    
    public function pickLang($data){
        // Can I modify the query here to alias column 'test_'.$_SESSION['lang'] AS test
    }
    public function parseLang($data){
        if(isset($data['data'])){
                $tmp = false;
                if(isset($data['data']['id'])){
                    //only one row, add an additional dimension to array in prep foreach loop
                    $tmp['data'][0] = $data['data'];
                    $data = $tmp;
                }
                
                foreach($data['data'] as $key => $d) {
                    $test = $d['test_'.$_SESSION['lang']];
                    unset($data['data'][$key]['test_en']);
                    unset($data['data'][$key]['test_es']);
                    $data['data'][$key]['test'] = $test;
                }
              
              if($tmp){
                  //remove additional dimension if one was added
                  $data['data'] = $data['data'][0];
              }
              
          }
          return $data;
    }
} 


Why are you doing it like that, CodeIgniter has Localization built in to it?
I have a almost complete localization layout already built with Dark Mode and Language DropDown with
Flags, Home, About and Contacts.
I' am still working on it so there may still be some minor bugs in it but it is working.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

PHP Code:

$locale = service('request')->getLocale();
// validate & sanitize $locale
"select test_{$locale} AS test FROM tblzz;" 


Validate/sanitize locale before plugging anything into a query string like that to prevent SQL injection attacks and/or use appropriate QueryBuilder methods.
However, the more appropriate way to do this would be to create a locale column and then modify your query to be:

PHP Code:

"select test FROM tblzz WHERE locale={$locale};" 


CodeIgniter isn't opinionated on how you handle localization when using a database, so the way to do is left up to the developer. If your table is primarily static and the information won't change, you're better off using the flat file approach that's already supported within CodeIgniter. You can store the data as a serialized array (if for some reason you need it raw) or with html already embedded.

Halberd | CodeIgniter Sandbox

You can also use this library, although it uses slightly different table layout: https://github.com/michalsn/codeigniter-translatable