Eloquent ORM and Query Builder

WHAT?

• The Eloquent ORM that comes with Laravel provides a complete and simple ActiveRecord for working with databases. Each table of the database will be mapped via a ‘Model’, and this model is used to interact with the table.
• Query Builder helps us to create queries from the database, which can be used to perform most database operations in the application and work with all supported databases.

HOW?

Eloquent

In Eloquent, each table in the database must correspond to a model. Note that each Eloquent model must extend Illuminate\Database\Eloquent\Model
Return 1 Eloquent Collections, where each result is 1 object.

Query builder

Query Builder builds a DB class to execute queries. Therefore, to start a Query Builder, we use the table() function on the DB facade.

Return 1 result array , where each result is 1 object StdClass by PHP

• $ten_bang is to set the table that the query will query to.
• properties are expressions that in the query will execute such as: where, orWhere, orderBy, groupBy, …
• travel data () is a method to execute a query including previous expressions, the most basic is get() in addition, first(), pluck(), lists(), … Example:

To see the expressions the query builder supports, run the following code and you will see that there are many supported queries:

COMPARE?

• Eloquent is built from Query Builder, so Query Builder cannot use eloquent’s methods.
• In case you have to join two or more tables, you should use the query builder to query according to stricter conditions
• Query Builder has faster data query performance than Eloquent ORM because Eloquent has to add an extra layer in the application and requires many SQL queries

Eager Loading

WHAT?

When we use ORM in laravel, by default ORM will be in “lazy” mode when loading all relational models. Let’s take a look at an example

And there is a piece of code like this:

Looking at the example above we will see:

• 1 query to get all the records from the comments table
• For each comment record we query one more sentence to get the user name from the relationship

=> So for n comment records we will need N+1 query, a huge number of queries

HOW?

Laravel has provided us with 2 methods load() and with() to deal with the above problem.

Eager loading with method with()

The executed query will be like this:

Chúng ta nhận ra điểm mạnh của phương thức này là chúng ta thay vì phải tốn N+1 query thì chúng ta sẽ chỉ tốn 2 query.

• 1 query loads all comment records
• A query that loads the relationship with the users . table

If you need to specify more conditions in the query, do the following:

Eager loading with load() method

We get the same execution query as using with()

Difference between with() and load()?

We will see that

• with() it will execute immediately after the first query and when it encounters complex methods like get(), first(), all(), …
• load() then it will run the method to load the records first and load the relationships later. When using load() we have the power to decide whether to run the second query to load the relationships or not

How to use?

• Use with() method when there is a query (Eloquent builder)
• Use the load() method when Eloquent collection or Model . data has been received