Update August 2017: I am currently updating this article with more modern syntax and removal of a large number of now obsolete statements. Generally the code concepts are still valid however, please check back for updates in due course.
This is Part 3 of Developing Applications in Laravel 5. To recap so far:
- In Part 1 I covered developing a simple application that exercised the three essential components of the Laravel eco-system, that being Models, Views and Controllers.
- In Part 2 we looked at Views and we went through the steps of doing a refactor on an old blade template.
In this article we will look at Models, in particular using Laravel's Fluent Query Builder to build a Model to access the database and retrieve data using different queries. But first lets re-cap where a "Model" fits into the big picture.
When a Request reaches our application, the Laravel framework looks up a route table for a matching path. In routes.php we typically define a Path, Controller and Method which allows our code to be called as needed. So for any path, typically a Controller is instanced and a method inside it is called. From this Method, Models are usually invoked, data retrieved and business logic applied which then results in data that needs to be displayed. The Controller returns a View object, passing the data with it which is then rendered back to the user using the Blade Templating engine.
For this article, the key point is when the Controller is invoked, it gets data from one or Models as a result of User input or program Actions. Models usually get their data from a database, sometimes a flat file or other external data source. One of the big mistakes I see in code samples in many tutorials is direct access to a database being called from within a Controller or View rather than via a Model, even worse the data access methods used are typically low level SQL calls. In reviewing these code samples I consider them to fail dramatically as "good" examples; for the following reasons:
- In a large application, Database code cannot be maintained in the long term if its scattered with business logic.
- Changes to the Database engine often introduce incompatibilities that are time consuming to refactor if code is not contained in a Model.
- Poor coding skills and a lack of experience may lead to Database specific code being duplicated, introducing errors.
- Limits enhancements to functionality.
- Long term maintenance of production applications is hampered due to poor Database code design.
In Laravel there are several ways to get access to a database, you can:
- Use the low level interface if you wish (see: Database), I highly suggest you re-think that path if that is what you are use to doing.
- You could go the high level interface called Eloquent, which has some powerful features on offer, or
- You could go middle of the road and use the Fluent Query Builder.
When I initially started Laravel Development, the Eloquent path looked attractive, but I quickly found difficulties with my then limited knowledge to manipulate the data collections returned from simple queries. I investigated the Fluent Query Builder and immediately had results, I also found solutions to all my needs so I began to use it for all my applications, even if you have 100's of tables, Fluent is still a viable solution.
Creating a Model
I find using the Artisan tool is a great way to create a model file, below is the syntax for it. When creating a Model it is often good to also create a Migration file with it, if you recall from the earlier articles in this series, the Migration file is where we define our database table and its columns.
# php artisan help make:model
Usage:
make:model [options] [--] <name>
Arguments:
name The name of the class
Options:
-m, --migration Create a new migration file for the model.
-h, --help Display this help message
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi Force ANSI output
--no-ansi Disable ANSI output
-n, --no-interaction Do not ask any interactive question
--env[=ENV] The environment the command should run under.
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Help:
Create a new Eloquent model class
So if we create a table called Registrations we can use the command line:
#php artisan make:model Registrations --migration -vvv Model created successfully. Created Migration: 2016_03_24_111120_create_registrations_table #
The Model file generated is bare at best, but it doesn't hurt to have it done via the tool, you will find the Model file in the "app" dicrectory:
<?php namespace App;
use IlluminateDatabaseEloquentModel;
class Registrations extends Model
{
//
}
The migration file generated however does have extra support code in it.
<?php
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;
class CreateRegistrationsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('registrations', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('registrations');
}
}
Structuring a Model
Below is a file for a table called "account_plans" from a real application (still in development). I selected it as it has a reasonable number of columns, it's still under development so I can make changes if needed and I can refactor the code with no production issues.
<?php namespace App;
use IlluminateDatabaseEloquentModel;
class AccountPlans extends Model
{
protected $id;
protected $plan_name;
protected $plan_description;
protected $plan_status;
protected $plan_base_cost;
protected $plan_cnt_clients;
protected $plan_cnt_debtors;
protected $plan_cnt_users;
protected $plan_cnt_storage;
protected $plan_additional_clients;
protected $plan_additional_debtors;
protected $plan_additional_users;
protected $plan_additional_storage;
protected $plan_date_created;
#
#
#
public $timestamps = false;
#
#
#
protected $fillable = array('plan_name','plan_description','plan_status','plan_base_cost','plan_cnt_clients','plan_cnt_debtors','plan_cnt_users','plan_cnt_storage','plan_additional_clients','plan_additional_debtors','plan_additional_users','plan_additional_storage','plan_date_created');
public function InsertPlan($d)
{
$today = date("Y-m-d");
return DB::table('account_plans')->insertGetId(
array(
'plan_name'=>$d['plan_name'],
'plan_description'=>$d['plan_description'],
'plan_status'=>'A',
'plan_base_cost'=>$d['plan_base_cost'],
'plan_cnt_clients'=>$d['plan_cnt_clients'],
'plan_cnt_debtors'=>$d['plan_cnt_debtors'],
'plan_cnt_users'=>$d['plan_cnt_users'],
'plan_cnt_storage'=>$d['plan_cnt_storage'],
'plan_additional_clients'=>$d['plan_additional_clients'],
'plan_additional_debtors'=>$d['plan_additional_debtors'],
'plan_additional_users'=>$d['plan_additional_users'],
'plan_additional_storage'=>$d['plan_additional_storage'],
'plan_date_created'=>$today
)
);
}
public function getByID($id=0)
{
return DB::table('account_plans')->where(['id'=>$id])->get();
}
}
The basic code shows two methods, InsertPlan() and getByID(), (with no comments). We will add more methods as we go (and comments) but these two methods will help us start building basic functionality for use in our Controller.
You will also notice there are two other variables defined in the Model file, $timestampes and an array called $fillable. "Timestamps" are automatically defined when you use Artisan to create a Migration and the framework will try to update a timestamp column it assumes is there, so settings this to false disables this behaviour. This line is not normally incuded in the pre-built Model, you need to add it and set it to false.
"Fillable" is for mass assignment protection, if the variables in the table are not defined in "fillable", you cannot update them and attempts to do so will cause a Mass Assignment Exception in your application. You can also define an array called "guarded" to define columns that cannot be updated en-mass.
Lets look at the insert method, apart from the data we pass in, columns such as "status" and "date_created" are assigned set values during the insert which is a logical action to do. In our application, setting these makes the account plan go active immediately.
When the controller invokes this object it will use the following syntax:
$AccountPlans = new AppAccountPlans();
To then access a method (in this example the getByID() method, we will call the method using:
$rows = $AccountPlans->getByID($account_plan_id);
In its present form the Model has both methods for talking to the Database and it has protected variables to hold database parameters that can represent a single row in the table. Ideally an Insert, Update or getByID() method should update these values to reflect the row data in the Model, this effectively makes the Model a Data Transfer Object (DTO) which will be useful in our application down the track.
If we update our Model to also be a DTO then the code to do this will look something like:
public function InsertPlan($d)
{
$this->plan_name = $d['plan_name'];
$this->plan_description = $d['plan_description'];
$this->plan_status = 'A';
$this->plan_base_cost = $d['plan_base_cost'];
$this->plan_cnt_clients = $d['plan_cnt_clients'];
$this->plan_cnt_debtors = $d['plan_cnt_debtors'];
$this->plan_cnt_users = $d['plan_cnt_users'];
$this->plan_cnt_storage = $d['plan_cnt_storage'];
$this->plan_additional_clients = $d['plan_additional_clients'];
$this->plan_additional_debtors = $d['plan_additional_debtors'];
$this->plan_additional_users = $d['plan_additional_users'];
$this->plan_additional_storage = $d['plan_additional_storage'];
$this->plan_date_created = $today;
$today = date("Y-m-d");
$this->id = DB::table('account_plans')->insertGetId(
array(
'plan_name'=>$d['plan_name'],
'plan_description'=>$d['plan_description'],
'plan_status'=>'A',
'plan_base_cost'=>$d['plan_base_cost'],
'plan_cnt_clients'=>$d['plan_cnt_clients'],
'plan_cnt_debtors'=>$d['plan_cnt_debtors'],
'plan_cnt_users'=>$d['plan_cnt_users'],
'plan_cnt_storage'=>$d['plan_cnt_storage'],
'plan_additional_clients'=>$d['plan_additional_clients'],
'plan_additional_debtors'=>$d['plan_additional_debtors'],
'plan_additional_users'=>$d['plan_additional_users'],
'plan_additional_storage'=>$d['plan_additional_storage'],
'plan_date_created'=>$today
)
);
return $this->id;
}
We can also add the code block to the "update" and "getByID()" methods and our object will be updated accordingly when these methods are called. We can even add a Constructor and pass in the row ID and fill the object that way if required.
Accessing and Manipulating Data
If we wanted to get a single row from our application, we can call the getByID() method in our model as follows:
$row = $AccountPlans->getByID($account_plan_id)[0];
Which at a lower level in the Model calls:
public function getByID($id)
{
return DB::table('account_plans')->where(['id'=>$id])->get();
}
The basic operations an application will typically perform on a database that will result in changes are Insert's, Update's and Deletes. Selects on the other hand will only read from the database and if no index is specified on the columns that form the selection then the SQL engine will perform a sequential read of the tables involved which can have significant performance implications.
We already have a basic insert using Fluent syntax, this form uses an array of columns and returns the inserted row ID. Lets add a delete method:
public function DeleteByID($id)
{
return DB::table('account_plans')->where(['id'=>$id])->delete();
}
The where clause uses the row ID which is a primary key and the method will return the number of deleted rows. Inside the where clause you can see the 'id' parameters are encased in an array [] syntax. You will also see that the delete() method is chained to the where() method similar to our getByID() method above. When using the Fluent Query Builder, chaining will be used many times to join operations together.
Lets now look at an Update method, in this case we will use most of the columns in our UpdateAccountPlan() method (without saving the data into the Model):
public function UpdateAccountPlan($d)
{
return DB::table('account_plans')->where(['id'=>$d['id']])->update(
array(
'plan_name'=>$d['plan_name'],
'plan_description'=>$d['plan_description'],
'plan_status'=>$d['plan_status'],
'plan_base_cost'=>$d['plan_base_cost'],
'plan_cnt_clients'=>$d['plan_cnt_clients'],
'plan_cnt_debtors'=>$d['plan_cnt_debtors'],
'plan_cnt_users'=>$d['plan_cnt_users'],
'plan_cnt_storage'=>$d['plan_cnt_storage'],
'plan_additional_clients'=>$d['plan_additional_clients'],
'plan_additional_debtors'=>$d['plan_additional_debtors'],
'plan_additional_users'=>$d['plan_additional_users'],
'plan_additional_storage'=>$d['plan_additional_storage']
)
);
}
As per the delete() call, there is a where clause which uses our ID of the row, passed in the array called $d. We don't update the date created field so its left out of the update statement. There is nothing wrong with creating other update methods such as:
public function UpdateStatus($id,$status)
{
return DB::table('account_plans')->where(['id'=>$d['id']])->update(['plan_status'=>$status]);
}
If we chain "where" clauses we create an "AND" condition, for a logical "OR" clause we can use orWhere() instead and we can use Advanced Where Clauses to build as complex a query as we need.
A simple AND clause would look like:
public function GetClientsDebtors($client_count, $debtor_count)
{
return DB::table('account_plans')
->where(['plan_cnt_clients'=>$client_count])
->where(['plan_cnt_debtor'=>$debtor_count])
->get();
}
Finding dates always seams to be an issue for people, but if you use YYYY-MM-DD formatting then using logic such as whereBetween() will return rows between these dates but not including the dates.
return DB::table('account_plans')->whereBetween('plan_date_created', ['2015-08-01', '2015-08-20'])->get();
So that's our basic Create, Update and Delete methods covered, lets look at some selects on this table, lets find all rows which have less than 10 clients and 10 users:
In our controller:
$AccountPlans = new AppAccountPlans();
$rows = $AccountPlans->ClientUserCount(10,10);
foreach($rows as $row)
{
echo "Plan ID is: $row->id ";
}
And Our method in our Model:
public function ClientUserCount($clients=1,$users=1)
{
return DB::table('account_plans')
->where(['plan_cnt_clients'=>$clients ])
->where([ 'plan_cnt_users'=>$users ])
->get();
}
By chaining our where() clauses one after the other we have created the AND logic needed to select on multiple columns.
We can extend the where clause to also use an IN clause, which is a popular method of restricting a query to a subset of records. Lets say we have a group of accounts that use account_plan 100, and we want to get all the clients under each account using that plan. So we need to build a list of accounts first, then use the list to get the clients.
public function getClientsByPlanID($plan_id)
{
$accounts = DB::table('accounts')->where(['account_plan_id'=>$plan_id])->lists('id');
return DB::table('clients')->whereIn('client_account_id', $accounts)->get();
}
The first line gets a list of 'id' columns's from the 'accounts' table that have a plan ID of what ever we passed into the function.
The whereIn() statement matches the list given to the column name in the 'clients' table and returns rows using the get().
So far a lot of our where clauses have been "this == that" so if you need to use the 'LIKE' operator, such as the function below for finding all DEMO users in our "users" table, then the where clause takes the form:
public function getDemoUsers()
{
return DB::table('users')->where('usr_type','LIKE',"DE%")->get();
}
We could adds lots more examples here especially for inner joins, outer joins, unions, aggregators, groupBy and a host of SQL standard operations. One very handy feature is the increment and decrement operation. This method will select, increment and update an integer column (as an option you can also pass the increment value if greater than 1):
public function IncrementAssigned($id)
{
return DB::table('users')->where(['id'=>$id])->increment('user_assigned');
}
But lets divert our efforts to using the Model as a DTO and wrap up.
Earlier in this article we mentioned our protected variables that represent 1 row of data. We also mentioned that the Insert, Update and getByID() methods were ideal places to update the data held in the class. To make a DTO useable you will need to decide if you provide public access to the variables or provide individual accessor methods. The advantage of accessor methods is when you use an tool that has an intelligent IDE, it can provide syntax highlighting, auto-complete and documentation options. If you use the "magic hooks" method by providing a __get() method you circumvent these useful features.
For reference, the PHP "Magic Methods" code for accesing internal class variables looks like this:
A PHP class accessor method using __get()
public function __get($name)
{
if(isset($this->$name))
{
return $this->$name;
}
else
{
return false;
}
}
Moving Forward
Each table in your project should have its own Model file. Its best to use the artisan tools as it may introduce features in later versions that add more versatility to the models it creates. The only issue I have with the creation of the model is it's placed in the 'app' directory rather than a special purpose "app/Models" directory. To fix this, I create an entry in my composer.json file to enable the inclusion of the classes in app/Models. The line to add looks like this:
"autoload": {
"classmap": [
"database",
"app/Models"
],
"psr-4": {
You will need to do a clearing of the artisan caches using php artisan cache:clear and also config:clear. After that run a composer dump-autoload and the next time your application runs the model files in app/Models will be useable. You will need to move each newly created model into this directory as the artisan tool will still create them in the default location.
