Php - Mapping Table Column Names to Different Model Properties in Laravel 10 (Without Touching Database Schema)

Rager
May 3, 2023
241 views
1 vote
2 Answers

I’m building a Laravel 10 model for a database I can’t change (since it’s 3rd party). I want to effectively map the column name to something else inside the program.

All model functionality should utilize the new name, but all queries against the database should run with the original name.

Here’s what I tried.

The migration

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('test_models', function (Blueprint $table) {
            $table->id();
            $table->string('test');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('test_models');
    }
};

The Model

use IlluminateDatabaseEloquentFactoriesHasFactory;
use IlluminateDatabaseEloquentModel;

class TestModel extends Model
{
    use HasFactory;

    protected $appends = ['test_changed'];

    public $hidden = ['test'];

    public function __call($method, $parameters)
    {
        if($method === 'getTestChangedAttribute'){
            return $this->getAttribute('test');
        } else if ($method === 'serTestChangedAttribute'){
            return $this->setAttribute('test', $parameters[0]);
        }
        return parent::__call($method, $parameters);
    }

    public function getAttribute($key)
    {
        if($key === 'test_changed'){
            $key = 'test';
        }
        return parent::getAttribute($key);
    }

    public function setAttribute($key, $value)
    {
        if($key === 'test_changed'){
            $key = 'test';
        }
        return parent::setAttribute($key, $value);
    }
}

This works great

TestModel::all();

However, something like this

TestModel::where('test_changed', 'test 1')->get();

Fails with the following:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘test_changed’ in ‘where clause’ (Connection: mysql, SQL: select * from test_models where test_changed = test 1)

How can I effectively change the property to ‘test_changed’ anywhere it may be used?

Tags: laravel php

Answers

Chosen as BEST ANSWER

Alright, after reading through the Laravel source (and a lot of trial and error), I've determined the following was the best solution in my application & covered as many scenarios as I could throw at it.

abstract class BaseModel extends Model
{
    /**
     * Override this to set property names that should be cast to a different
     * table column name.
     * @var array
     */
    static protected $columns = [];


    /**
     * Getter for the property names that should be cast to a different
     * table column name.
     * @param string $key
     * @return mixed
     */
    public function getAttribute($key)
    {
        return parent::getAttribute($this->getGetterKey($key));
    }

    /**
     * Setter for the property names that should be cast to a different table column name.
     * @param string $key
     * @param mixed $value
     * @return mixed
     */
    public function setAttribute($key, $value)
    {
        return parent::setAttribute($this->getSetterKey($key), $value);
    }

    /**
     * Getter for the property names that should be cast to a different
     * table column name.
     * @param string $key
     * @return mixed
     */
    public function __get($key)
    {
        return parent::__get($this->getGetterKey($key));
    }

    /**
     * Setter for the property names that should be cast to a different table column name.
     * @param string $key
     * @param mixed $value
     * @return mixed
     */
    public function __set($key, $value)
    {
        return parent::__set($this->getSetterKey($key), $value);
    }


    /**
     * Convert an attribute key to the column name, dictated by the $columns array.
     * @param string $key
     * @return string
     */
    protected function getGetterKey($key): string
    {
        if (isset(static::$columns[$key]) && !$this->getterExists($key)) {
            $key = static::$columns[$key];
        }

        return $key;
    }

    /**
     * Convert an attribute key to the column name, dictated by the $columns array.
     * @param string $key
     * @return string
     */
    protected function getSetterKey($key): string
    {
        if (isset(static::$columns[$key]) && !$this->setterExists($key)) {
            $key = static::$columns[$key];
        }

        return $key;
    }

    /**
     * Determine if a setter exists for an attribute. See link for how setters are defined
     * @see https://laravel.com/docs/10.x/eloquent-mutators#defining-a-mutator
     * @return bool
     */
    protected function setterExists($attribute): bool
    {
        $method = Str::camel($attribute);
        if (!method_exists($this, $method)) {
            return false;
        }
        if ($this->{$method}()->set === null) {
            return false;
        }
        return true;
    }


    /**
     * Determine if a getter exists for an attribute. Seee link for how getters are defined
     * @see https://laravel.com/docs/10.x/eloquent-mutators#defining-an-accessor
     * @return bool
     */
    protected function getterExists($attribute): bool
    {
        $method = Str::camel($attribute);
        if (!method_exists($this, $method)) {
            return false;
        }
        if ($this->{$method}()->get === null) {
            return false;
        }
        return true;
    }

    /**
     * Convert the property name to the column name.
     * @param string $name
     * @return string
     */
    public static function convertColumnName(string $name): string
    {
        if (isset(static::$columns[$name])) {
            $name = static::$columns[$name];
        }

        return $name;
    }

    /**
     * Override.
     * Convert the property names to the columns names from the $this->columns array.
     * @return array
     */
    public function attributesToArray()
    {
        $attributes = parent::attributesToArray();

        foreach (static::$columns as $convention => $actual) {
            if (array_key_exists($actual, $attributes)) {
                if ($this->getterExists($convention)) {
                    // If a Attribute getter exists, use that instead.
                    $attributes[$convention] = $this->{$convention};
                } else {
                    $attributes[$convention] = $attributes[$actual];
                }
                unset($attributes[$actual]);
            }
        }

        return $attributes;
    }
}

This allows the extended Model to do something like this

use AppBaseBaseModel;
use IlluminateDatabaseEloquentCastsAttribute;

/**
 * ERPPriceTranslator Model
 */
class TestModel extends BaseModel
{
     /** @var array  */
    static protected $columns = [
        'item_number' => 'cd_tp_1_item_no',
        'customer_number' => 'cd_tp_1_cust_no',
        'customer_type' => 'cd_tp_3_cust_type',
        'product_category' => 'cd_tp_2_prod_cat',
    ];

    // Allows for override like this if you need different getters and setters.
    public function customerNumber(): Attribute
    {
        return Attribute::make(
            get: fn() => trim($this->getAttribute('cd_tp_1_cust_no'))
        );
    }
}

In the case of the where Model::where() I then did an explicit convert with a static property

$price = TestModel::where('cd_tp', 1)
         ->where(TestModel::convertColumnName('customer_number'), $customerNumber)
         ->where(TestModel::convertColumnName('item_number'), $itemNumber)
         ->first();

(Edit)

Create a parent abstract class (ParentModel class below). Notice this approach fits better giving the existing functionality and structures you have already.

use IlluminateDatabaseEloquentModel;

abstract class ParentModel extends Model {

protected $columns = [];

public function attributesToArray()
{
    $attributes = parent::attributesToArray();
    foreach ($this->columns as $convention => $actual) {
        if (array_key_exists($actual, $attributes)) {
            $attributes[$convention] = $attributes[$actual];
            unset($attributes[$actual]);
        }
    }
    return $attributes;
}

public function getAttribute($key)
{
    if (array_key_exists($key, $this->columns)) {
        $key = $this->columns[$key];
    }
    return parent::getAttributeValue($key);
}

public function setAttribute($key, $value)
{
    if (array_key_exists($key, $this->columns)) {
        $key = $this->columns[$key];
        }
        return parent::setAttribute($key, $value);
    }

}
}

Afterwards override $colums in child models and you should be good to perform queries as defined by you.

protected $columns = [
    'test_changed' => 'test',

];

Please signup or login to give your own answer.

Click here to cancel reply.

Php – Mapping Table Column Names to Different Model Properties in Laravel 10 (Without Touching Database Schema)

Answers