This title was written for Laravel version 4. If you're looking for material on the latest version of Laravel, then please check out Code Smart.
We've learned how to configure our database and how we can use the schema builder to structure tables within our database, but now it's time to get down to the nitty gritty and learn how we can store information in the database.
Now some of you who have already encountered the database components of Laravel, or even those of you who have been using Laravel 3 might be wondering why I'm choosing to start with the ORM? Why don't I begin with SQL statements, then query building?
Well, let's take a step back and think about why we are here. You are a developer, a PHP developer in fact! Since you are reading this book, I'm hoping that you are a PHP 5+ developer, and will have embraced object-oriented development.
If we are describing the entities in our application as objects, then it makes sense to store them as objects, retrieve them as objects, and more.
Let's imagine that we are writing an online book store.
Object-oriented application design has taught us that we need to identify the objects within our application. Well, a bookstore isn't going to be very successful without any books, right? So there's a fair change that we will be wanting a book object to represent the individual books used by our application. Normally we will refer to these application objects as 'Models', since they represent part of our applications business model. Here's an example.
<?php
class Book
{
/**
* The name of our book.
*
* @var string
*/
public $name;
/**
* A description for our book.
*
* @var string
*/
public $description;
}
$book = new Book;
$book->name = 'The Colour of Magic';
$book->description = 'Rincewind and Twoflower in trouble!';
Wonderful!
We have created a book to represent Terry Pratchett's 'The Colour of Magic', one of my personal favourites! Now let's store this book in our database. We will assume that we used the schema builder and have already created a 'books' table with all required columns.
First we will need to construct an SQL query. Now I know that you would probably build a prepared query for security reasons, but I want to keep the example simple. This should do the trick...
<?php
$query = "
INSERT INTO
books
VALUES (
'{$book->name}',
'{$book->description}'
);
";
We construct an SQL query to insert our object into the database. This query can then be executed using whichever database adapter you normally use.
I think that it's a real shame that we have to build a string SQL query just to store that data in the database. Why bother creating the object in the first place if we are going to transform it into a string for storage? We'd just have to build the object again when retrieving it from the database too. It's a waste of time if you ask me...
I think that we should be able to 'throw' our objects directly at the database, without having to build those ugly SQL queries. Hmm, perhaps something like this?
<?php
$book = new Book;
$book->name = 'The Colour of Magic';
$book->description = 'Rincewind and Twoflower in trouble!';
$book->save();
The save() method would handle the SQL side of things for us. Persisting the object to the database. That would be great! Someone should really build on this idea of mine.
C> It's already been done buddy.
What, really? Shame... I thought I had found the idea that might bring me fame and fortune. Well I guess it's a good thing really.
Ah yes, I remember now. This functionality is provided by object relational mappers, or simply 'ORM's. ORMs can be used to allow us to map our application objects to database tables, and individual instances of these objects as rows. You can think of the class attributes of these objects as the individual columns for the table.
The ORM will take care of object retrieval and persistance for us, we won't have to write a single line of SQL. This is great news, because I can't stand SQL! It's ugly and boring. Objects are much more fun, right?
Many ORMs also offer the ability to manage the relationships between multiple object types. For example, books and authors. Authors and publishers, etc.
Laravel ships with its own ORM component called 'Eloquent'. Eloquent is very much true to its name. Its syntax is quite beautiful, and it makes interacting with the database layer of your application stack a pleasing experience, rather than a chore.
When I think about a storage layer the word CRUD comes to mind. No, I'm not refering to my dislike for SQL this time, but rather the actions that can be performed upon the storage layer.
- C - Create a new row.
- R - Read existing rows.
- U - Update existing rows.
- D - Delete existing rows.
Let's learn more about Eloquent by tackling these actions in order. We will start with the creation of Eloquent model instances.
Creating new models.
Before we create our first Eloquent model, we need a quirky example data topic. Hrm... I've just built a new gaming PC, so let's go with video games.
We can create some objects to represent video games, but first, we need to create a table schema.
We will create a new migration to build the schema for our 'games' table.
<?php
// app/database/migrations/2013_07_10_213946_create_games.php
use Illuminate\Database\Migrations\Migration;
class CreateGames extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('games', function($table)
{
$table->increments('id');
$table->string('name', 128);
$table->text('description');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('games');
}
}
Hopefully this sample code requires no introduction. If you have found anything confusing within the example then take another look at the schema builder chapter.
You will notice that we named our table games. This is because we intend to call our Eloquent model Game. Eloquent is clever, by default it will look for the plural form of the model name as the table to use to store instances of our objects. This behaviour can be overriden, but let's keep things simple for now.
Eloquent models have a basic requirements. A model must have an auto incremental column named id. This is a unique primary key that can be used to identify a single row within the table. You can add this column to the table structure easily by using the increments() method.
Let's run the migration to update the database.
{:lang="text"} $ php artisan migrate Migrated: 20130710213946create_games
Now we can get started. Let's create a new Eloquent model to represent our games.
<?php
// app/models/Game.php
class Game extends Eloquent
{
}
Here we have a complete Eloquent model that can be used to represent our games. Surprised? Yes I suppose it is a little sparse, but that's a good thing really. Many other ORMs will demand that you build an XML map of the database schema, or create annotations for each of the database columns of the table representing the object. We don't need to do this because Eloquent makes some sensible assumptions.
Let's create a new game.
<?php
// app/routes.php
Route::get('/', function()
{
$game = new Game;
$game->name = 'Assassins Creed';
$game->description = 'Assassins VS templars.';
$game->save();
});
Hey, that looks familiar! Isn't that how we wanted to persist our objects in the first place? It's clean and simple. We create a new instance of our 'Game' model and set its public attributes, which map to table columns, to the values that we require. When we are done, we simply call the save() method on the object to persist the new row to the database.
Let's visit the / URI. We're expecting to receive no response, since the query will execute and return nothing from our routed logic. However, we receive something quite different.
We receive an error screen. A beautiful error screen. A really beautiful error screen! The guy who themed that must have had some serious skills, right? Heh... Anyway, what's this error?
{:lang="text"}
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'updated_at' in 'field list' (SQL: insert into games (name, description, updated_at, created_at) values (?, ?, ?, ?)) (Bindings: array ( 0 => 'Assassins Creed', 1 => 'Assassins VS templars.', 2 => '2013-07-14 16:30:55', 3 => '2013-07-14 16:30:55', ))
When Eloquent creates our new model, it attempts to populate the updated_at and created_at columns of our table with the current time. This is because it expects us to have added the ->timestamps() method when building our table schema. It's a sensible default, since it never hurts to have a record of creation/update times. However, if you are using an existing database table, or simply don't wish to have the timestamp columns present within your database table, you may want to disable this functionality.
To disable automatic timestamp updates with Eloquent models, just add a new public attribute to your model.
<?php
// app/models/Game.php
class Game extends Eloquent
{
public $timestamps = false;
}
The public attribute $timestamps is inherited from the Eloquent base class. It is a boolean value that can be used to enable or disable the automatic timestamp functionality. In the above example we have set it to false, which will let Eloquent know that we wish to disable timestamping.
Let's visit that / URI once more. This time the page shows a black result. Don't panic, this is simply because we have not returned a response from our routed logic. We received no error message, so the SQL query must have been executed. Let's examine the games table to see the result.
{:lang="text"} mysql> use myapp; Database changed mysql> select * from games; +----+-----------------+------------------------+ | id | name | description | +----+-----------------+------------------------+ | 1 | Assassins Creed | Assassins VS templars. | +----+-----------------+------------------------+ 1 row in set (0.00 sec)
We can see that our new row has been inserted correctly. Great! We have inserted a new record without writing a single line of SQL. Now that's my kind of victory.
You will notice that we didn't have to specify an id value for our object. The id column is automatically incremented, so the database layer will handle the numbering of our rows for us. It's generally a bad idea to modify the id column of an Eloquent model. Try to avoid it unless you really know what you're doing.
We used the $timestamps attribute to disable automatic timestamps. Instead, let's take a look at what happens when we enable them. First we need to alter our database schema. It's a bad idea to manually modify our database schema, or to update existing migrations. This is because our database state might become 'out-of-sync' with our teammates. Instead, let's create a new migration that our teammates could also execute to receive our changes.
{:lang="text"} $ php artisan migrate:make addtimestampstogames Created Migration: 20130714165416addtimestampstogames
Our migration has been created. You will notice that we gave the migration a discriptive name to represent our intentions with the migration. This could be useful information should your teammates later execute the migration. Let's use the schema builder to add timestamps to our game table.
<?php
// app/database/migrations/2013_07_14_165416_add_timestamps_to_games.php
use Illuminate\Database\Migrations\Migration;
class AddTimestampsToGames extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('games', function($table)
{
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
//
}
}
We have used Schema::table() to alter our 'games' table, and the timestamps() method to automatically add the timestamps column. Now ladies and gentlemen, say it with me.
C> What goes up, must come down!
You really do learn fast! Great work. Let's remove the timestamps columns from the table within the down() method.
<?php
// app/database/migrations/2013_07_14_165416_add_timestamps_to_games.php
use Illuminate\Database\Migrations\Migration;
class AddTimestampsToGames extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('games', function($table)
{
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::table('games', function($table)
{
$table->dropColumn('updated_at', 'created_at');
});
}
}
I have used the dropColumn() schema builder method to remove the updated_at and created_at columns from the table within the down() method. I thought there might be a lovely dropTimestamps() method for this, but apparently not. Not a problem! It's an open source project, so I'll just send a pull request later when I get some free time. Hint hint...
Let's execute our new migration to add the new columns to our 'games' table.
{:lang="text"} $ php artisan migrate Migrated: 20130714165416addtimestampsto_games
Now we have a choice, we could either set the $timestamps attribute within our model to true, which would enable the automatic timestamping feature.
<?php
// app/models/Game.php
class Game extends Eloquent
{
public $timestamps = true;
}
Or... we could simply remove it. This is because the default value of the $timestamps attribute within the parent Eloquent model is true. Its value will be inherited within our model.
<?php
// app/models/Game.php
class Game extends Eloquent
{
}
Great, now let's execute our / URI once again to insert a new row. We will examine the 'games` table within the database to see the result.
{:lang="text"} mysql> use myapp; Database changed mysql> select * from games; +----+-----------------+------------------------+---------------------+---------------------+ | id | name | description | createdat | updatedat | +----+-----------------+------------------------+---------------------+---------------------+ | 1 | Assassins Creed | Assassins VS templars. | 2013-07-14 17:14:13 | 2013-07-14 17:14:13 | +----+-----------------+------------------------+---------------------+---------------------+ 1 row in set (0.00 sec)
As you can see, the created_at and updated_at columns have been populated with the current timestamp for us. This is a great timesaver! You may wish to remind your applications users about their one year anniversary using your application, and could compare the current date with the created_at column for this purpose.
Before we move to the next section I'd like to share a little trick with you. If you don't want your database table name to be the plural form of your model then you will need to let Eloquent know about it. Just add the $table public attribute to your model and set its value to the string name of your table. Eloquent will then use the provided table name for all future queries relating to this model.
<?php
// app/models/Game.php
class Game extends Eloquent
{
public $table = 'gamezilla_roar';
}
If you chose to namespace your models you will need to use the $table attribute to provide simple table names. This is because a model with the namespace and class combination of MyApp\Models\Game will result in an expected table name of my_app_models_games to avoid collisions with packages within other namespaces which also use the database. You will also notice that Eloquent is very clever, and will expand a camel cased namespace or model name into its snake cased variant.
We have learned how to create new rows within our database tables by using Eloquent to treat them as PHP objects. Next, let's take a look at how we can retrieve these existing rows.
Reading Existing Models
Eloquent offers a number of methods of querying for instances of models. We will examine them all in a future chapter, but for now we will use the find() method to retrieve a single model instance from the database by its id column. Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
$game = Game::find(1);
return $game->name;
});
We have used the static find() method of our model to retrieve an instance of Game representing the database row with an id value of 1. We can then access the public attributes of the model instance to retrieve the column values. Let's visit the / URL to see the result.
{:lang="text"} Assassins Creed
Great, our existing value has been retrieved. The static find() method is inherited from the Eloquent parent class, and does not need to be created within your model. As I said earlier, there are many other retrieval methods, which will be covered in a later chapter about querying Eloquent models. For now, let's look at how we can update existing table rows.
Updating Existing Models
If you have recently created a new model, then the chances are that you have assigned it to a variable. In the previous section we created a new instance of our Game model, assigned the instance to the $game variable, updated its columns and used the save() method to persist it to the database.
<?php
// app/routes.php
Route::get('/', function()
{
$game = new Game;
$game->name = 'Assassins Creed';
$game->description = 'Assassins VS templars.';
$game->save();
});
Just because we have save()d our model instance, it doesn't have to mean we can't modify it. We can alter its values directly and call the save() method once more to update the existing row. You see, the first time save() is used on a new object, it will create a new row and assign an auto incremental id column value. Future calls to the save() method will persist only the changes to columns for the existing row in our database.
Take a look at the following example.
<?php
// app/routes.php
Route::get('/', function()
{
$game = new Game;
$game->name = 'Assassins Creed';
$game->description = 'Show them what for, Altair.';
$game->save();
$game->name = 'Assassins Creed 2';
$game->description = 'Requiescat in pace, Ezio.';
$game->save();
$game->name = 'Assassins Creed 3';
$game->description = 'Break some faces, Connor.';
$game->save();
});
You might imagine that the above example would create three entries within the games table, but you would be wrong. You will notice that we are only creating a single new instance of the Game class. All future calls to save() serve to modify this existing database row. The last saved state of the object will be present within the database.
I'm going to truncate my games table within the database to demonstrate this example. Go ahead and do it too if you're following along at home.
{:lang="text"} mysql> truncate games; Query OK, 0 rows affected (0.00 sec)
Now let's visit the / URI once more to execute our routed logic. Here's the resulting contents for the games table.
{:lang="text"} mysql> select * from games; +----+-------------------+---------------------------+---------------------+---------------------+ | id | name | description | createdat | updatedat | +----+-------------------+---------------------------+---------------------+---------------------+ | 1 | Assassins Creed 3 | Break some faces, Connor. | 2013-07-14 17:38:50 | 2013-07-14 17:38:50 | +----+-------------------+---------------------------+---------------------+---------------------+ 1 row in set (0.00 sec)
As you can see, 'Assassins Creed 3' was updated by our last save() method.
The above method is very useful when you already have a reference to an existing instance of our model, but what if you don't? What if you created the model a long time ago? We can simply use the find() method to retrieve an instance representing an existing database row, and alter it accordingly.
Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
$game = Game::find(1);
$game->name = 'Assassins Creed 4';
$game->description = 'Shiver me timbers, Edward.';
$game->save();
});
Upon inspecting the games table, we discovered that our previous row was inserted with an id value of 1. Using the static find() method on our model, and the known id, we are able to retrieve an instance of Game representing the existing table row. Once the new instance has been returned, we can modify its column values and use save() in the same way as we did earlier.
Here's the resulting table row.
{:lang="text"} mysql> select * from games; +----+-------------------+----------------------------+---------------------+---------------------+ | id | name | description | createdat | updatedat | +----+-------------------+----------------------------+---------------------+---------------------+ | 1 | Assassins Creed 4 | Shiver me timbers, Edward. | 2013-07-14 17:38:50 | 2013-07-14 17:49:28 | +----+-------------------+----------------------------+---------------------+---------------------+ 1 row in set (0.00 sec)
As you can see, our existing row has been updated accordingly. Once again, we did not write a single line of SQL. Only beautiful, eloquent PHP. You will also notice that the updated_at column has been populated with update time automatically. Very useful!
Deleting Existing Models
Deleting Eloquent models is a simple process. First we need to get our hands on the instance of the model that we wish to delete. For example, we could use the find() method that we discovered in a previous sub chapter.
<?php
// app/routes.php
Route::get('/', function()
{
$game = Game::find(1);
});
Once we have our grubby little mitts on an Eloquent model instance, we can use the delete() method to remove the row represented by our model from the database.
<?php
// app/routes.php
Route::get('/', function()
{
$game = Game::find(1);
$game->delete();
});
We can also delete a single instance, or multiple instances of our models from the database using their id column values and the destroy() static method.
<?php
// app/routes.php
Route::get('/', function()
{
Game::destroy(1);
});
To destroy multiple records, you can either pass a number of id value as parameters to the destroy() method...
<?php
// app/routes.php
Route::get('/', function()
{
Game::destroy(1, 2, 3);
});
... or an array of id's, like this:
<?php
// app/routes.php
Route::get('/', function()
{
Game::destroy(array(1, 2, 3));
});
It's entirely up to you!
SQL offers a number of different and complex ways to query for a specific subset of records. Don't worry, Eloquent can also perform this simple task. In the next chapter we will learn about the variety of query methods available to the Eloquent ORM. Go ahead, flip the page!
My books are available online for free to encourage learning. However, if you'd like for me to keep writing, then please consider buying a digital copy over at Leanpub.com.
It's available in PDF, ePub, and Kindle format, and contains a bunch of extras that you won't find on the site. I have a full-time job, and I write my books in my spare time. Please consider buying a copy so that I can continue to write new books from the comfort of my sofa!