Query data from Database in Laravel

Tram Ho

3 years ago

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.

User<span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token function">all</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

1 2	User<span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token function">all</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

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

<span class="token constant">DB</span><span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token variable">$ten_bang</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token property">bieuthuc</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token function">laydulieu</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

DB::table($ten_bang)->bieuthuc->laydulieu();

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

<span class="token constant">DB</span><span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token function">table</span><span class="token punctuation">(</span>'users<span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

DB::table('users)->get();

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

<span class="token function">var_dump</span><span class="token punctuation">(</span><span class="token function">get_class_methods</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'\Illuminate\Database\Query\Builder'</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

var_dump(get_class_methods('\Illuminate\Database\Query\Builder'));

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

<span class="token php language-php"><span class="token delimiter important">&lt;?php</span>
<span class="token keyword">namespace</span> <span class="token package">App</span><span class="token punctuation">;</span>
<span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Database<span class="token punctuation">\</span>Eloquent<span class="token punctuation">\</span>Model</span><span class="token punctuation">;</span>

<span class="token keyword">class</span> <span class="token class-name">Comment</span> <span class="token keyword">extends</span> <span class="token class-name">Model</span>
<span class="token punctuation">{</span>
    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">user</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token punctuation">{</span>
        <span class="token keyword">return</span> <span class="token variable">$this</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token function">belongsTo</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'App\User'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</span>

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Comment extends Model

{

public function user()

{

return $this->belongsTo('App\User');

}

And there is a piece of code like this:

<span class="token variable">$comments</span> <span class="token operator">=</span> App\<span class="token package">Comment</span><span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token function">all</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$comments</span> <span class="token keyword">as</span> <span class="token variable">$comment</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">echo</span> <span class="token variable">$comment</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token property">user</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token property">name</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

foreach ($comments as $comment) {

echo $comment->user->name;

}

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()

<span class="token variable">$comments</span> <span class="token operator">=</span> App\<span class="token package">Comment</span><span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token function">with</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'user'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

$comments = App\Comment::with('user')->get();

The executed query will be like this:

<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> comments
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">where</span> id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>

select * from comments

select * from users where id in (1, 2, 3, 4, 5, ...)

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:

$users <span class="token operator">=</span> <span class="token keyword">User</span>::<span class="token keyword">with</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string">'posts'</span> <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token keyword">function</span><span class="token punctuation">(</span>$query<span class="token punctuation">)</span> {
  $query<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token keyword">where</span><span class="token punctuation">(</span><span class="token string">'title'</span><span class="token punctuation">,</span> <span class="token string">'like'</span><span class="token punctuation">,</span> <span class="token string">'%first%'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
}<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">&gt;</span>get<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

$users = User::with(['posts' => function($query) {

$query->where('title', 'like', '%first%');

}])->get();

Eager loading with load() method

<span class="token variable">$comments</span> <span class="token operator">=</span> App\<span class="token package">Comment</span><span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token function">all</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token function">load</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'user'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

$comments = App\Comment::all()->load('user');

We get the same execution query as using with()

<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> comments
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">where</span> id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>

select * from comments

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

Share the news now