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);
}
}