Laravel Database Queries - From Beginner to Highly Advanced

Use two database drivers in the same app for demonstrating purposes. Use migrations, seeders and factories

Compare it with Query Builder and Eloquent ORM. Install a package for debugging

Listen to database query events and learn how to make consistent database data using transactions

Let’s create comments table for learning purposes using migration mechanism of Laravel

Factories allow you to fill database with example content for your app or just for testing purposes

Write and execute seeders using model factories

How to get results from the database including select statements and aggregate functions

Get familiar with basic where clauses in Laravel Query Builder

‘whereNotBetween’, ‘whereIn’ and other sql clauses. How to get information from a table based on information inside another table (‘where exists’ clause)

Query JSON type column and when to use such a type

It prevents from very long web pages when a database result is big

If you search through many text records, fulltext search is a better choice (faster) than simple ‘like’ query

Laravel Query Builder or Eloquent just abstracts the complexity of sql queries. But sometimes we need to use raw sql expressions. Here is when

Get familiar with how Laravel abstracted common sql clauses like ORDER, etc. so that you do not need to write raw sql queries

Using conditional clauses we can utilize the same query to get different database results. To process large amount of records we can chunk results to prevent out of memory error

How to use join, left join, cross join and when to use it (by examples). Use console command to generate model, migration, factory and seeder at once

If we have two tables with the same column types we can combine them using UNION clause

How to insert new data to a database

How to update data in database (including json columns)

How to delete rows and truncate entire table

How to prevent a row from being selected/modified by another process. dd() and dump() methods in queries

About chunking results (chunk, cursor methods) and select subqueries + order by data inside another table

Get single models and aggregates (select for single results from the database). About query scopes (local and global)

Eloquent get() or all() queries result in collection instance which allows us to make other useful operations on it like filtering, etc.

How to insert database records using models in Laravel framework (two ways). Mass assignment

How to update database records using models in Laravel framework

How to delete database records using models in Laravel. About soft deletes

If we want to execute some code while retrieving, deleting, updating records, we can use events on models

We can use model accessors when we want to present data in other way than written in the database, for example an accessor than capitalizes first letter of a name

We can use model mutators when we want to save manipulated data to the database, for example a mutator that capitalizes first letter of a name

It is possible to change at runtime the way of presenting data from database even if it comes from subquery

Define one to one relation like a user has one address. Fix problem with unique() method in Faker library or save relations using factories

A user can write many comments. Let’s define such relationship using Laravel Eloquent

More complicated relationship. Deal with pivot tables

Thanks to this relation we can for example show a country that a comment comes from (through user model who wrote a comment and lives in some country)

We can get database records for model from a model that is not connected with the first one (like in has one through). But this time we get many records

A model can belong to more that one other model – polymorphism, e.g. an image can belong to User or City (one to one)

A model can belong to more that one other model – polymorphism, e.g. a comment can belong to Article or Service, but we can have many comments (one to many)

E.g. a user can like an image and a room (many of them). The last two models can be liked by many users - polymorphism

In Eloquent we have available all the methods from Query Builder. Querying for existence of related models

We can specify query result for model based on condition within polymorphic related models. Also we can count related polymorphic models

Eloquent ORM is … eloquent :) - we do not need to use column_id manually to attach/update or delete related models!

It may be useful for example for handling events that occur while creating many to many relation, e.g. send an email when a role (many) was attached to the user (many)

By default Eloquent relationships introduce 1+N problem if we want to show all related models - one query to retrieve main model and N (unknown) number of queries to retrieve child models

Eloquent ORM or Query Builder or both of them? Or maybe plain sql? Here are the answers

Raw expressions (not entire queries but raw part of the sql queries) in Laravel and when to use them

Install and configure Redis – key value store memory database

Set, get and delete data from Redis database

How to serialize eloquent model to array or json and hide sensitive attributes like passwords, etc.

Returning JSON from resources and resource collections. Resource is a transformation layer between models and JSON response

Also include resources conditionally, including information from pivot tables

How to paginate and add additional metadata to the JSON response (also conditionally)

Additionally install a package for debugging

User model already exists after installation of Laravel. Populate users and posts tables with dummy data using Faker PHP library

Define also inverse of the relation (a comment belongs to one post). Populate comments table with dummy data

Define also inverse of the relation (a post belongs to one category). Populate categories table with dummy data

Populate tags table with dummy data

Let’s satisfy first business requirements of blog application – database queries for showing post categories, a list of post tags, latest posts. Order results

Most popular posts (most commented) and most active users (who wrote the most amount of posts)

Display most popular post category which is the one who has the most amount of comments (through Post model). HasManyThrough relationship

Also use eager loading feature, e.g when querying for single post, also load its comments to avoid N+1 problem

Search for blog posts by title or content. Use LIKE sql operator. Paginate results

Create FULLTEXT index using raw sql statement on DB facade and artisan tinker

Sort posts by date columns. Leverage useful when() Laravel function to avoid if statements when dynamic sorting. Paginate results

Order blog posts by the largest amount of comments

So that the code is clearer and it it possible to move parts of the query to other class methods

Show posts only from a concrete user

Show posts only with high rating (through comments)

Create plain indexes to speed up select queries. Create them using either raw sql in artisan tinker or migration files

Write operations on posts and comments using Laravel Eloquent ORM. Use relationships to associate models

Assign a tag (many to many) and a new category to blog post using Eloquent database relationships

Additionally install a debugging package for new project

Create Country, City models. Also factories, migrations, seeders. Database relationship hasMany

Create hasMany relationship (a city has many hotels)

Create hasMany relationship (a hotel has many rooms)

Create hasOne relationship (a room belongs to one room type)

We do not need to create model, factory and migration for User because it already exists after installation of Laravel framework

Also define factory for Reservation model and one to many relation with User model (a user has many reservations)

And populate with dummy data

First, get matching reservations from the reservations table, later get all rooms that fits reservation dates criteria from the request – Query Builder style

Compare performance with Query Builder style query

Rather than showing all date matching rooms, fetch only those who match requested city also (two versions of the query – Eloquent and Query Builder style)

Join hotel data for rooms results (for each room we want to see the hotel name for it). Also prevent a room from overbooking

Sort collection of rooms by price of the room in ascendant order

Show only specific size of the room, e.g. 1 double bed

Create indexes on room, hotels, cities, room_types, and reservation pivot table

We have to use database transaction here because we update 3 tables at once: reservations with its pivot table and room_types table (decrement the number of available rooms after the reservation)

Fix one error in our room searching database query

Fetch related models to reservation list like room types, hotels. Use eloquent ORM

Fetch related models to reservation list like room types, users. Use eloquent ORM

Order rooms by number of reservations and order reservations by nights count (the longest period of reservation time) – for admin panel

Get all owner’s hotels ordered by number of rooms. Get number of single rooms in each hotel

Show users ordered by highest paid reservation

Associate a city with the hotel (a hotel belongs to a city)

Room type belongs to a room, but room belongs to a hotel

Check cascade deletion of objects, e.g. if we remove a hotel we can assure that all of his rooms are also deleted (at sql server level)

Use chunk() method to save memory while updating large amount of records