Database Model Usage (Laravel Style)

The webman model is based on Eloquent ORM. Each database table has a corresponding "model" used to interact with that table. You can use the model to query the data in the table and insert new records into the table.

Before starting, please ensure that the database connection is configured in config/database.php.

Note: Eloquent ORM requires additional import composer require "illuminate/events" to support model observers. Example

Database Model Example

<?php
namespace app\model;

use support\Model;

class User extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'user';

    /**
     * The primary key associated with the table.
     *
     * @var string
     */
    protected $primaryKey = 'uid';

    /**
     * Indicates if the model should be timestamped.
     *
     * @var bool
     */
    public $timestamps = false;
}

Table Name

You can specify a custom data table by defining the table property on the model:

class User extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'user';
}

Primary Key

Eloquent assumes that each table has a primary key column named id. You can define a protected $primaryKey property to override this convention.

class User extends Model
{
    /**
     * The primary key associated with the table.
     *
     * @var string
     */
    protected $primaryKey = 'uid';
}

Eloquent assumes the primary key is an auto-incrementing integer, which means by default the primary key is automatically cast to an int. If you want to use a non-incrementing or non-numeric primary key, you need to set the public $incrementing property to false.

class User extends Model
{
    /**
     * Indicates whether the model's primary key is auto-incrementing.
     *
     * @var bool
     */
    public $incrementing = false;
}

If your primary key is not an integer, you need to set the protected $keyType property on the model to string:

class User extends Model
{
    /**
     * The "type" of the auto-incrementing ID.
     *
     * @var string
     */
    protected $keyType = 'string';
}

Timestamps

By default, Eloquent expects created_at and updated_at columns to exist in your tables. If you do not want Eloquent to manage these columns automatically, set the $timestamps property on the model to false:

class User extends Model
{
    /**
     * Indicates if the model should be timestamped.
     *
     * @var bool
     */
    public $timestamps = false;
}

If you need to customize the format of the timestamps, set the $dateFormat property in your model. This property determines how date attributes are stored in the database, as well as how the model is serialized to arrays or JSON:

class User extends Model
{
    /**
     * The format of the timestamp storage.
     *
     * @var string
     */
    protected $dateFormat = 'U';
}

If you need to customize the names of the timestamp fields, you can set the values of the CREATED_AT and UPDATED_AT constants in the model:

class User extends Model
{
    const CREATED_AT = 'creation_date';
    const UPDATED_AT = 'last_update';
}

Database Connection

By default, Eloquent models will use the default database connection configured for your application. If you want to specify a different connection for the model, set the $connection property:

class User extends Model
{
    /**
     * The connection name for the model.
     *
     * @var string
     */
    protected $connection = 'connection-name';
}

Default Attribute Values

To define default values for certain attributes of a model, you can define the $attributes property on the model:

class User extends Model
{
    /**
     * The default attribute values for the model.
     *
     * @var array
     */
    protected $attributes = [
        'delayed' => false,
    ];
}

Model Retrieval

After creating the model and its associated database table, you can now query data from the database. Think of each Eloquent model as a powerful query builder, which allows you to quickly query the associated data table. For example:

$users = app\model\User::all();

foreach ($users as $user) {
    echo $user->name;
}

Tip: Since Eloquent models are also query builders, you should also read about Query Builder for all available methods. You can use these methods in Eloquent queries.

Additional Constraints

The all method of Eloquent will return all results from the model. Since each Eloquent model also acts as a query builder, you can add constraints and then use the get method to get the results:

$users = app\model\User::where('name', 'like', '%tom')
               ->orderBy('uid', 'desc')
               ->limit(10)
               ->get();

Reloading Models

You can use the fresh and refresh methods to reload a model. The fresh method retrieves the model from the database again. The existing model instance is not affected:

$user = app\model\User::where('name', 'tom')->first();

$fresh_user = $user->fresh();

The refresh method re-assigns the existing model with fresh data from the database. Also, the loaded relationships will be reloaded:

$user = app\model\User::where('name', 'tom')->first();

$user->name = 'jerry';

$user = $user->fresh();

$user->name; // "tom"

Collections

The all and get methods of Eloquent can return multiple results, returning an instance of Illuminate\Database\Eloquent\Collection. The Collection class provides many helper functions to work with Eloquent results:

$users = $users->reject(function ($user) {
    return $user->disabled;
});

Using Cursors

The cursor method allows you to iterate over the database using a cursor, executing the query only once. When dealing with large amounts of data, cursor can greatly reduce memory usage:

foreach (app\model\User::where('sex', 1)->cursor() as $user) {
    //
}

The cursor returns an instance of Illuminate\Support\LazyCollection. Lazy collections allow you to use most collection methods from Laravel Collections while only loading one model into memory at a time:

$users = app\model\User::cursor()->filter(function ($user) {
    return $user->id > 500;
});

foreach ($users as $user) {
    echo $user->id;
}

Selects Subqueries

Eloquent provides advanced subquery support, allowing you to extract information from related tables with a single query. For example, suppose we have a destinations table and a flights table. The flights table contains an arrived_at field representing when the flight arrives at the destination.

Using the select and addSelect methods offered by the subquery feature, we can query all destinations and the names of the last flights arriving at each destination in a single statement:

use app\model\Destination;
use app\model\Flight;

return Destination::addSelect(['last_flight' => Flight::select('name')
    ->whereColumn('destination_id', 'destinations.id')
    ->orderBy('arrived_at', 'desc')
    ->limit(1)
])->get();

Sorting by Subquery

Additionally, the orderBy function of the query builder also supports subqueries. We can use this feature to sort all destinations by the arrival time of the last flight to each destination. Similarly, this can execute a single query on the database:

return Destination::orderByDesc(
    Flight::select('arrived_at')
        ->whereColumn('destination_id', 'destinations.id')
        ->orderBy('arrived_at', 'desc')
        ->limit(1)
)->get();

Retrieve Single Model / Collection

In addition to retrieving all records from a specified table, you can use the find, first or firstWhere methods to retrieve a single record. These methods return a single model instance instead of a model collection:

// Find a model by its primary key...
$flight = app\model\Flight::find(1);

// Find the first model matching the query...
$flight = app\model\Flight::where('active', 1)->first();

// A quick implementation to find the first model matching the query...
$flight = app\model\Flight::firstWhere('active', 1);

You may also call the find method with an array of primary keys, which will return a collection of matching records:

$flights = app\model\Flight::find([1, 2, 3]);

Sometimes, you might want to execute other actions when searching for the first result but getting no value. The firstOr method will return the first result if found; if not, it will execute the given callback. The return value of the callback will be the return value of the firstOr method:

$model = app\model\Flight::where('legs', '>', 100)->firstOr(function () {
        // ...
});

The firstOr method also accepts an array of fields for querying:

$model = app\model\Flight::where('legs', '>', 100)
            ->firstOr(['id', 'legs'], function () {
                // ...
            });

"Not Found" Exception

Sometimes, you may want to throw an exception when a model is not found. This is especially useful in controllers and routes. The findOrFail and firstOrFail methods will retrieve the first result of a query, throwing an Illuminate\Database\Eloquent\ModelNotFoundException if not found:

$model = app\model\Flight::findOrFail(1);
$model = app\model\Flight::where('legs', '>', 100)->firstOrFail();

Retrieving Collections

You can also use methods like count, sum, and max provided by the query builder, and other collection functions to operate on collections. These methods only return appropriate scalar values instead of model instances:

$count = app\model\Flight::where('active', 1)->count();

$max = app\model\Flight::where('active', 1)->max('price');

Inserting

To add a new record to the database, first create a new model instance, set the attributes for the instance, and then call the save method:

<?php

namespace app\controller;

use app\model\User;
use support\Request;
use support\Response;

class FooController
{
    /**
     * Add a new record in the user table
     *
     * @param  Request  $request
     * @return Response
     */
    public function store(Request $request)
    {
        // Validate the request

        $user = new User;

        $user->name = $request->get('name');

        $user->save();
    }
}

The created_at and updated_at timestamps will be automatically set (when the $timestamps property in the model is true), and no manual assignment is needed.

Updating

The save method can also be used to update an existing model in the database. To update a model, you need to retrieve it first, set the attributes to update, and then call the save method. Similarly, the updated_at timestamp will be automatically updated, so manual assignment is not required:

$user = app\model\User::find(1);
$user->name = 'jerry';
$user->save();

Bulk Update

app\model\User::where('uid', '>', 10)
          ->update(['name' => 'tom']);

Check Attribute Changes

Eloquent provides the isDirty, isClean, and wasChanged methods to check the internal state of the model and determine how its attributes have changed since they were initially loaded.
The isDirty method determines if any attributes have changed since the model was loaded. You can pass a specific attribute name to check if that specific attribute is dirty. The isClean method is the opposite of isDirty and also accepts an optional attribute parameter:

$user = User::create([
    'first_name' => 'Taylor',
    'last_name' => 'Otwell',
    'title' => 'Developer',
]);

$user->title = 'Painter';

$user->isDirty(); // true
$user->isDirty('title'); // true
$user->isDirty('first_name'); // false

$user->isClean(); // false
$user->isClean('title'); // false
$user->isClean('first_name'); // true

$user->save();

$user->isDirty(); // false
$user->isClean(); // true

The wasChanged method determines if any properties were changed during the last save of the model within the current request cycle. You can also pass an attribute name to see if that specific attribute was changed:

$user = User::create([
    'first_name' => 'Taylor',
    'last_name' => 'Otwell',
    'title' => 'Developer',
]);

$user->title = 'Painter';
$user->save();

$user->wasChanged(); // true
$user->wasChanged('title'); // true
$user->wasChanged('first_name'); // false

Mass Assignment

You can also use the create method to save a new model. This method will return a model instance. However, before using it, you need to specify the fillable or guarded properties on the model, as all Eloquent models are not mass assignable by default.

Mass assignment vulnerabilities occur when a user passes unexpected HTTP parameters through a request and those parameters alter fields in the database that should not be changed. For example, a malicious user might pass the is_admin parameter via an HTTP request and pass it to the create method, allowing the user to escalate themselves to an admin.

Therefore, you should define which attributes on the model can be mass assigned beforehand. You can achieve this by using the $fillable property on the model. For example, to allow the name attribute of the Flight model to be mass assignable:

<?php

namespace app\model;

use support\Model;

class Flight extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['name'];
}

Once we have set the attributes that can be mass assigned, we can insert new data into the database using the create method. The create method will return the saved model instance:

$flight = app\model\Flight::create(['name' => 'Flight 10']);

If you already have an instance of the model, you can pass an array to the fill method to assign values:

$flight->fill(['name' => 'Flight 22']);

The $fillable can be considered a "whitelist" for mass assignment, and you can also use the $guarded property to achieve the same effect. The $guarded property contains the array of attributes that are not allowed for mass assignment. In other words, $guarded functions more like a "blacklist". Note: You can only use either $fillable or $guarded, but not both at the same time. In the following example, all attributes except the price attribute can be mass assigned:

<?php

namespace app\model;

use support\Model;

class Flight extends Model
{
    /**
     * The attributes that are not mass assignable.
     *
     * @var array
     */
    protected $guarded = ['price'];
}

If you want all attributes to be mass assignable, you can define $guarded as an empty array:

/**
 * The attributes that are not mass assignable.
 *
 * @var array
 */
protected $guarded = [];

Other Creation Methods

firstOrCreate/firstOrNew
Here are two methods you might use for mass assignment: firstOrCreate and firstOrNew. The firstOrCreate method will match data in the database by the given key/value pair. If no model is found in the database, a new record will be inserted, containing properties from the first parameter along with properties from the optional second parameter.

The firstOrNew method behaves similarly to firstOrCreate, but if the firstOrNew method does not find a corresponding model, it will return a new model instance. Note that the model instance returned by firstOrNew has not yet been saved to the database; you need to call the save method manually to save it:

// Retrieve the flight by name, creating it if it does not exist...
$flight = app\model\Flight::firstOrCreate(['name' => 'Flight 10']);

// Retrieve the flight by name, or create it with the name, delayed attribute, and arrival_time attribute...
$flight = app\model\Flight::firstOrCreate(
    ['name' => 'Flight 10'],
    ['delayed' => 1, 'arrival_time' => '11:30']
);

// Retrieve the flight by name, creating an instance if it does not exist...
$flight = app\model\Flight::firstOrNew(['name' => 'Flight 10']);

// Retrieve the flight by name, or create a model instance using name, delayed attribute, and arrival_time attribute...
$flight = app\model\Flight::firstOrNew(
    ['name' => 'Flight 10'],
    ['delayed' => 1, 'arrival_time' => '11:30']
);

You may also encounter situations where you want to update an existing model or create a new one if it does not exist. The updateOrCreate method can accomplish this in one step. Similar to firstOrCreate, updateOrCreate persists the model, so there is no need to call save():

// Set the price to $99 if there is a flight from Oakland to San Diego.
// If no matching model is found, one will be created.
$flight = app\model\Flight::updateOrCreate(
    ['departure' => 'Oakland', 'destination' => 'San Diego'],
    ['price' => 99, 'discounted' => 1]
);

Deleting Models

You can call the delete method on a model instance to remove it:

$flight = app\model\Flight::find(1);
$flight->delete();

Deleting Models by Primary Key

app\model\Flight::destroy(1);

app\model\Flight::destroy(1, 2, 3);

app\model\Flight::destroy([1, 2, 3]);

app\model\Flight::destroy(collect([1, 2, 3]));

Deleting Models by Query

$deletedRows = app\model\Flight::where('active', 0)->delete();

Copying Models

You can use the replicate method to copy a new instance that has not been saved to the database. This method is very useful when model instances share many similar properties.

$shipping = App\Address::create([
    'type' => 'shipping',
    'line_1' => '123 Example Street',
    'city' => 'Victorville',
    'state' => 'CA',
    'postcode' => '90001',
]);

$billing = $shipping->replicate()->fill([
    'type' => 'billing'
]);

$billing->save();

Comparing Models

Sometimes, you may need to determine if two models are "the same". The is method can be used to quickly check if two models have the same primary key, table, and database connection:

if ($post->is($anotherPost)) {
    //
}

Model Observers

Refer to Model Events and Observer in Laravel

Note: Eloquent ORM requires additional import composer require "illuminate/events" to support model observers.

<?php
namespace app\model;

use support\Model;
use app\observer\UserObserver;

class User extends Model
{
    public static function boot()
    {
        parent::boot();
        static::observe(UserObserver::class);
    }
}

Transactions

See Database Transactions