Laravel Table Prefix Namespaces

Miris

<2021-11-11 Thu>


In one of the larger platforms I've somehow concocted using Laravel, I had to "namespace" the table names in the database with separate prefixes:

Model Table
VPS/Patient vps_patients
VPS/Form vps_forms
RID/Project rid_projects
RID/Location rid_locations
SCA/Form sca_forms
SCA/Discipline sca_disciplines

Many of the tables referenced tables in other "namespaces", thus it was much more practical to keep the tables in the same database. Prefixing prevents collisions between table names, and also groups the tables together in similar fashion to the corresponding models.

Solution

The solution is pretty pragmatic in nature, and also maintains compatibility with framework's source code. All it requires is creating a new Model abstract with the following code:

<?php

namespace App\Models;

abstract class Model extends \Illuminate\Database\Eloquent\Model
{
  protected $prefix;

  /**
   * Returns the database table name with awareness of the prefix.
   *
   * @return string name of the model's table name in the database.
   */
  public function getTable(bool $usePrefix = true): string
  {
    /**
     * Prevent redundant prefix when not wanting a prefix, or when this method is invoked multiple times.
     */

    $table = str_replace($this->prefix, null, parent::getTable());

    /**
     * Conditionally prepend the prefix if desired, otherwise return the table name without any prefix at all.
     */

    return $usePrefix ? ($this->prefix . $table) : $table;
  }
}

public static function table($usePrefix = true): string
{
  return (new static())->getTable($usePrefix);
}

Usage

Have your Eloquent models inherit this Model instead of the default Eloquent one that comes with the framework. Once done, define the respective prefix as necessary:

<?php

class Project extends Model // our Model, not the Eloquent one!
{
    $prefix = 'pfx_';
}

You can now elegantly retrieve the model's table name like so:

Schema::create(Project::table(), function (Blueprint $table) {
    // Project::table() == pfx_projects
});

Schema::create(Project::table(false), function (Blueprint $table) {
    // Project::table(false) == projects
});

Pivot Tables

Prefixing pivot tables is a bit more involved. You'll have to sacrifice Laravel's table inference in favour of explicitly providing the pivot table name. I sought to make it as convenient as possible.

Solution

Add the following methods to your Model abstract:

<?php

/**
 * Returns the database pivot table name for the current model & inbound model, with awareness of the prefix and
 * calling model.
 *
 * @param Model $model     related model for the pivot relationship.
 * @param bool  $usePrefix append prefix to pivot table name.
 * @return string name of the two models' pivot table.
 */
public function getTablePivot(Model $model, bool $usePrefix = true): string
{
  /**
   * Ensure that the prefix isn't redundantly acknowledged.
   */

  $own = $this->getTable(false);
  $rel = $model->getTable(false);

  /**
   * Ensure that $model1->getTablePivot($model2) == $model2->getTablePivot($model1)
   */

  $concat = ord($own[0]) < ord($rel[0])
    ? sprintf("%s_%s", $own, $rel)
    : sprintf("%s_%s", $rel, $own);

  return $usePrefix ? ($this->prefix . $concat) : $concat;
}

public static function tablePivot(Model $model, $usePrefix = true): string
{
  return (new static())->getTablePivot($model, $usePrefix);
}

Usage

The usage is pretty straightforward, yet quite spiffy:

<?php

/**
 * IN DATABASE MIGRATIONS
 */

Schema::create(Researcher::tablePivot(new Project()), function (Blueprint $table) {
  // Researcher::tablePivot(new Project()) == "pfx_projects_researchers"
});

/**
 * IN ELOQUENT RELATIONSHIPS
 */

// in the Researcher model
public function projects(): BelongsToMany
{
    return $this->belongsToMany(Project::class, Project::tablePivot($this));
}

// in the Project model
public function researchers(): BelongsToMany
{
    return $this->belongsToMany(Researcher::class, Researcher::tablePivot($this));
}

Full Code

<?php

namespace App\Models;

/**
 * Model abstract for Models with prefixes.
 *
 * Class Model
 * @package App\Models
 * @author Miris Wisdom
 */
abstract class Model extends \Illuminate\Database\Eloquent\Model
{
  protected $prefix;

  /**
   * Returns the database table name with awareness of the prefix.
   *
   * @return string name of the model's table name in the database.
   */
  public function getTable(bool $usePrefix = true): string
  {
    /**
     * Prevent redundant prefix when not wanting a prefix, or when this method is invoked multiple times.
     */

    $table = str_replace($this->prefix, null, parent::getTable());

    /**
     * Conditionally prepend the prefix if desired, otherwise return the table name without any prefix at all.
     */

    return $usePrefix ? ($this->prefix . $table) : $table;
  }

  /**
   * Returns the database pivot table name for the current model & inbound model, with awareness of the prefix and
   * calling model.
   *
   * @param Model $model     related model for the pivot relationship.
   * @param bool  $usePrefix append prefix to pivot table name.
   * @return string name of the two models' pivot table.
   */
  public function getTablePivot(Model $model, bool $usePrefix = true): string
  {
    /**
     * Ensure that the prefix isn't redundantly acknowledged.
     */

    $own = $this->getTable(false);
    $rel = $model->getTable(false);

    /**
     * Ensure that $model1->getTablePivot($model2) == $model2->getTablePivot($model1)
     */

    $concat = ord($own[0]) < ord($rel[0])
    ? sprintf("%s_%s", $own, $rel)
    : sprintf("%s_%s", $rel, $own);

    return $usePrefix ? ($this->prefix . $concat) : $concat;
  }

  public static function table($usePrefix = true): string
  {
    return (new static())->getTable($usePrefix);
  }

  public static function tablePivot(Model $model, $usePrefix = true): string
  {
    return (new static())->getTablePivot($model, $usePrefix);
  }
}