Models and Migrations

Apex provides a clean, straight forward models implementation. In essense, you write the database schema in SQL, and Apex will generate the model classes for you with constructor property promotion while taking into account column type, nullable, default value, and foreign key constraints.

If you're not familiar with writing SQL, please check out the How to Write Proper, Well Structured mySQL Database Schemas guide for a quick primer. Don't worry, SQL is quite easy, and it's highly recommended you write your database schemas in SQL as a well structured database schema is an integral part of any quality software system.

Please note, if you are against writing SQL code, Apex does have full support for both Eloquent and Doctrine models and migrations. Please check the Database section of the documentation for details.

Initial Migration

If you don't already have a package to use for development, create one with the command:

./apex package create demo

Every package includes an initial database migration that is executed during installation. If you open the file at ~/etc/Demo/migrate.php you will see within the install() method there is only one line that executes all SQL code within the install.sql file.

Open the file located at ~/etc/Demo/install.sql and enter the following contents:

CREATE TABLE demo_categories (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    slug VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE demo_products (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    category_id INT NOT NULL,
    in_stock INT NOT NULL DEFAULT 0,
    price DECIMAL(8,2) NOT NULL DEFAULT 0,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES demo_categories (id) ON DELETE CASCADE
);

CREATE TABLE demo_keywords (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    keyword VARCHAR(100) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES demo_products (id) ON DELETE CASCADE
);

Now execute the SQL code against the database with the command:

./apex migration install demo

Alternatively, you may connect directly to the database and copy and paste the code by running the sql command without any arguments, such as:

./apex sql

This will bring up the database's prompt and allow you to execute SQL statements directly on the database.

Generate Models

Next, generate the model classes with the command:

./apex opus model Demo/Models/Category --dbtable demo_categories --magic

This will ask you to confirm creation of the parent directory, then proceed to detect the two foreign key constraints and ask if you would like model classes generated for each. Confirm generation of the two additional model classes, and when prompted for the filepath of each press enter to accept the default value.

Upon completion, you will have three new PHP classes located in the ~/src/Demo/Models directory. Open the file at ~/src/Demo/Models/Product.php and you will see it's a simple PHP class that utilizes constructor property promotion with one property for each column within the database table. The appropriate PHP type has been assigned to each property, along with taking default values and whether or not the column allows null values into consideration. You will also notice that it generated two methods named getCategory() and getKeywords() for the foreign key constraints.

Since the --magic flag was included in the command, no standard get / set methods were generated, and instead you may access the properties directly at $obj->property_name. If you would prefer not to use magic in this manner, simply generate the model classes without the --magic flag, and the standard get / set methods will also be generated for each property.

Inserting Records

Now that you've generated a couple model classes, let's add some records to them using the static insert() method. Open a blank file within the Apex installation directory, and enter the following contents:

<?php

use Apex\App\App;
use App\Demo\Models\{Category, Product};

// Load Apex
require_once('./vendor/autoload.php');
$app = new App();

// Create category
$cat = Category::insert([
    'slug' => 'service',
    'name' => 'Services'
]);

// Display category info
print_r($cat->toArray());

// Add product
$product = Product::insert([
    'category_id' => $cat->id,
    'price' => 34.95,
    'name' => 'House Cleaning Service',
    'description' => 'Make your house shine!'
]);

// View product
print_r($product->toArray());

Run the script, and the two arrays of the new objects will be displayed. The ModelName::insert() method accepts a single associative array defining the values of the new record to create, and returns an instance of the newly created record.

Insert Multiple Records

You may also insert multiple records at once by passing an array of associative arrays to the static insert() method, for example:

[data-line=5]
use App\Demo\Models\Category;

[data-line=30]
Category::insert([
    ['slug' => 'furniture', 'name' => 'Furniture'],
    ['slug' => 'electronics', 'name' => 'Electronics'],
    ['slug' => 'kitchen', 'name' => 'Kitchenware']
]);

Insert or Update

If you are uncertain whether or not a record needs to be inserted or updated, you may use the static insertOrUpdate() method, for example:

[data-line=6]
use App\Demo\Models\DemoCategory;

[data-line=40]
DemoCategory::insertOrUpdate(
    ['slug' => 'services'],
    ['name' => 'Services']
);

DemoCategory::insertOrUpdate(
    ['slug' => 'services'],
    ['name' => 'Personal Services']
);

The above code will result in one category with the slug of "services" and name of "Personal Services". This method takes two associative arrays, the first being the criteria to search for and the second being the values to update the record with. If a record exists that matches the criteria, it will be updated with the values within the second array. Otherwise, a new record will be created using the values of both arrays combined.

Selecting Records

With model classes generated and records inserted, let's go through how to retrieve them from the database. Open a blank file within the Apex installation directory, and enter the following contents:

<?php

use Apex\App\App;
use App\Demo\Models\{Category, Product};

// Load Apex
require_once('./vendor/autoload.php');
$app = new App();

// Get category
$slug = 'service';
if (!$cat = DemoCategory::whereFirst('slug = %s', $slug)) { 
    die("No category with slug '$slug'");
}
echo "Found Category: " . $cat->name . "\n";

// Get same category, by id#
$category_id = $cat->id;
$cat = DemoCategory::whereId($category_id);

// Get all products within category, sorted by name
$products = $cat->getProducts('name');
foreach ($products as $product) { 
    echo "Product: " . $product->name . ' - ' . $product->price . "\n";
}

// Get all active products with price greater than $20
$is_active = true;
$price = 20;
$products = Product::where('is_active = %b AND price > %d', $is_active, $price);
foreach ($products as $product) { 
    echo "Product: " . $product->name . ' - ' . $product->price . "\n";
}

// Get all categories, sorted by name
$categories = Category::all('name');
foreach ($categories as $cat) { 
    print_r($cat->toArray());
}

Although a bit of a handful, execute the above code for an example of how to retrieve records. The below sections go through everything in the above code in detail.

Select Multiple Records

You may select records using the static where() method, for example:

[data-line=7]
use App\Demo\Models\Product;

[data-line=25]
// Get all active products with price greater than 24.95
$is_active = true;
$price = 24.95;
$products = Product::where('is_active = %b AND price > %d', $is_active, $price);
foreach ($products as $product) { 
    echo "Found Product: " . $product->name . ' - ' . $product->price . "\n";
}

The first argument is the where clause within the SQL statement, and all other arguments are the values of any placeholders. For information on placeholders, please visit the Database Placeholders page of the documentation. This will return an iterator of all model instances that match the criteria.

Within the first argument you may also add any desired order by, limit, offset, et al statements. For example:

[data-line=7]
use App\Demo\Models\Product;

[data-line=26]
$price = 24.95;
$products = Product::where('price > %d ORDER BY name LIMIT 25 OFFSET50', $price);

Select First Record

Many times you will only need the first matching record, which can be done with the static whereFirst() method, for example:

[data-line=8]
use App\Demo\Models\Category;

[data-line=36]
$slug = 'service';
if (!$cat = Category::whereFirst('slug = %s', $slug)) { 
    die("No category found");
}

print_r($cat->toArray());

Same as the where() method, except instead of returning an iterator of all matching records, it will only return a model instance of the first record found.

Select Record by ID

You may also select a record by its unique ID# with the static whereId() method, for example:

[data-line=7]
use App\Demo\Models\Product;

[data-line=47]
$product_id = 51;
if (!$product = Product::whereId($product_id)) { 
    die("No product found");
}

print_r($product->toArray());

This will select the individual record based on the primary key column of the database table, and return an instance of the model class or null if no record is found.

Select All Records

You may retrieve all records within the database table with the static all() method, for example:

[data-line=8]
use App\Demo\Models\Category;

[data-line=55]
$limit = 10;
$offset = 20;

// Get categories 10 - 25 ordered by name in descending order
$cats = Category::all('name', 'desc', $limit, $offset);
foreach ($cats as $category) { 
    print_r($category->toArray());
}

The all() method accepts four optional parameters, the column to sort by, the direction to sort, plus a limit and offset. For full details, please visit the BaseModel::all() page of the documentation.

Updating Records

You may update multiple records at once with the static update() method,such as:

<?php

use Apex\App\App;
use App\Demo\Models\Product;

// Deactivate all products less than $19.95
$price = 19.95;
Product::update(
    ['is_active' => false],
    'price < %d',
    $price
);

The first parameter to the update() method is an associative array of values to update the records with. The second paramater is the same as the static where() method is is the where clause of the SQL statement with placeholders, while all other arguments to the method are the values of the placeholders.

Update Single Record

You can update a single record by calling the save() method on the model instance, for example:

use App\Demo\Models\Product;

$product_id = 2;
if (!$product = Product::whereId($product_id)) {
    die("No product found");
}

// Update by passing an array to save()
$product->save([
    'price' => 49.95,
    'name' => 'Update Test'
]);

// Alternatively, set the properties as desired and call save() with no arguments
$product->price = 94.99;
$product->name = 'Testing Again';
$product->save();

Both methods work the same, and it's simply a matter of personal preference.

Deleting Records

You may delete multiple records at once by using the status deleteMany() method, such as:

<?php

use Apex\App\App;
use App\Demo\Models\Product;

// Delete all products less than $19.95
$price = 19.95;
$num = Product::deleteMany('price < %d', $price);
echo "Deleted $num products\n";

Delete Individual Record

You may delete an individual record by calling the delete() method on the model instance, for example:

[data-line=7]
use App\Demo\Models\Product;

[data-line=61]
$product_id = 2;
if (!$product = Product::whereId($product_id)) {
    die("No product found");
}

// Delete product
$product->delete();

Purge All Records

You may also delete all records of a database table by calling the static purge() method on the model class, for example:

use App\Demo\Models\Product;

// Delete all products
Product::purge();

toArray

You may also retrieve all properties of the model as an array by calling the toArray() method on a model instance, such as:

<?php

use Apex\App\App;
use App\Demo\Models\Product;

// Boot Apex
require_once('./vendor/autoload.php');
$app = new App();

// Get product
$product_id = 3;
if (!$product = Product::whereId($product_id)) {
    die("No product found");
}

// Get all properties as an array
$props = $product->toArray();
print_r($pops);

Database Object to Perform SQL Statements

Instead of using the methods above, sometimes you may wish to execute SQL statements directly against the database. This can be done by injecting the Apex\Svc\Db service into the desired class. For example, create a new HTTP controller with the command:

./apex create http-controller demo demo-products --route products