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:

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

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

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 the command to actually create the tables and register the migrations.

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

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:

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.

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:

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:

* 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

It will return a JSON response by default i.e an array of objects

3.  Get just one particular record( a book)

Now, here if id 2 doesn’t exist it will throw a ModelNotFound and NotFoundHttpException.

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

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

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.

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.

This executes if we want all the  books where pages_count is less than 1000 or  books that have title as My First Book!

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.

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

10. Delete a particular book

11. Get all the books that have pages_count between 150 and 220

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!

13. Get a book that has title Eze goes to College!

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.


15. Get the total number of books in the books table

16. Get the total number of books that have pages_count greater than 140

17.  Get the minimum number of pages of books

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

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

20. Get the sum of all pages of books that have pages_count greater than 170



21. Return all records starting from the 4th to the 10th record

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:

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:

for this operation, only these two queries will be executed:

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:

If you need to set additional query constraints on the eager loading query, you may pass aClosure to the load method:

24.  Order books by their title.

25. Group books by their prices

26. Get books that have their pages_count less than 150. So what’s difference between this and where?

27.  Get all books but in a more efficient way!

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

Create a new migration file, add attribute $table->softDeletes() and run the migration like so:

Then go to your Book Model, add the SoftDeletes Trait and also the $dates attribute.

29. Get only all the books that have been deleted

30. Get a book’s id that has been soft deleted and restore the book

31.  Delete a book totally. I mean really really delete the book. No jokes!!! :smile:


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.


Go to the Book Model Class and add this:

As explained in the comment, this will return every book price as $something.something e.g $7.45


Add this below also in the Book Model Class.

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 :smile:

This actually makes use of PHP’s magic methods.

Add this to the Book Model Class

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:


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:

but if we don’t cast the data type, we will have to do:

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:

That’s all.


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

Essentially we want to get books that have low page counts.

We can just add this to the Book Model like so:

Then when we want to retrieve the results in any of our controllers, we will just do:

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:

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:

We have come to the end of the beginning of this series: LARAVEL ELOQUENT FOR DAYS

Sorry for boring you for too long :smile:

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:




Food Ninja, Code Slinger, Technical Trainer, Accidental Writer, Open Source Advocate and Developer Evangelist.