Avoid conflict between column names ?

Hello to the great CI community !

Before i used to write my columns names as follow : 

[dbprefix]_[tablename]_[columnname] for example abc_recipes_name 

It avoided any conflict with other tables in case of joining for example.
but seems nobody do that except me, so i'm back to just the column name but in case of joining it cause some issues. 


My actual way of fixing is to use aliases in the SELECT but i'm not sure if it's the best practice, it's a bit of messy.

Is there any better way using CI ? 

Thanks a lot,

I am not expert on it but in my experience best way is to use tablename_columnname pattern for column names. this way is good when joining tables

I am not expert on it but in my experience best way is to use tablename_columnname pattern for column names. this way is good when joining tables

I think so. I stopped when i started to learn CakePHP, it was out of the convention of this framework. Then i kept the CakePHP way when i went to CI. 
But maybe CI has its own way to deal with it.

I just saw in the documentation that CI has dbprefix()

https://www.codeigniter.com/user_guide/d...s-manually

But i don't really get how it works. So i keep my database without changing the column name, but i define a dbprefix and then when i use CI Query Builder, i write the column name including the prefix as if it's native ? Or the dbprefix only applies on the table name and not on the column names ?

I think the best approach is prefix with the table name on foreign keys only, and using SQL aliases for duplicated collumn names. Ex.:

Table user
col id
col name
col age
col company_id

Table company
col id
col name
col address

Code:

SELECT
    user.*,
    company.name AS company_name
FROM user
JOIN company ON company.id = company_id;

So you can get your objects this way:

PHP Code:

$this->db->select('user.*');
$this->db->select('company.name AS company_name');
$this->db->join('company', 'company.id = company_id');
$user = $this->db->get('user')->row();

echo "$user->name, $user->company_name"; 


If you prefix your collumns in your db with the table name, even if you make a simple select, you'll have a ugly code like that:

PHP Code:

echo "$user->user_name, $user->user_age"; 


At least for me, repeating the table name is annoying and makes my code harder to read.

https://www.w3schools.com/sql/sql_alias.asp

In my tables I have for example a recipes table: recipe_id, recipe_name and in an ingredients table: ingredient_id, ingredient_name. Just be careful not to use recipe as your table name base for any other table. So the recipe_items table is: item_id, item_recipe_id and item_ingredient_id.

No conflicts, no problems, very clear to use. (IMHO)

Paul

In my tables I have for example a recipes table: recipe_id, recipe_name and in an ingredients table: ingredient_id, ingredient_name. Just be careful not to use recipe as your table name base for any other table. So the recipe_items table is: item_id, item_recipe_id and item_ingredient_id.

No conflicts, no problems, very clear to use. (IMHO)

Paul

Best practice for me has always been to load each record as a model. Then name your columns whatever you want, the database logic will be tied to the model and you would just use $my_model->full_name which could be something like... (Warning: untested code)

# In some controller method

PHP Code:

<?php
$user_model = new User(ci()->session->user_data('id')); 


# In some view

Code:

<p><?php echo $user_model->full_name ?></p>
<div class="work-history">
    <?php if(0 === $user_model->work_history->total) : ?>
    <p class="none">No work history was found.</p>
    <?php else : ?>
    <ul>
        <?php foreach($user_model->work_history->limit(5) as $work_history_model) : ?>
        <li><a href="<?php echo $work_history_model->company->details_url ?>" class="out"><?php $work_history_model->company->legal_name ?></a>, <?php echo $work_history_model->formatted_date_range ?></em>)</li>
        <?php endforeach ?>
    </ul>

    <?php if(TRUE === $user_model->work_history->has_more) : ?>
    <p class="view-all"><a href="<?php echo $user_model->details_url ?>">View full work history</a></p>
    <?php endif ?>

    <?php endif ?>
</div>

/application/config/config.php

PHP Code:

<?php defined('BASEPATH') OR exit('No direct script access allowed');

define('ALLOW_DEBUG', FALSE);
define('STATUS_ERROR', 'error');
define('STATUS_SUCCESS', 'success');

/*
|--------------------------------------------------------------------------
| Auto-load models, controllers, libraries, etc.
|--------------------------------------------------------------------------
| Keep the php autoloader in this file - do not place in autoloader.php
| If it's moved out of this file, classes will not be loaded soon enough.
*/

function application_autoloader($class) {
    $class = $class.'.php';

    if(ALLOW_DEBUG) : 
        $locations = array(
            APPPATH.'models-TESTING'.DIRECTORY_SEPARATOR.strtolower($class),
            APPPATH.'controllers-TESTING'.DIRECTORY_SEPARATOR.strtolower($class),
        );
    else : 
        $locations = array();
    endif;

    $locations = array_merge($locations, array(
        APPPATH.'models'.DIRECTORY_SEPARATOR.strtolower($class),
        APPPATH.'collections'.DIRECTORY_SEPARATOR.strtolower($class),
        APPPATH.'controllers'.DIRECTORY_SEPARATOR.$class,
        APPPATH.'libraries'.DIRECTORY_SEPARATOR.$class,
        APPPATH.'core'.DIRECTORY_SEPARATOR.$class,
    ));

    foreach($locations as $location) :
        if(file_exists($location)) :
            include $location;
            break;
        endif;
    endforeach;
}
spl_autoload_register('application_autoloader');

function system_autoloader($class) {
    $class = ucfirst(str_replace('CI_', '', $class)).'.php';

    $locations = array(
        BASEPATH.'libraries'.DIRECTORY_SEPARATOR.$class,
        BASEPATH.'core'.DIRECTORY_SEPARATOR.$class,
    );

    foreach($locations as $location) : 
        if(file_exists($location)) :
            include $location;
            break;
        endif;
    endforeach;
}
spl_autoload_register('system_autoloader');

# ... Rest of config file... 


/application/helpers/utilities.php

PHP Code:

<?php defined('BASEPATH') OR die('No direct script access allowed');

if(!function_exists('ci')) : 
    function ci() {
        return get_instance();
    }
endif;

if(!function_exists('formatEmailAddress')) : 
    function formatEmailAddress($string) {
        return trim(strtolower($string));
    }
endif;

if(!function_exists('formatPhoneNumber')) : 
    function formatPhoneNumber($string) {
        return $string;
    }
endif;

if(!function_exists('isValidEmailAddress')) : 
    function isValidEmailAddress($string) {
        return 3 <= strlen($string) && strstr($string, '@');
    }
endif;

if(!function_exists('isValidPhoneNumber')) :
    function isValidPhoneNumber($string) {
        return TRUE;
    }
endif; 


/application/core/MY_Model.php

PHP Code:

<?php defined('BASEPATH') OR die('No direct script access allowed');

class MY_Model {
    const DB_TBL_ALIAS = 'model.';
    const DB_TBL_NAME = NULL;
    const DB_TBL_PK = NULL;

    protected $process_results = array();
    protected $properties = NULL;
    
    public function __construct($id=NULL) {
        if(is_numeric($id)) : 
            $this->loadById($id);
        endif;
    }
    
    public function __get($key) {
        if(array_key_exists($key, $this->properties)) : 
            return $this->properties[$key];
        endif;
    
        $lazy_load_property = '__lazy_load_'.$key;
    
        if(array_key_exists($lazy_load_property, $this->properties)) :
            return $this->properties[$lazy_load_property];
        endif;

        $lazy_load_method = '__load'.str_replace(' ', '', ucwords(str_replace('_', ' ', $key)));
    
        if(method_exists($this, $lazy_load_method)) :
            $this->properties[$lazy_load_property] = $this->$lazy_load_method();
            return $this->properties[$lazy_load_property];
        endif;
    
        return NULL;
    }

    public function __loadDetailsUrl() {
        return NULL;
    }

    public function __loadIsLoaded() {
        return NULL !== $this->properties && NULL === $this->deleted_date;
    }

    public function __modifyLoadQuery() {
        return TRUE;
    }

    public function getProcessResults() {
        return empty($this->process_results) ? NULL : $this->process_results;
    }

    public function loadByArray($properties=NULL) {
        $this->properties = NULL;

        if(is_array($properties) && !empty($properties)) : 
            $this->properties = $properties;
        endif;

        return $this;
    }

    public function loadByColumn($column, $value) {
        $this->properties = NULL;

        if(empty($value) || !is_string($value)) : 
            return $this;
        endif;

        $column = 'id' === $column ? static::DB_TBL_PK : $column;
        $column = strstr($column, '.') ? substr($column, strpos($column, '.')+1) : $column;

        ci()->db
            ->select(static::DB_TBL_ALIAS.'*')
            ->from(trim(static::DB_TBL_NAME.' '.str_replace('.', '', static::DB_TBL_ALIAS))
            ->where(static::DB_TBL_ALIAS.$column, $value);

        $this->__modifyLoadQuery();

        $qr = ci()->db->get();
    
        if(0 < $qr->num_rows()) :
            $this->properties = $row;
        endif;
    
        return $this;
    }

    public function loadById($id=NULL) {
        return $this->loadByColumn('id');
    }

    public function processCreate($data_set=array()) {
        $this->setProcessResult(STATUS_ERROR, __METHOD__.' is not overloaded in '.static::class);
        return FALSE;
    }

    public function processDelete($data_set=array()) {
        ci()->db
            ->set('updated_date', 'NOW()', FALSE)
            ->set('deleted_date', 'NOW()', FALSE)
            ->where(static::DB_TBL_PK, $this->id)
            ->update(static::DB_TBL_NAME);

        $this->processPostDelete($data_set);
        $this->setProcessResult(STATUS_SUCCESS, 'Record was deleted');

        return TRUE;
    }

    protected function processPostCreate($data_set=array()) {
        # Overload in model; Send email, log data changes/user action, etc, etc
    }

    protected function processPostDelete($data_set=array()) {
        # Overload in model; Send email, log data changes/user action, etc, etc
    }

    protected function processPostUpdate($data_set=array()) {
        # Overload in model; Send email, log data changes/user action, etc, etc
    }

    protected function processPrepareData($data_set=array()) {
        # Make sure all necessary fields are present
        return $data_set;
    }

    protected function processPreValidate($data_set=array()) {
        return TRUE;
    }

    public function processUpdate($data_set=array()) {
        $this->setProcessResult(STATUS_ERROR, __METHOD__.' is not overloaded in '.static::class);
        return FALSE;
    }

    protected function setProcessResult($status, $message=NULL) {
        $this->process_results[] = array(
            'status' => $status,
            'message' => $message,
        );

        return TRUE;
    }
} 


/application/libraries/model_collection.php

PHP Code:

<?php defined('BASEPATH') OR die('No direct script access allowed');

class Model_collection implements IteratorAggregate {
    protected $db_table_alias = 'model.';
    protected $db_table_name = NULL;
    protected $db_table_pk = NULL;
    protected $limit = NULL;
    protected $joins = array();
    protected $model = NULL;
    protected $orders = array();
    protected $filters = array();
    protected $page_records = NULL;
    protected $properties = array();
    protected $selects = array();
    protected $total_records = NULL;

    public function __construct($model_name) {
        if(!class_exists($model_name)) : 
            die('Invalid model name ('.$model_name.') in '.__METHOD__);
        endif;

        $this->model = new $model_name();
        $this->db_table_name = constant(get_class($this->model).'::DB_TBL_NAME');
        $this->db_table_pk = constant(get_class($this->model).'::DB_TBL_PK');
    }

    public function __get($key) {
        switch($key) : 
            case 'id' : 
                $key = $this->db_table_pk;
            break;
        endswitch;

        if(array_key_exists($key, $this->properties)) : 
            return $this->properties[$key];
        endif;

        $lazy_load_property = '__lazy_load_'.$key;
    
        if(array_key_exists($lazy_load_property, $this->properties)) :
            return $this->properties[$lazy_load_property];
        endif;

        $lazy_load_method = '__load'.str_replace(' ', '', ucwords(str_replace('_', ' ', $key)));
    
        if(method_exists($this, $lazy_load_method)) :
            $this->properties[$lazy_load_property] = $this->$lazy_load_method();
            return $this->properties[$lazy_load_property];
        endif;
    
        return NULL;
    }

    public function __loadHasMore() {
        return NULL !== $this->limit && $this->total > $this->limit;
    }

    public function __loadPageRecords() {
        $this->loadRecords();
        return $this->page_records;
    }

    public function __loadTotal() {
        $this->loadTotal();
        return $this->total_records;
    }

    public function byOrder($order) {
        $order = $order > 0 ? $order-1 : $order;
        return is_array($this->page_records) && isset($this->page_records[$order]) ? $this->page_records[$order] : NULL;
    }

    public function filter($column, $value, $method=NULL) {
        $column = 'id' === $column ? $this->db_table_pk : $column;
        $column = !strstr($column, '.') ? $this->db_table_alias.$column : $column;

        $method = empty($method) ? 'where' : $method;

        $this->filters[] = array(
            'column' => $column,
            'value' => $value,
            'method' => $method,
        );

        return $this;
    }

    public function filterByKey($key=NULL, $value=NULL) {
        switch(url_title($key, '_', TRUE)) : 
            case 'active' : 
                $this->filter('deleted_date', NULL);
            break;
            case 'id' : 
                $method = is_array($value) ? 'where_in' : 'where';
                $this->filter('id', $value, $method);
            break;
        endswitch;

        return $this;
    }

    public function first() {
        return $this->byOrder(1);
    }

    public function getIterator() {
        return new ArrayIterator($this->page_records);
    }

    public function join($table, $condition, $type='inner') {
        $type = trim(str_ireplace('join', '', $type));
        $type = in_array(strtolower($type), array('inner', 'outer', 'left', 'right')) ? strtolower($type) : 'inner';

        $this->joins[] = array(
            'table' => $table,
            'condition' => $condition,
            'type' => $type,
        );

        return $this;
    }

    public function last() {
        return $this->byOrder($this->limit);
    }

    public function limit($count=NULL) {
        if(is_numeric($count)) : 
            $this->limit = (int) $count;
        endif;

        return $this;
    }

    public function loadTotal($force=FALSE) {
        if(FALSE === $force && NULL !== $this->total_records) : 
            return $this;
        endif;

        if(NULL === ($records_query = $this->readyRecordsQuery(FALSE))) : 
            $this->total_records = 0;
        else : 
            $qr = ci()->db->query('SELECT COUNT(*) AS '.ci()->db->escape_str('num_rows').' FROM ('.$records_query.') AS temp');
            $this->total_records = $qr->row()->num_rows;
        endif;

        $this->limit($this->total_records);
        return $this;
    }

    public function loadRecords($force=FALSE) {
        $this->loadTotal($force);

        if(0 === $this->total_records) : 
            return $this;
        endif;

        if(FALSE === $force && NULL !== $this->page_records) : 
            return $this;
        endif;

        $this->page_records = array();
        $qr = ci()->db->query($this->readyRecordsQuery(TRUE));

        if(0 < $qr->num_rows()) : 
            foreach($qr->result() as $row) : 
                $this->page_records[] = $row;
            endforeach;
        endif;

        return $this;
    }

    public function order($column, $direction) {
        $direction = in_array(strtolower($direction), array('asc', 'desc')) ? strtolower($direction) : 'asc';

        $this->orders[] = array(
            'column' => $column,
            'direction' => $direction,
        );

        return $this;
    }

    public function orderByKey($key=NULL, $direction=NULL) {
        switch(url_title($key, '_', TRUE)) : 
            case 'created' : 
                $this->order('created_date', $direction);
            break;
            case 'id' : 
                $this->order('id', $direction);
            break;
        endswitch;

        return $this;
    }

    public function readyRecordsQuery($include_limit=TRUE) {
        # Get creative...
        # Build out select with selects/joins/filters/orders/limit/etc
    }

    public function reset() {
        $this->limit = NULL;
        $this->orders = array();
        $this->filters = array();
        $this->page_records = NULL;
        $this->properties = array();
        $this->total_records = NULL;

        return $this;
    }

    public function select($column) {
        $column = 'id' ? $this->db_table_pk : $column;
        $column = !strstr($column, '.') ? $this->db_table_alias.$column : $column;

        $this->selects[$column] = $column;
        return $this;
    }
} 


/application/collections/work_history_collection.php

PHP Code:

<?php defined('BASEPATH') OR die('No direct script access allowed');

class Work_history_collection extends Model_collection {
    public function __construct() {
        parent::__construct('Work_history');

        $this->join(Company::DB_TBL_NAME.' company', 'company.'.Company::DB_TBL_PK.' = '.$this->db_table_alias.'company_id', 'inner')
             ->filter('company.deleted_date', NULL);
    }

    public function filterByKey($key=NULL, $value=NULL) {
        switch(url_title($key, '_', TRUE)) : 
            case 'active' : 
                $this->filter('status', Work_history::STATUS_ACTIVE);
            break;
            case 'hired_after' : 
                $this->filter('hired_date >=', $value);
            break;
            case 'hired_before' : 
                $this->filter('hired_date <=', $value);
            break;
            case 'user' : 
                $method = is_array($value) ? 'WHERE_IN' : 'WHERE';
                $this->filter('user_id', $value, $method);
            break;
        endswitch;

        return parent::filterByKey($key, $value);
    }

    public function orderByKey($key, $direction) {
        switch(url_title($key, '_', TRUE)) : 
            case 'hired' : 
                $this->order('hired_date', $direction);
            break;
        endswitch;

        return parent::orderByKey($key, $direction);
    }
} 


/application/models/user.php

PHP Code:

<?php defined('BASEPATH') OR die('No direct script access allowed');

class User extends MY_Model {
    const DB_TBL_NAME = 'user';
    const DB_TBL_PK = 'id';

    public function __loadCurrentJob() {
        $collection = new Work_history_collection();

        if(NULL !== ($work_history_model = $collection->filterByKey('active')->filterByKey('user', $this->id)->orderByKey('hired', 'desc')->limit(1)->first())) : 
            return TRUE === $work_history_model->is_current ? $work_history_model : new Work_history();
        endif;

        return NULL;
    }

    public function __loadDetailsUrl() {
        return site_url(array('user-profile', $this->slug));
    }

    public function __loadFullName() {
        return TRUE === $this->is_loaded ? trim($this->first_name.' '.$this->last_name) : NULL;
    }

    public function __loadSlug() {
        return TRUE === $this->is_loaded ? url_title($this->full_name.' '.$this->id, '-', TRUE) : NULL;
    }

    public function __loadWorkHistory() {
        $collection = new Work_history_collection();
        return $collection->filterByKey('active')->filterByKey('user', $this->id);
    }

    public function __modifyLoadQuery() {
        ci()->db
            ->select('extras.*')
            ->join('user_extras extras', 'extras.user_id = '.static::DB_TBL_ALIAS.static::DB_TBL_PK, 'inner');

        return parent::__modifyLoadQuery();
    }

    public function processCreate($data_set=array()) {
        if(TRUE === $this->is_loaded) : 
            $this->setProcessResult(STATUS_ERROR, 'Record is already loaded');
            return FALSE;
        endif;

        if(!is_array($data_set) || empty($data_set)) : 
            $this->setProcessResult(STATUS_ERROR, 'Data set cannot be empty');
            return FALSE;
        endif;

        $data_set = $this->processPrepareData($data_set);

        if(TRUE !== $this->processPreValidate($data_set)) : 
            return FALSE;
        endif;

        ci()->db
            ->set('first_name', $data_set['first_name'])
            ->set('last_name', $data_set['last_name'])
            ->set('email_address', $data_set['email_address'])
            ->set('phone_number', $data_set['phone_number'])
            ->set('created_date', 'NOW()', FALSE)
            ->set('updated_date', 'NOW()', FALSE)
            ->insert(static::DB_TBL_NAME);

        $this->loadById(ci()->db->insert_id());

        if(FALSE === $this->is_loaded) : 
            $this->setProcessResult(STATUS_ERROR, 'Failed to locate new record');
            return FALSE;
        endif;

        $this->processPostCreate($data_set);
        $this->setProcessResult(STATUS_SUCCESS, 'Record was created');

        return TRUE;
    }

    protected function processPrepareData($data_set=array()) {
        if(isset($data_set['full_name'])) : 
            $names = explode(' ', trim($data_set['full_name']));
            $names = array_filter($names, 'strlen');

            $data_set['first_name'] = array_pop($names);
            $data_set['last_name'] = implode(' ', $names);
        endif;

        if(!isset($data_set['email_address'])) : 
            $data_set['email_address'] = TRUE === $this->is_loaded ? $this->email_address : NULL;
        else : 
            $data_set['email_address'] = formatEmailAddress($data_set['email_address']);
        endif;

        if(!isset($data_set['phone_number'])) : 
            $data_set['phone_number'] = TRUE === $this->is_loaded ? $this->phone_number : NULL;
        else : 
            $data_set['phone_number'] = formatPhoneNumber($data_set['phone_number']);
        endif;

        return $data_set;
    }

    protected function processPreValidate($data_set=array()) {
        if(TRUE !== isValidEmailAddress($data_set['email_address'])) : 
            $this->setProcessResult(STATUS_ERROR, 'Email address is invalid');
            return FALSE;
        endif;

        if(!empty($data_set['phone_number']) && TRUE !== isValidPhoneNumber($data_set['phone_number'])) : 
            $this->setProcessResult(STATUS_ERROR, 'Phone number appears invalid');
            return FALSE;
        endif;

        $user_model = new User();
        $user_model->loadByColumn('email_address', $data_set['email_address']);

        if(TRUE === $user_model->is_loaded) : 
            $this->setProcessResult(STATUS_ERROR, 'Email address is already in use');
            return FALSE;
        endif;

        return TRUE;
    }

    public function processUpdate($data_set=array()) {
        if(TRUE !== $this->is_loaded) : 
            $this->setProcessResult(STATUS_ERROR, 'Record is not loaded');
            return FALSE;
        endif;

        if(!is_array($data_set) || empty($data_set)) : 
            $this->setProcessResult(STATUS_ERROR, 'Data set cannot be empty');
            return FALSE;
        endif;

        $data_set = $this->processPrepareData($data_set);

        if(TRUE !== $this->processPreValidate($data_set)) : 
            return FALSE;
        endif;

        ci()->db
            ->set('first_name', $data_set['first_name'])
            ->set('last_name', $data_set['last_name'])
            ->set('email_address', $data_set['email_address'])
            ->set('phone_number', $data_set['phone_number'])
            ->set('updated_date', 'NOW()', FALSE)
            ->where(static::DB_TBL_PK, $this->id)
            ->update(static::DB_TBL_NAME);

        $this->loadById(ci()->db->insert_id());

        if(FALSE === $this->is_loaded) : 
            $this->setProcessResult(STATUS_ERROR, 'Failed to locate new record');
            return FALSE;
        endif;

        $this->processPostUpdate($data_set);
        $this->setProcessResult(STATUS_SUCCESS, 'Record was created');

        return TRUE;
    }
} 


/application/models/work_history.php

PHP Code:

<?php defined('BASEPATH') OR die('No direct script access allowed');

class Work_history extends MY_Model {
    const DB_TBL_NAME = 'work_history';
    const DB_TBL_PK = 'id';
    const STATUS_ACTIVE = 'true';
    const STATUS_INACTIVE = 'false';

    public function __loadCompany() {
        return new Company($this->company_id);
    }

    public function __loadDateRange() {
        return array(
            'hired' => $this->hired_date, 
            'terminated' => $this->terminated_date,
        );
    }

    public function __loadFormattedDateRange() {
        if(NULL !== $this->date_range['hired']) : 
            if(NULL !== $this->date_range['terminated']) : 
                return $this->date_range['hired'].' - '.$this->date_range['terminated'];
            endif;

            return $this->date_range['hired'].' - Current';
        endif;

        return 'Unknown';
    }

    public function __loadHiredDate() {
        if(TRUE === $this->is_loaded && FALSE !== ($date_ts = strtotime($this->hired_date))) : 
            return date('m/d/Y', $date_ts);
        endif;

        return NULL;
    }

    public function __loadIsCurrent() {
        return NULL !== $this->date_range['hire'] && NULL === $this->date_range['terminate'];
    }

    public function __loadTerminatedDate() {
        if(TRUE === $this->is_loaded && FALSE !== ($date_ts = strtotime($this->terminated_date))) : 
            return date('m/d/Y', $date_ts);
        endif;

        return NULL;
    }

    public function __modifyLoadQuery() {
        ci()->db
            ->join(Company::DB_TBL_NAME.' company', 'company.'.Company::DB_TBL_PK.' = '.static::DB_TBL_ALIAS.'company_id', 'inner')
            ->where('company.deleted_date', NULL);

        return parent::__modifyLoadQuery();
    }
} 


/application/models/company.php

PHP Code:

<?php defined('BASEPATH') OR die('No direct script access allowed');

class Company extends MY_Model {
    const DB_TBL_NAME = 'company';
    const DB_TBL_PK = 'id';

    public function __loadDetailsUrl() {
        return TRUE === $this->is_loaded ? site_url(array('company-profile', $this->slug)) : NULL;
    }

    public function __loadLegalName() {
        return TRUE === $this->is_loaded ? trim($this->name.' '.$this->entity_type) : NULL;
    }

    public function __loadSlug() {
        return TRUE === $this->is_loaded ? url_title($this->legal_name.' '.$this->id, '-', TRUE) : NULL;
    }

    public function __loadWebsiteUrl() {
        return TRUE === $this->is_loaded && !empty($this->website) ? $this->website : NULL;
    }

    public function __modifyLoadQuery() {
        ci()->db
            ->select('extras.*')
            ->join('company_extras extras', 'extras.company_id = '.static::DB_TBL_ALIAS.static::DB_TBL_PK, 'inner');

        return parent::__modifyLoadQuery();
    }
} 


I just saw in the documentation that CI has dbprefix()

https://www.codeigniter.com/user_guide/d...s-manually

But i don't really get how it works. So i keep my database without changing the column name, but i define a dbprefix and then when i use CI Query Builder, i write the column name including the prefix as if it's native ? Or the dbprefix only applies on the table name and not on the column names ?

Pretty sure it's just table name. Think multiple CI sites within a single DB container.

Best practice for me has always been to load each record as a model. Then name your columns whatever you want, the database logic will be tied to the model and you would just use $my_model->full_name which could be something like.... (you'll have to setup an autoloader or 2 to use this method...)

That actually is a really interesting idea. I will have a closer look at it on the next build I do, thankyou.

Paul

That actually is a really interesting idea. I will have a closer look at it on the next build I do, thankyou.

Paul

Made the example a little more robust to give you a more complete idea of how records are managed, etc.

Let me know if you end up using something similar.