Provides EAV (Entity-Attribute-Value) design pattern.
Installs: 1 576
Dependents: 2
Suggesters: 0
Security: 0
Stars: 5
Watchers: 3
Forks: 4
Type:cakephp-plugin
Suggests
- quickapps-plugins/cms: Allows to store extended meta-information for each virtual column as a serialized array.
This package is not auto-updated.
Last update: 2024-12-21 18:56:08 UTC
README
Entity–attribute–value model (EAV) is a data model to describe entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. In mathematics, this model is known as a sparse matrix. EAV is also known as object–attribute–value model, vertical database model and open schema.
—Wikipedia
Introduction
You will typically use an EAV pattern when you have Entities with a variable number of attributes, and these attributes can be of different types. This makes it impossible to define these attributes as column in the entity's table, because there would be too many, most of them will not have data, and you can't deal with dynamic attributes at all because columns need to be pre-defined in relational databases.
To solve this situation in a relational fashion you would create a child table, and relate that to the 'entity' table using a One-to-Many relation, where every attribute would become a record in the child table. Downside of this approach however is that to be able to get a specific attribute value, you'll have to loop over all related records, compare the value of the attribute column with the attribute you look for, and if a match is found, get the contents of the value column.
EAV Plugin uses this same implementation, but allows you to merge these virtual attributes with the entity, so the attributes become properties of the entity object.
Installation
You can install EAV plugin into your CakePHP project using Composer:
$ composer require quickapps-plugins/eav:"*"
Plugin Loading
Then edit your project bootstrap.php
file and make sure EAV plugin is being
loaded correctly:
Plugin::load('Eav');
Check Cake's documentation for further information.
Import DB Schemas
Lastly use the provided SQL script /config/eav-mysql.sql
and import it into your
project's database (MySQL only for the moment), this will create two tables that are
used internally by EAV plugin to store and define virtual properties.
Usage
Once EAV plugin has been loaded into your project and all MySQL tables has been
created your are ready to go. To start using the EAV API you must attach the
Eav.Eav
behavior to the table you wish to "extend" (add virtual columns to it),
for example:
use Cake\ORM\Table; class UsersTable extends Table { public function initialize(Table $table) { $this->addBehavior('Eav.Eav'); } }
Defining Attributes
Once EAV behavior is attached to your table, you can now start defining virtual columns. There are two ways of defining virtual columns, CLI based or php-script based. We'll explain how to define such columns using both methods.
Using EAV CLI (Recommended)
EAV plugin provides a simple management command-line-interface (CLI) which allows you to easily add or drop virtual columns.
You need to tell which table is being altered, what action you wish to perform (add new virtual column, or drop existing one). And if you are adding new column you must provide column information (column name, data type, etc). Below an example on how to add new virtual column named user_age:
user@name:/path/to/bin/$ cake Eav.table schema --use UsersPlugin.UsersTable --action add --name user_age --type integer --searchable
The searchable
indicates that this virtual column can be in WHERE
clauses.
If you want to drop an existing column:
user@name:/path/to/bin/$ cake Eav.table schema --use UsersPlugin.UsersTable --action drop --name user_age
Check EAV CLI help for more options available.
Using PHP Script
Warning
You should do this step just once, otherwise you will end unnecessary updating columns every time the script is executed.
You can create new virtual columns definitions using the addColumn()
method of
your table, this method will update column information if already exists:
use Cake\ORM\Table; class UsersTable extends Table { public function initialize(Table $table) { $this->addBehavior('Eav.Eav'); // WARNING: just run once these two lines $this->addColumn('user-age', ['type' => 'integer']); $this->addColumn('user-address', ['type' => 'string', 'bundle' => 'admin']); } }
The first argument is the name of the column your are defining, you must use lower
case letters, numbers or "-" symbol. For instance, user-age
is a valid column
name but user_age
or User-Age
are not.
And second argument is used to define column's metadata and supports the following keys:
- type (string): Type of data for that attribute, note that using any other type not
listed here will throw an exception. Supported values are:
- biginteger
- binary
- date
- float
- decimal
- integer
- time
- datetime
- timestamp
- uuid
- string
- text
- boolean
- bundle (string): Indicates the attribute belongs to a bundle name within the table, check the "Bundles" section for further information. Defaults to null (no bundle).
- searchable (bool): Whether this attribute can be used in SQL's "WHERE" clauses. Defaults to true
Dropping Virtual Columns
You can also drop existing virtual columns previously defined using addColumn()
,
to do this you can use the dropColumn()
method:
use Cake\ORM\Table; class UsersTable extends Table { public function initialize(Table $table) { $this->addBehavior('Eav.Eav'); $this->dropColumn('user-age'); $this->dropColumn('user-address', 'admin'); } }
Optionally the second argument can be used to indicate the bundle where the column can be found.
Warning
This method will remove any stored information associated to the column being dropped, so use with extreme caution.
Fetching Entities
After behavior is attached to your table and some virtual columns are defined, you can start fetching entities from your table as usual, using "Table::find()" or similar; every Entity fetched in this way will have additional attributes as they were conventional table columns. For example in any controller:
$user = $this->Users->get(1); debug($user) [ // ... 'properties' => [ 'id' => 1, // real table column 'name' => 'John', // real table column 'user-age' => 15 // EAV attribute 'user-phone' => '+34 256 896 200' // EAV attribute ] ]
You can use your EAV attributes as usual; you can apply validation rules, use them in your WHERE clauses, create form inputs, save entities, etc:
$adults = $this->Users ->find() ->where(['Users.user-age >' => 18]) ->all();
Note
EAV API has some limitation, for instance you cannot use virtual attributes in ORDER BY clauses, GROUP BY, HAVING or any aggregation function.
Bundles
Bundles are sub-sets of attributes within the same table. For example, we could have "articles pages", "plain pages", etc; all of them are Page entities but they might have different attributes depending to which bundle they belongs to:
$this->addColumn('article-body', ['type' => 'text', 'bundle' => 'article']); $this->addColumn('page-body', ['type' => 'text', 'bundle' => 'page']);
We have defined two different columns for two different bundles, article
and
page
, now we can find Page Entities and fetch attributes only of certain
bundle
:
$firstArticle = $this->Pages ->find('all', ['bundle' => 'article']) ->limit(1) ->first(); $firstPage = $this->Pages ->find('all', ['bundle' => 'page']) ->limit(1) ->first(); debug($firstArticle); // Produces: [ // ... 'properties' => [ 'id' => 1, 'article-body' => 'Lorem ipsum dolor sit amet ...', ] ] debug($firstPage); // Produces: [ // ... 'properties' => [ 'id' => 5, 'page-body' => 'Nulla consequat massa quis enim. Donec pede.', ] ]
If no bundle
option is given when retrieving entities EAV behavior will fetch
all attributes regardless of the bundle they belong to:
$firstPage = $this->Pages ->find() ->limit(1) ->first(); debug($firstPage); // Produces: [ // ... 'properties' => [ 'id' => 5, 'article-body' => 'Lorem ipsum dolor sit amet ...', 'page-body' => null ] ]
Warning
Please be aware that using the bundle
option you are telling EAV behavior to
fetch only attributes within that bundle, this may produce column not found
SQL errors when using incorrectly:
$this->Pages ->find('all', ['bundle' => 'page']) ->where(['article-body LIKE' => '%massa quis enim%']) ->limit(1) ->first();
As article-body
attribute exists only on article
bundle you will get an
SQL error as described before.
EAV Cache
In some cases when fetching to many entities per query EAV may become slow, as for
every entity being fetched EAV plugin needs to retrieve all virtual columns related
to that entity, that is, for every collection of entities an additional
SELECT
query is performed. In order to improve this, EAV allows to cache virtual
values of every entity as a serialized structure under a real column of your
entities. To do so, you must indicate the name of the column where EAV values will
be cached using the cache
option, for example:
Cache all virtual values under the eav_cache
column:
$this->addBehavior('Eav.Eav', ['cache' => 'eav_cache']);
Cache custom sets of virtual values under different columns:
$this->addBehavior('Eav.Eav', [ 'cache' => [ 'contact_info' => ['user-name', 'user-address'], 'eav_all' => '*', ], ]);
Accesing cached values
After cache has been enabled, you can access cached EAV values as follow:
// controller use App\AppController; class UsersController extends AppController { public function index() { // load the model and fetch ALL USERS AT ONCE. $this->loadModel('Users'); $users = $this->Users->find('all', ['eav' => true]) $this->set('users', $users); } } // view foreach ($users as $user) { // physical column `name` $name = $user->get('name'); // virtual columns read from cache, read as follow: // $user->get(<cache_column_name>)->get(<virtual_column_name>); $age = $user->get('eav_cache')->get('user-age'); echo sprintf('%s is %s years old', $name, $age); }
Limitations
Caches are automatically updated after every entity update. However, cache may become out of sync under certain circumstances. In some cases, you will be able to see cached values for virtual columns that was previously removed/modified if the entity has not been updated/synced yet.
Updating EAV-cache of every entity after virtual columns are changed is a really expensive task, that is why EAV plugin will not perform this task automatically.
To summarize, you must be aware of the following cases:
- After dropping a virtual column.
- After adding new virtual columns.
- After virtual column's definition is changed (type of value, etc).
Note
You can use the updateEavCache()
method of your table to update EAV cache
for a single entity:
$this->loadModel('Users'); $user = $this->Users->get($id), $this->Users->updateEavCache($entity);