Overview

Description

Caldera ORM includes a Query builder and an ORM layer to make it easy to interact with databases.

As with the other Caldera components it has been built to be swappable and modular.

Installation

The easisest way to install it is to use Composer:

composer require vecode/caldera-orm

Requires

  • php: >=8.1
  • ext-mbstring: *
  • vecode/caldera-database: ^1.0

Basic usage

Getting started

You will need a working database connection (please refer to the vecode/caldera-database documentation for more details) in order to pass it to the Query constructor:

use Caldera\Database\Query\Query;

$database = new Database($adapter); // Where $adapter is a valid AdapterInterface implementation
$query = new Query($database);

It is strongly recommended that you also set the working database object on the QueryFactory, as it will be required for some queries:

use Caldera\Database\Query\QueryFactory;

QueryFactory::setDatabase($database);
Selecting rows

Row selection is easy, just set the table, the conditions and run the query with all to retrieve all the matching rows:

$rows = $query->table('posts')
    ->where('status', 'Published')
    ->all();

If you only want a single row use first:

$row = $query->table('posts')
    ->where('status', 'Published')
    ->first();

You can specify other operator than = too:

$rows = $query->table('posts')
    ->where('status', 'Trash', '!=')
    ->all();

To order the results use the order method, passing the column and either ASC or DESC:

$rows = $query->table('posts')
    ->where('status', 'Trash', '!=')
    ->order('title', 'ASC')
    ->all();

You may also limit the results with the limit method and by passing the limit and offset parameters:

$rows = $query->table('posts')
    ->where('status', 'Trash', '!=')
    ->order('title', 'ASC')
    ->limit(15, 0)
    ->all();

There also a pagination helper, the paginate method which takes the page number and items per page:

$rows = $query->table('posts')
    ->where('status', 'Trash', '!=')
    ->order('title', 'ASC')
    ->page(1, 15)
    ->all();

Grouping is also supported through the group method and its syntax is similar to the where one:

$rows = $query->table('posts')
    ->where('status', 'Trash', '!=')
    ->group('author', 'ASC')
    ->all();
Where, Having

The where and having methods accept up to four parameters to set conditions:

  • Column
  • Value
  • Comparison operator
  • Binary operator
$rows = $query->table('posts')
    ->where('status', 'Published', '=', 'AND')
    ->all();

For more complex conditions you may group them by using a Closure:

$rows = $query->table('posts')
    ->where('status', 'Published')
    ->where(function($query) {
        $query->where('created', '2021-01-01', '>');
        $query->where('created', '2021-01-03', '<');
    })
    ->all();

To check within an array of possible values you can use the whereIn and whereNotIn methods:

$rows = $query->table('posts')
    ->whereIn('id', [154, 11, 92, 63])
    ->all();
$rows = $query->table('posts')
    ->whereNotIn('id', [154, 11, 92, 63])
    ->all();
Unions and joins

Union and Join operations are also supported by some adapters.

For example, for a simple union just create the first query and use the union method on the second one:

$other = $query->table('office')
    ->column('city');
$rows = $query->table('warehouse')
    ->column('city')
    ->union($other)
    ->all();

Joins are easier, just specify the table and which columns:

$rows = $query->table('user', 'u')
    ->join('user_meta', 'id_test', 'u.id')
    ->where('u.id', 1)
    ->all();

For more complex conditions you may also use a Closure, like with the where method:

use Aurora\Database\Database;
use Aurora\Database\Query\Argument;

$rows = $query->table('user', 'u')
    ->join(Argument::table('user_meta', 'um'), function($query) {
        $query->where(Argument::column('um.id_user'), Argument::column('u.id'));
        $query->where(Argument::column('um.name'), 'first_name');
    })
    ->all();

Notice how we use Argument::table and Argument::column in this example. This is required to avoid the automatic back-ticking of these parameters, you can use also Argument::method for the built-in methods of your DBMS, for example Argument::method('NOW') on MySQL to use the NOW() function.

Aggregates

Aggregate functions are also supported, you can get the min, max, avg, sum and count of any table with the corresponding method:

$max = $query->table('order')->max('price');

$min = $query->table('order')->min('price');

$sum = $query->table('order')->sum('price');

$avg = $query->table('order')->avg('price');

$count = $query->table('order')->count();
Select chunked rows

As with the base Database object you can get chunked results easily:

$query->table('order')->chunk(10, function($rows) {
    // Do something with $rows
});
Inserting rows

To insert rows you will need to pass an array with the desired field values to the insert method:

$query->table('user')->insert([
    'id' => 0,
    'name' => 'Test user',
    'created' => Argument::method('NOW'),
    'modified' => Argument::method('NOW')
]);

You can also insert multiple rows by passing an array with the data for each row:

$query->table('user')->insert([
    [
        'id' => 0,
        'name' => 'Test user',
        'created' => Argument::method('NOW'),
        'modified' => Argument::method('NOW')
    ], [
        'id' => 0,
        'name' => 'Another user',
        'created' => Argument::method('NOW'),
        'modified' => Argument::method('NOW')
    ]
]);
Updates

Updating rows is straightforward, just set the conditions with where calls and then use the update method specifying which columns to update and its new values:

$query->table('user')
    ->where('id', 10, '<')
    ->update(['modified' => Argument::method('NOW')]);
Upserts

Upserts are a convenience utility, they try to create a new row and if it already exists then just update some fields.

Using them is easy, you just need to call the upsert method, passing the fields as you will for an insert call, then the fields you'd want to update if the row exists and finally the columns which determine if the row is unique or not:

$query->table('user')->upsert(
    ['id' => 1, 'name' => 'Test user', 'created' => Argument::method('NOW'), 'modified' => Argument::method('NOW')],
    ['modified' => Argument::method('NOW')],
    ['id']
);

Please note that the third parameter is optional, this is because MySQL uses the UNIQUE and PRIMARY KEY constraints of each table and thus it is not required.

Deleting rows

To delete a row just set the conditions and call delete:

$query->table('test')->where('id', 10, '<')->delete();
Truncating tables

You can also truncate a table by deleting all its rows and, depending on the adapter, resetting the auto-increment counter; to do so, call the truncate method:

$query->table('test')->truncate();

Using models

The Query builder sets the basis for the ORM layer.

With the ORM layer you can create more complex apps by offloading the database logic to models that represent your entities.

To define a model just create a class that extends AbstractModel:

use Caldera\Database\Model\AbstractModel;

class User extends AbstractModel {

}

In the class definition you can specify the following properties:

  • $table - Table name
  • $model - Model name
  • $fields - Model fields
  • $update - Model fields for update
  • $defaults - Default values
  • $field_primary - Primary field name
  • $field_created - Created field name
  • $field_updated - Updated field name

For example, revisiting our User model:

use Caldera\Database\Model\AbstractModel;

class User extends AbstractModel {

    /**
     * Table name
     * @var string
     */
    protected static $table  = 'user';

    /**
     * Model name
     * @var string
     */
    protected static $model  = 'User';

    /**
     * Model fields
     * @var array
     */
    protected static $fields = [
        'id',
        'name',
        'email',
        'status',
        'type',
        'created',
        'modified',
    ];

    /**
     * Model fields for update
     * @var array
     */
    protected static $update = [
        'name',
        'email',
        'status',
        'type',
        'modified',
    ];

    /**
     * Default values
     * @var array
     */
    protected static $defaults = [
        'status' => 'Inactive',
        'type' => 'Subscriber',
    ];
}
Fetching models

Fetching models is really easy, you can for example to get all the users you can do:

$users = User::all();

To get a single user by its ID, use the get method:

$user = User::get(1);

Also the Model wraps a Query object, so you can apply where filters and even sorting/paging for example:

$users = User::where()
    ->where('status', 'Active')
    ->sort('created', 'ASC')
    ->page(3, 15)
    ->all();

Saving and updating models

Creating a model from scratch is straightforward, just create a variable, set its fields and call the save method:

$user = new User();
$user->name = 'Chuck Norris';
$user->email = '[email protected]';
$user->type = 'Subscriber';
$user->save();

Updating is even easier, for example, to change the nicename field you can simply:

$user = User::get(1);
$user->email = '[email protected]';
$user->save();

The save method updates the row on the table if it exists and creates it if it does not, using the upsert method of the Query builder.

Meta models

There is also a special model type included, based on AbstractMetaModel. When you extend your models from that abstract class you gain the ability to store and retrieve metadata for them; just add two extra properties, meta_table and meta_field:

use Caldera\Database\Model\AbstractMetaModel;

class User extends AbstractMetaModel {

    /**
     * Table name
     * @var string
     */
    protected static $table  = 'user';

    /**
     * Model name
     * @var string
     */
    protected static $model  = 'User';

    /**
     * Model fields
     * @var array
     */
    protected static $fields = [
        'id',
        'name',
        'email',
        'status',
        'type',
        'created',
        'modified',
    ];

    /**
     * Model fields for update
     * @var array
     */
    protected static $update = [
        'name',
        'email',
        'status',
        'type',
        'modified',
    ];

    /**
     * Default values
     * @var array
     */
    protected static $defaults = [
        'status' => 'Inactive',
        'type' => 'Subscriber',
    ];

    /**
     * Metadata table name
     * @var string
     */
    protected static $meta_table = 'user_meta';

    /**
     * Metadata foreign key field
     * @var string
     */
    protected static $meta_field = 'id_user';
}

In this case $meta_table identifies the table in which the metadata will be stored and $meta_field the key that connects each metadata item with its corresponding row on the user table; the metadata table should have the following structure:

Table user_meta

  • id - Big integer, not nullable, autoincremental, primary key
  • id_user - Big integer, not nullable and indexed
  • name - String, not nullable and indexed
  • value - Medium text, not nullable

And with a unique constraint that includes both id_user and name, so that the DBMS can detect collisions on items with the same name for the same user.

For example, on MySQL it would be:

CREATE TABLE user_meta (
    id BIGINT NOT NULL PRIMARY KEY,
    id_user BIGINT NOT NULL,
    name VARCHAR(150) NOT NULL,
    value MEDIUMTEXT NOT NULL,
    KEY key_user (id_user),
    UNIQUE KEY uk_user_name (id_user, name),
    PRIMARY KEY pk_id (id)
);

Once created both the model and its table, you can start using it, for example to store some extra information about the User with setMetadata:

$user->setMetadata('first_name', 'John');
$user->setMetadata('last_name', 'Rambo');

To retrieve the metadata just call getMetadata:

$first_name = $user->getMetadata('first_name');
$last_name = $user->getMetadata('last_name');

You can pass a second argument with a default value just in case it doesn't exist:

$address = $user->getMetadata('address', 'No address set yet');

This is a powerful pattern, but beware as it can be used poorly.

Just remember to never store data susceptible to searching, filtering or sorting as metadata: if the field will be used for any of these three operations, put it on the main table, use a pivot table or find a better alternative as using the metadata table for any of the aforementioned operations is bulky, slow and complicated.

Also remember to think of metadata as extra information that may or may not exist for any given record.