Laravel Eloquent For Days: Crud, Mass Assignment, Aggregates, Eager Loading
Eloquent is the ORM for Laravel and it is a house of treasures. Dive deep into it and you’ll see lots of quality, awesome and unused mansions lying within.
This is the beginning of a series titled Laravel Eloquent For Days!
There are several helper functions, tips, tricks and techniques you will be exposed to when dealing with your database records or manipulating data in Laravel.
We’ll deal with basic crud, aggregates, mass assignment and eager loading techniques in this post.
Create a new Laravel app called eloquentJourney. Make sure you have a database too. Apart from the users table that comes by default with Laravel, we’ll create books, authors and publishers table.
From the terminal run, these commands like so:
|
1 |
php artisan make:migration create_books_table |
|
1 |
php artisan make:migration create_authors_table |
Now that we have created these two migration files, We’ll use Laravel Schema builder to create the columns for the table.
create_books_table migration file
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
<?php use IlluminateDatabaseSchemaBlueprint; use IlluminateDatabaseMigrationsMigration; class CreateBooks extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('books', function($table) { $table->increments('id')->unsigned(); $table->string('title', 250); $table->integer('pages_count'); $table->decimal('price', 5, 2); $table->text('description'); $table->timestamps(); $table->integer('author_id')->unsigned(); $table->foreign('author_id')->references('id')->on('authors'); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::drop('books'); } } |
We have title, pages_count, price, description, author_id, created_at and updated_at as the column names we want the books table to have.
The created_at and updated_at colums are created using the $table->timestamps() method.
The books table references the author_id that will be on the authors table so right now it is a foreign key on this books table.
Let’s create the authors table.
create_authors_table migration file
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
<?php use IlluminateDatabaseSchemaBlueprint; use IlluminateDatabaseMigrationsMigration; class CreateAuthors extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('authors', function($table) { $table->increments('id'); $table->string('first_name'); $table->string('last_name'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::drop('authors'); } } |
We really don’t need too many details here, so we will just have the id, first_name, last_name, created_at and updated_at columns on the authors table.
Now, that we have done that..run the command to actually create the tables and register the migrations.
|
1 |
php artisan migrate |
Trick: When you migrate, and you start having errors that signify that one table is been referenced as a foreign key but does not exist…and you already have a migration file for the table, all you need to do is rename the migration file to an earlier timestamp. Laravel runs migration files in order of timestamps.
e.g 2015_11_03_035919_create_authors.php is an example of a migration file. You can rename it to be
2015_09_03_035919_create_authors.php. I just changed the month to an earlier one so if there are other migration files that fall in the 10th month. This particular file will be run first before them.
Check your database..we should have authors, books, users, migrations and password_resets tables.
In the migrations table, you will notice a batch column, that is how Laravel keeps track of the migrations to rollback or reset.
Once you run a migration, it registers a number in the batch column with all the migration files it ran at that time. Now, if you run php artisan migrate:rollback, it rolls back the last migration, if you run it again, it rolls back the migration that executed before the last one.
php artisan migrate:reset rolls back all the migrations at once
php artisan migrate:refresh rolls back and run the migrations again.
We have talked too much about that. Let’s start doing stuff ASAP!
For tutorial purpose, we’ll be doing most of our manipulations from the route file since we are focused more on knowing how to deal with data/ Eloquent models.
Let’s create the Book Model
Run the command to do that
|
1 |
php artisan make:model Book |
The Book Model represents the books table. Laravel automatically maps a model to a table with its plural form
e.g Book model will be mapped to the books table, Author model will be mapped to the authors table
If you don’t like that and you want to map it to a different table, there is an option for you to do that
Inside the Model file, add this like so:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?php namespace App; use IlluminateDatabaseEloquentModel; class Book extends Model { /** * The table associated with the model. * * @var string */ protected $table = 'my_books'; } |
So that’s the $table attribute we just added to map it to a different table. Remember it’s an option for you to decide to use as a developer.
Open your routes.php file
Let’s create data for the books table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
Route::get('book_create', function(){ echo "Creating the records...."; $title = 'Eze goes to College'; $pages_count = 800; $price = 15.5; $description = 'This is a very interesting book about Eze going to College and killing it'; $author_id = 1; // Method 1 of inserting records into the database via a Model - Mass Assignment $book = new AppBook([ 'title' => $title, 'pages_count' => $pages_count, 'price' => $price, 'description' => $description, 'author_id' => $author_id, ]); $book->save(); // You have to call the save method here if using Method 1 technique // Use either of them to insert records into the database // For security issues it won't allow you create those records just like that, so... // Make sure you have properties $fillable or $guarded in your Model // e.g protected $fillable = ['title','pages_count','price','description','author_id','publisher_id'] // Method 2 of inserting records into the database via a Model - Mass Assignment $book = AppBook::create([ 'title' => $title, 'price' => $price, 'pages_count' => $pages_count, 'description' => $description, 'author_id' => $author_id, 'publisher_id' => $publisher_id ]); echo "Done...."; }); |
There are different ways of inserting data into tables in Laravel.
Remember each model represents a table.
The first method is passing an associative array of values into the Model’s Constructor while the second method is calling the static create method and passing in the array of values. We call these techniques Mass Assignment. For Security reasons, every model attribute ( which are table columns in most cases) are guarded in Laravel. It disallows inserting records en masse just like that.
To enable this mass inserting of records, we have to add the $fillable attribute to the model like so:
|
1 2 3 4 5 6 7 8 9 10 |
<?php namespace App; use IlluminateDatabaseEloquentModel; class Book extends Model { protected $fillable = ['title','pages_count','price','description','author_id']; } |
The $fillable array can be called a whitelist. We insert the names of the table columns that we want mass assignment to happen on.
Another alternative is using the $guarded array which can be called a blacklist. We can insert the names of the table columns that we don’t want the mass assignment to happen on.
Trick: If we don’t want the mass assignment to happen on any of them, we can just write it in the Book Model like so:
|
1 |
protected $guarded = ['*']; |
* means all the table column names . So all the table column names would be guarded.
Note that when you use the first method to insert records like you see above, you have to call the save() method on the model instance to trigger the actual saving of the records.
So, go to that route a couple of times to create records for the books..Every time, change the contents so it wont be the same of all the records you’ll have in the table.
2. Fetch all the records in the Books table
|
1 2 3 |
Route::get('book_get_all', function(){ return AppBook::all(); }); |
It will return a JSON response by default i.e an array of objects
3. Get just one particular record( a book)
|
1 2 3 |
Route::get('book_get_one_record', function(){ return AppBook::findOrFail(2); }); |
Now, here if id 2 doesn’t exist it will throw a ModelNotFound and NotFoundHttpException.
|
1 2 3 |
Route::get('book_get_one_record', function(){ return AppBook::find(2); }); |
If id 2 doesn’t exist, it stops execution, nothing is been returned, nothing even shows on the screen so it is advisable you use findOrFail. At the end of the day, it still totally depends on the use case the developer is dealing with.
Now, if id 2 exists, it will return an object with the data for the second record in the books table.
4. Get all the books that have pages_count less than 1000
|
1 2 3 4 |
Route::get('book_get_where', function(){ $result = AppBook::where('pages_count', '<', 1000)->get(); return $result; }); |
AppBook::where(‘pages_count’, ‘<‘, 1000) actually builds the SQL query, then the get() triggers the execution and returns a collection of values.
If there are no books with pages_count less than 1000, it will return [] ( an empty array).
5. Retrieve only the first result out of all the records returned
|
1 2 3 4 |
Route::get('book_get_where_first', function(){ $result = AppBook::where('pages_count', '<', 1000)->first(); return $result; }); |
So if we have five books with pages_count less than 1000, this query will return only the first result.
6. Chaining where methods, evaluating multiple AND Conditions.
|
1 2 3 4 5 6 |
Route::get('book_get_where_chained', function(){ $result = AppBook::where('pages_count', '<', 1000) ->where('title', '=', 'My First Book!') ->get(); return $result; }); |
Can you remember the days of writing raw SQL queries in your php code. For instance, if we want to get all the books that have title as My First Book! and pages_count less than 1000, We will have something like:
select * from books where title = ‘My First Book!’ AND pages_count < 1000
That’s what the chained multiple where methods actually do.
7. Chaining where methods, evaluating multiple OR Conditions.
|
1 2 3 4 5 6 |
Route::get('book_get_or_where_chained', function(){ $result = AppBook::where('pages_count', '<', 1000) ->orWhere('title', '=', 'My First Book!') ->get(); return $result; }); |
This executes if we want all the books where pages_count is less than 1000 or books that have title as My First Book!
|
1 2 3 4 5 6 |
Route::get('book_get_where_complex', function(){ $results = AppBook::where('title', 'LIKE', '%ook%') ->orWhere('pages_count', '>', 190) ->get(); return $results; }); |
8. Get all books where pages_count is greater than 120 AND title might have Book in its name OR books where pages_count is less than 200 AND description is equal to Eze goes to college.
|
1 2 3 4 5 6 7 8 9 |
Route::get('book_get_where_more_complex', function(){ $results = AppBook::where(function($query){ $query->where('pages_count', '>', 120)->where('title', 'LIKE', '%Book%'); })->orWhere(function($query){ $query->where('pages_count', '<', 200)->orWhere('description', '=', 'Eze goes to College'); })->get(); return $results; }); |
Now, you can have more complex queries by deep nesting several where and orWhere queries in the closures.
9. Update a particular book’s information
|
1 2 3 4 5 6 |
Route::get('book_update', function() { $book = AppBook::find(3); $book->title = 'Eze now goes to University!'; $book->pages_count = 285; $book->save(); }); |
10. Delete a particular book
|
1 2 3 |
Route::get('book_delete', function() { AppBook::find(7)->delete(); }); |
11. Get all the books that have pages_count between 150 and 220
|
1 2 3 4 5 |
Route::get('book_get_whereBetween', function(){ $results = AppBook::whereBetween('pages_count', [150, 220])->get(); return $results; }); |
Note: I really like this method, it prevents you from writing long queries.
12. Get all the books that have titles Eze goes to School! and Eze goes to college!
|
1 2 3 4 5 6 |
// Return all results that have title that exist in this array Route::get('book_get_whereIn', function(){ $results = AppBook::whereIn('title', ['Eze goes to School!','Eze goes to college!'])->get(); return $results; }); |
13. Get a book that has title Eze goes to College!
|
1 2 3 4 5 6 |
// Fetching all records with the where clause in conjunction with a column Name a.k.a magic Where Route::get('book_get_whereColumnName', function(){ $results = AppBook::whereTitle('Eze goes to College!')->get(); return $results; }); |
Now this is where the Magic happens. title is a column name in the books table so we appended it to the where clause like so whereTitle. We can also use wherePagesCount if we want books that have pages_count as blah blah blah..
14. Get all books that have title has NULL. For example if the title has a nullable method attached to it in its migration file, that means there are books that can be inserted without titles. So this query returns all the books without titles.
|
1 2 3 4 5 6 |
// Return all books that dont exist. .ie books that have the title column as NULL Route::get('book_get_whereNull', function() { $results = AppBook::whereNull('title')->get(); return $results; }); |
BRACE UP AND GRAB THAT CUP OF COFFEE!…LET’S TALK ABOUT AGGREGATES!
15. Get the total number of books in the books table
|
1 2 3 4 5 6 |
// Get the number of books in the database Route::get('book_get_books_count', function(){ $booksCount = AppBook::count(); return $booksCount; }); |
16. Get the total number of books that have pages_count greater than 140
|
1 2 3 4 5 6 |
// Get the number of books that have page count greater than 140 Route::get('book_get_bookPages_count', function(){ $booksCount = AppBook::where('pages_count', '>', 140)->count(); return $booksCount; }); |
17. Get the minimum number of pages of books
|
1 2 3 4 5 6 7 |
/ Get the minimum number of pages i.e books with pages above 120, at least ..but the minimum this // Route::get('book_get_books_min_pages_count', function(){ $minPagesCount = AppBook::where('pages_count', '>', 120)->min('pages_count'); return $minPagesCount; }); |
For instance if we have 3 books like so:
Book Title: Eze goes to School, Page count 128
Book Title: Eze goes to College, Page count 140
Book Title: Eze marries Ada, Page count 167
The query above will return Book Title: Eze goes to School because it has the minimum number of book pages.
18. Get the maximum number of pages
|
1 2 3 4 5 6 7 |
// Get the maximum number of pages i.e books with pages above 120, return the highest number of pages // this will return 200 Route::get('book_get_books_max_pages_count', function(){ $maxPagesCount = AppBook::where('pages_count', '>', 120)->max('pages_count'); return $maxPagesCount; }); |
For instance if we have 3 books like so:
Book Title: Eze goes to School, Page count 128
Book Title: Eze goes to College, Page count 140
Book Title: Eze marries Ada, Page count 167
The query above will return Book Title: Eze marries Ada because it has the maximum number of book pages.
19. Get the average price of all the books that have Eze in their titles
|
1 2 3 4 5 6 |
// Get the average price of all the books Route::get('book_get_books_avg_price', function(){ $avgPrice = AppBook::where('title', 'LIKE', '%Eze%')->avg('price'); return $avgPrice; }); |
20. Get the sum of all pages of books that have pages_count greater than 170
|
1 2 3 4 5 6 |
// Get the sum of all pages of books that have pages count greater than 170 Route::get('book_get_books_sum_price', function(){ $countTotal = AppBook::where('pages_count', '>', 170)->sum('pages_count'); return $countTotal; }); |
I HOPE YOU ARE NOT TIRED!.. YOU CAN ALWAYS BOOKMARK THIS POST AS A REFERENCE TOOL WHENEVER YOU NEED TO MANIPULATE DATA.
EAGER LOADING TECHNIQUES
21. Return all records starting from the 4th to the 10th record
|
1 2 3 4 5 6 7 |
// Skip the first 3 records and return the next 6 records Route::get('book_skip_and_take', function(){ $books = AppBook::skip(3)->take(6)->get(); return $books; }); |
It skips record 1 to 3, then returns 4 to 10. Now if the records numbering have been screwed with, then it skips the first 3 records and returns the next 6 records. Can you remember LIMIT used in raw SQL queries? that’s essentially what happens here.
22. Now, let’s retrieve all books and their authors:
|
1 2 3 4 5 |
$books = AppBook::all(); foreach ($books as $book) { echo $book->author->first_name; } |
This loop will execute 1 query to retrieve all of the books on the table, then another query for each book to retrieve the author. So, if we have 25 books, this loop would run 26 queries: 1 for the original book, and 25 additional queries to retrieve the author of each book.
Thankfully, we can use eager loading to reduce this operation to just 2 queries. When querying, you may specify which relationships should be eager loaded using the with method:
|
1 2 3 4 5 |
$books = AppBook::with('author')->get(); foreach ($books as $book) { echo $book->author->first_name; } |
for this operation, only these two queries will be executed:
|
1 2 3 |
select * from books select * from authors where id in (1, 2, 3, 4, 5, ...) |
23. Sometimes you may need to eager load a relationship after the parent model has already been retrieved. For example, this may be useful if you need to dynamically decide whether to load related models:
|
1 2 3 4 5 |
$books = AppBook::all(); if ($someCondition) { $books->load('author'); } |
If you need to set additional query constraints on the eager loading query, you may pass aClosure to the load method:
|
1 2 3 |
$books->load(['author' => function ($query) { $query->orderBy('published_date', 'asc'); }]); |
24. Order books by their title.
|
1 2 3 4 5 6 7 |
// Order the books in ascending order according to their title, `desc` is also an alternative Route::get('book_orderBy', function(){ $books = AppBook::orderBy('title', 'asc')->get(); return $books; }); |
25. Group books by their prices
|
1 2 3 4 5 6 7 |
// Group the books by the price...e.g if 3 books have thesame price, it groups them together as one Route::get('book_groupBy', function(){ $books = AppBook::groupBy('price')->get(); return $books; }); |
26. Get books that have their pages_count less than 150. So what’s difference between this and where?
|
1 2 3 4 5 6 7 |
// Return all books that have pages_count less than 150 Route::get('book_having', function(){ $books = AppBook::having('pages_count', '<', 150)->get(); return $books; }); |
27. Get all books but in a more efficient way!
|
1 2 3 4 5 6 7 8 9 10 11 12 |
/** * Record Chunking for Memory Optimization. * load 4 results, process them, unload them and repeat the same thing with the next 4 */ Route::get('chunk_these_books', function(){ AppBook::chunk(3, function($books){ foreach($books as $book){ echo $book->title . "<br/>"; }; }); }); |
Note: I really love this!..It’s very efficient when you have records running into thousands and hundreds of thousands!
28. Get all books including the ones that have been deleted
|
1 2 3 4 5 6 7 8 9 10 |
/** * Return all books including the ones that have been deleted * Listen: This requires that your migration already has $table->softDeletes() * and your Model also uses the Eloquent SoftDeletes trait */ Route::get('all_books_including_those_that_have_been_deleted', function(){ $books = AppBook::withTrashed()->get(); return $books; }); |
Create a new migration file, add attribute $table->softDeletes() and run the migration like so:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
<?php use IlluminateDatabaseSchemaBlueprint; use IlluminateDatabaseMigrationsMigration; class AddSoftdeletesToBooksTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::table('books', function($table) { $table->softDeletes(); }); } /** * Reverse the migrations. * * @return void */ public function down() { } } |
Then go to your Book Model, add the SoftDeletes Trait and also the $dates attribute.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?php namespace App; use IlluminateDatabaseEloquentModel; use IlluminateDatabaseEloquentSoftDeletes; class Book extends Model { use SoftDeletes; protected $fillable = ['title','pages_count','price','description','author_id','publisher_id']; protected $dates = ['deleted_at']; } |
29. Get only all the books that have been deleted
|
1 2 3 4 5 6 |
// Return only all the books that have been deleted Route::get('only_deleted_books', function(){ $books = AppBook::onlyTrashed()->get(); return $books; }); |
30. Get a book’s id that has been soft deleted and restore the book
|
1 2 3 4 5 |
// Restore a record that has been deleted provided you have the id Route::get('restore_deleted_book', function(){ $trashedBook = AppBook::find($trashedBookId); $trashedBook->restore(); }); |
31. Delete a book totally. I mean really really delete the book. No jokes!!! 
|
1 2 3 4 5 6 |
// Truly delete a record, I mean really really delete..I mean Bye Bye to the record Route::get('truly_delete_a_record', function(){ // $bookId refers to the id of the book you want to truly delete $book = AppBook::find($bookId); $book->forceDelete(); }); |
ACCESSORS AND MUTATORS
Accessors and Mutators are essentially setters and getters.
Accessors manipulate data coming from the database, Mutators manipulates data just before saving it into the database. That’s a simple way of explaining it.
Accessors
Go to the Book Model Class and add this:
|
1 2 3 4 5 6 7 8 9 |
/** * Accessor..manipulates values returned from the database * e.g <getColumnNameAttribute> that's the syntax e.g if you want to manipulate the title column, the method name * will be getTitleAttribute */ public function getPriceAttribute($value) { return '$' . $value; } |
As explained in the comment, this will return every book price as $something.something e.g $7.45
Mutators
Add this below also in the Book Model Class.
|
1 2 3 4 5 6 7 8 9 |
/** * Mutator...manipulates values as they are been inserted/stored in the database * e.g <setColumnNameAttribute> that's the syntax e.g if you want to manipulate the title column, the method name * will be setTitleAttribute like we have here */ public function setTitleAttribute($value) { $this->attributes['title'] = strtoupper($value); } |
As explained in the comment, this will change every title to UPPERCASE just before inserting it into the database table.
Creating table columns otherwise known as Model attributes on the fly!..Another Magic
This actually makes use of PHP’s magic methods.
Add this to the Book Model Class
|
1 2 3 4 5 6 7 8 9 10 |
protected $appends = ['book_and_price']; /** * Creating attributes on the fly. We are creating attribute book_and_price column attribute. This does not exist in any of the database table columns * One more thing we have to register the attribute in the model via the $appends array */ public function getBookAndPriceAttribute() { return $this->attributes['title'].' ' . $this->attributes['price']; } |
Add the attribute to the $appends array. It will append book_and_price attributes to the results that is been returned whenever any query is made on the Model.
Now, we don’t have any column name as book_and_price but this method above creates that and the resulting value will be a combination of the book’s title and price. So when the results are returned, you will see the book_and_price attribute like it exists as a column in the table like so:
ATTRIBUTE CASTING
This is simply converting the data types of a table column(s) to a desired data type. The supported types in Laravel are integer, real, float, double, string, boolean, object and array.
For example, if we have a table column/attribute in our books table as is_promo. The only values allowed for it in the database are 1 or 0. 1 meaning true, 0 meaning false.
When processing results from the database, all we want to do is:
|
1 2 3 4 5 6 7 8 |
if( $books->is_promo ) { // don't sell it } else { // Oya, give it out to the masses ASAP } |
but if we don’t cast the data type, we will have to do:
|
1 2 3 4 5 6 7 8 |
if( $books->is_promo == 1 ) { // blah blah } else { // bloo bloo } |
Which is longer, we want to KISS ( Keep it Simple Stupid), so to avoid doing this latter type of comparison, head over to your Book Model and add the table attribute to the $casts array like so:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<?php namespace App; use IlluminateDatabaseEloquentModel; use IlluminateDatabaseEloquentSoftDeletes; class Book extends Model { use SoftDeletes; protected $fillable = ['title','pages_count','price','description','author_id','publisher_id']; protected $dates = ['deleted_at']; protected $casts = [ 'is_promo' => 'boolean' ]; ...... ...... } |
That’s all.
QUERY SCOPES
OMG!!!..I also really love this technique. The use of scopes. It keeps everything DRY( Don’t Repeat Yourself )
There are some queries that you might have to repeat in several controllers but you can just write it once in your model and call the method anywhere in any of your controllers.
For example
|
1 |
AppBook::where('pages_count', '<', 200); |
Essentially we want to get books that have low page counts.
We can just add this to the Book Model like so:
|
1 2 3 |
public function scopeLowPageCount($query){ return $query->where('pages_count', '<', 200); } |
Then when we want to retrieve the results in any of our controllers, we will just do:
|
1 |
$booksWithLowPageCount = AppBook::lowPageCount()->get(); |
Shorter and Cleaner!..Whoop!! Whoop!!
One More thing
When you are getting results from the database via the Models, if there are attributes you don’t want to show in the result, you can the attributes name to the $hidden array in the Book Model like so:
|
1 |
protected $hidden = ['title']; |
Now, the returned results will not show the title of the books.
An alternative to that is the $visible array, if you want some attributes to be present in the returned results, then list them in the $visible array in the Book Model like so:
|
1 |
protected $visible = ['pages_count', 'price','description','author_id'] |
We have come to the end of the beginning of this series: LARAVEL ELOQUENT FOR DAYS
Sorry for boring you for too long
Pat yourself on the back because you have really done well for following through!
If you have any questions or observations, please let me know in the comments section below:
- How to build your own Youtube – Part 10 - August 1, 2016
- How to build your own Youtube – Part 9 - July 25, 2016
- How to build your own Youtube – Part 8 - July 23, 2016
- How to build your own Youtube – Part 6 - July 6, 2016
- Introducing Laravel Password v1.0 - July 3, 2016
- How to build your own Youtube – Part 5 - June 28, 2016
- How to build your own Youtube – Part 4 - June 23, 2016
- How to build your own Youtube – Part 3 - June 15, 2016
- How to build your own Youtube – Part 2 - June 8, 2016
- How to build your own Youtube – Part 1 - June 1, 2016