phitech / entities
Provides methods for managing entities that consist two database tables: main and meta. It allows for an indiscrete set of attributes by storing them as key-value sets in the meta table, while the most important attributes and indexes are in the main table. This structure is inspired by the post/pos
README
Author: Phitech Consulting
Package name: phitech/entities
Description: Provides methods for managing entities that consist two database tables: main and meta. It allows for an indiscrete set of attributes by storing them as key-value sets in the meta table, while the most important attributes and indexes are in the main table. This structure is inspired by the post/postmeta structure in Wordpress databases.
Important: This package requires MySQL DBMS. MariaDB won't do. The reason: 👇
"All databases except SQL Server require the columns in the second argument of the upsert method to have a "primary" or "unique" index. In addition, the MySQL database driver ignores the second argument of the upsert method and always uses the "primary" and "unique" indexes of the table to detect existing records." See also: https://laravel.com/docs/9.x/queries#upserts.
Installation
This library is installed using composer.
$ composer require phitech/entities
$ composer install
$ php artisan migrate
Usage
To test if everything is installed correctly in your Laravel application, run:
$ php artisan entities:test
Create a new entity:
$ php artisan entities:make <entity name single> <entity name plural>
For instance 'order':
$ php artisan entities:make order orders
An entity definition will now automatically be placed in the entities table which looks like:
{
"entity_name":"order",
"main_db_table":"orders",
"meta_db_table":"orders_meta",
"meta_instance_id_column":"order_id",
"main_required_columns":["order_id"]
}
You should now make two tables by using database migrations. You must do this manually, because this application is not able to do this for you. Examples of the two database migration scripts are added below.
Main table
$ php artisan migrate create_orders_table
Paste this script below into the newly created migration script. Edit the table name (here: orders). Add table attributes to your own preference and using the guidelines as described below.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('orders', function (Blueprint $table) {
$table->id();
$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->useCurrentOnUpdate()->nullable();
/**
* Here, define the main attributes of this entity. These should be the attributes that
* are used as (composite) keys, indexes, foreign keys, or other attributes that are so
* important that they should not be placed in the meta table.
*/
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('orders');
}
};
Meta table
$ php artisan migrate create_orders_meta_table
You should then paste this script below and only edit the table name (here: orders_meta), entity_id (here: order_id) parameters. Nothing else.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('orders_meta', function (Blueprint $table) {
$table->id();
$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->useCurrentOnUpdate()->nullable();
$table->unsignedBigInteger('order_id');
$table->foreign('order_id')->references('id')->on('orders')->onUpdate('cascade')->onDelete('cascade');
$table->string('meta_key', 255)->nullable();
$table->longText('meta_value')->nullable();
$table->unique(['order_id', 'meta_key']);
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('orders_meta');
}
};
Getting started
class Order extends Entity
{
public function __construct(id = null) {
parent::__construct('order', $id);
}
}