Query builder is a function that you can assemble SQL statements to query when you get records from the database, but you can easily assemble it.

Even if you are not familiar with SQL statements, this function allows you to query in a proper format, which is a very convenient function. It is also a function that is provided roughly in recent PHP frameworks.

This time, I will describe the function of Laravel’s Query Builder with a bit of breaking so that you can understand it even at the introductory level.

Verification environment

Regarding the version of Laravel, the operation has been confirmed on 5.7 / 5.6 / 5.5 / 5.4 / 5.3.

New features added since 5.6.0 release are annotated, so please use it against your own version.

The basic format for using Query Builder

This time we will use Query Builder in the controller, but the basic format is shown below.

<?php
namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB; // ← DB facade the Use

class SampleController extends Controller
{
    public function index()
    {
        // specify the table
        $users = DB::table('users');

    }
}

First, use the DB facade.

Then, by using the table method of the DB facade and passing the table name you want to acquire as an argument (“users” in the above), the query builder instance of that table will be acquired.

This time, as an example, I will write a query builder to get information from the user information table, but I will write a query in a chain for the variable $ users that stores the query builder instance of the users table and get the result It will be a thing.

From now on, the flow is to write the corresponding method on top of this base description.

Result data acquisition

Before describing the conditions, the acquisition of result data is shown below. Laravel’s query builder has the following getter methods:

get ()

Get all the result data according to the condition specification.

$data = $users->get();

Result data that can be obtained will be returned as a Collection object that contains an instance of StdClass object as a result . To access individual result data, you can loop it as follows or get it directly from the object.

// Get one by one in a loop
foreach ($data as $d) {
    echo $d->name;
}

// Get directly from the result data itself
echo $data[0]->name;

first ()

Only the first one of the result data will be fetched. No matter how many results you get, you will get only one.

$data = $users->first();

The result data is obtained with the StdClass object. To retrieve it, access the properties of the object.

echo $data->name;

value ()

Gets only one column specified in the value () method.

$data = $users->value('email');

However, this method retrieves only one case regardless of the condition, so it is used together with where clause etc. to retrieve the targeted one case.

echo $data; // test01@test.com

pluck ()

You can get only one specified column in the collection with the pluck () method.

$data = $users->pluck('email');

Unlike the value () method, this gets all data as a result of the condition.

Array
(
[0] => test01@test.com
[1] => test03@test.com
[2] => test04@test.com
[3] => test05@test.com
[4] => test06@test.com
[5] => test07@test.com
[6] => test08@test.com
[7] => test09@test.com
[8] => test10@test.com
)

Data can be accessed either in a loop or directly.

// Get one by one in a loop
foreach ($data as $d) {
    echo $d;
}

// Access from the result data itself
echo $data[0];

By the way, it is also possible to specify the identifier (property) column of the acquisition collection.

$data = $users->pluck('email', 'name');

By specifying the column to retrieve in the first argument and the property column in the second argument. It can be obtained in the form of key-value.

Array
(
[user01] => test01@test.com
[user02] => test02@test.com
[user03] => test03@test.com
[user04] => test04@test.com
[user05] => test05@test.com
[user06] => test06@test.com
[user07] => test07@test.com
[user08] => test08@test.com
[user09] => test09@test.com
[user10] => test10@test.com
)

exists ()

You can use the exists () method to check if the record exists for the condition.

$data = $users->exists();

Returns true if the record exists and false if the record does not exist .

if ( $ data ) { 
// record exists
} elseif ( ! $ data ) {
// record does not exist
}

doesntExist ()

The doesntExist () method is the opposite of the exists () method. Returns true if the record does not exist and returns false if the record exists .

$data = $users->doesntExist();

if ($data) {
    // record does not exist
} elseif (!$data) {
    // record exists
}

toSql ()

You can check the SQL statement you are trying to issue with the toSql () method.

$data = $users->toSql();

The SQL statement is stored in the variable $ data that stores the result .

echo $data; // => select * from `users`

Split processing

It may be difficult to get all at once when the target records are at the tens of thousands level when you try to get and process the records.

In such a case, if you use the chunk () method, you can split the acquisition condition.

$users->orderBy('id')->chunk(100, function ($users) {
foreach ($users as $user) {
//
}
});

In the above example, you can fetch 100 records for users table and process them in the closure.

One thing to keep in mind is that when using the chunk () method, it is necessary to specify the order of data by the orderBy () method.

By the way, you can interrupt the splitting process by returning false in the closure .

$users->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {

        // processing

        if($expr) {
            // chunk middle stage
            return false;
        }
    }
});

Aggregate

Laravel’s query builder comes with a set of aggregate methods.

After specifying the condition, the result can be obtained by chaining (connecting) the following methods at the end as in the case of obtaining result data.

count ()

You can get the number of records with the count () method.

$data = $users->count();
echo $data ;   // => 10 (cases)

max ()

You can get the maximum value of the specified column with max () method.

$data = $users->max('id');
echo $data; // => 10

Specify the column name in the argument.

min ()

You can get the minimum value of a specified column with the min () method.

$data = $users->min('id');
echo $data; // => 1

Specify the column name in the argument.

avg ()

You can get the average value of the specified column with the avg () method.

$data = $users->avg('id');
echo $data; // => 5.5000

Specify the column name in the argument.

sum ()

With the sum () method, you can get the total value of the specified column.

$data = $users->sum('id');
echo $data;  // => 55

Specify the column name in the argument.

Read also >>  Full Time - Backend Laravel Developer

SELECT

Chain the select () method to specify the columns to be fetched .

$data = $users->select('name', 'email as user_email')->get();

Pass the column you want to get as an argument, but you can also write an alias as described above.

// get a result
[ 0 ] => StdClass Object
(
[ Name ] => User01
[ user_email ] => Test01 Atto Test . Com
)
[ 1 ] => StdClass Object
(
[ Name ] => User02
[ user_email ] => Test02 Atto Test . Com
)

After defining the acquisition method in the select () method, if you want to add additional acquisition columns, you can add it with the addSelect () method.

$users->select('name', 'email as user_email');

// Add fetch column
$data = $users->addSelect('id')->get();

// Retrieve result
[0] => stdClass Object
(
    [name] => user01
    [user_email] => test01@test.com
    [id] => 1
)

[1] => stdClass Object
(
    [name] => user02
    [user_email] => test02@test.com
    [id] => 2
)

DISTINCT

If you use distinct () method, you can get the result of collecting duplicate rows.

$data = $users->distinct()->get();

Write SQL statement directly

You can also write SQL statements directly in the query builder.

Write the SQL statement using the DB :: raw method.

$data = $users->select(DB::raw('COUNT(*) AS user_count'))->get();

raw method

DB :: raw is the most basic SQL statement insertion method. Furthermore, the description can be shortened by using the raw method provided for each section.

selectRaw

The selectRaw method replaces the select (DB :: raw (…)) expression.

$data = $users->selectRaw('COUNT(*) AS user_count')->get();

whereRaw / orWhereRaw

The whereRaw and orWhereRaw methods can insert SQL statements directly into the WHERE clause.

$data = $users->whereRaw('`role` = 1')->get();

You can also bind parameters by passing a value in the form of an array to the second argument.

$data = $users->whereRaw('`role` = ? AND id < ?', [1, 5])->get();

havingRaw / orHavingRaw

The havingRaw and orHavingRaw methods can insert SQL statements directly into a having clause when doing a GROUP BY.

$data = $users
    ->select('role', DB::raw('SUM(role) as role_count'))
    ->groupBy('role')
    ->havingRaw('role > 1')
    ->get();

// Acquisition result
[0] => stdClass Object
    (
        [role] => 5
        [role_count] => 20
    )

[1] => stdClass Object
    (
        [role] => 10
        [role_count] => 50
    )

orderByRaw

The orderByRaw method can insert SQL statements directly into the ORDER BY clause.

$data = $users
->orderByRaw('updated_at - created_at DESC')
->get();

JOIN

When writing JOIN with Query Builder, it is as follows.

INNER JOIN

Use the join () method.

$data = $users
->join('role', 'users.role', '=', 'role.role_id')
->join('message', 'users.id', '=', 'message.user_id')
->get();

You can join multiple tables by chaining the join () method as above.

LEFT JOIN

Use the leftJoin () method.

$data = $users
->leftJoin('message', 'users.id', '=', 'message.user_id')
->get();

CROSS JOIN

Use the crossJoin () method.

By specifying the table you want to cross-join, you can get the Cartesian product (combination of all rows) of the base table and the specified table.

$data = $users->crossJoin('role')->get();

Complex JOIN

If the JOIN condition is more complicated than usual, it can be realized by defining the closure in the second argument.

$data = $users
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')
->orOn(...);
})
->get();

You can also use the WHERE clause in the closure.

$data = $users
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();

JOIN the subquery

You can use the methods joinSub () leftJoinSub () rightJoinSub () to JOIN a subquery to the query .

// subquery
$roles = DB::table('mst_role')->select('id', 'name')->where('deleted_at', null);

// a subquery JOIN to
$users = DB::table('users')
            ->select('users.id', 'users.name', 'roles.name AS role')
            ->joinSub($roles, 'roles', function ($join) {
                $join->on('users.role', '=', 'roles.id');
            })->get();

print_r($users);
// [0] => stdClass Object
//        (
//            [id] => 1
//            [name] => user01
//            [role] => system admin
//        )
//
// [1] => stdClass Object
//        (
//            [id] => 2
//            [name] => user02
//            [role] => admin
//        )
//
// [2] => stdClass Object
//        (
//             [id] => 3
//             [name] => user03
//             [role] => guest
//        )

Describe the subquery object in the first argument, an arbitrary identifier indicating them in the second argument, and the conditional expression for JOIN with the closure in the third argument.

* These methods can be used with ver5.6.17 or later.

UNION

If you want to perform UNION (query join), use the union () / unionAll () methods.

$data1 = DB::table('users')
->where('id', '<', 5);

$data2 = $users
->where('id', '>=', 5)
->union($data1)
->get();

As above, I will pass the instance that performs the query join with the union () method.

By the way, in the above, another query builder instance is generated separately, but be aware that if you perform UNION in the same instance, an error will occur depending on the maximum function nesting level setting value.

WHERE

From here, we will describe the condition grant (WHERE clause).

Basic WHERE clause

To use a popular WHERE clause, use the where () method.

Specify the column name in the first argument, the comparison operator in the second argument, or the operator used in SQL, and the value to compare in the third argument.

$data = $users->where('id', '>', 5)->get();

If the second argument is equal, that is, it is equal, you can omit the comparison operator and get two arguments.

// id = 5 the case of the 
$data = $users->where('id', 5)->get();

Similarly, the LIKE clause can be obtained with three arguments.

$data = $users->where('email', 'like', '%test.com')->get();

These where () methods can be chained together to form a WHERE clause, but they can be achieved with a single where () method by passing in an array.

$data = $users->where([
['role', '>', 5],
['id', '>', 6]
])->get();

OR clause

All of the above introduced will be a WHERE clause with AND if they are connected by a chain, but of course you can also write OR.

In that case, use the orWhere () method.

$data = $users
->where('role', '>', 5)
->orWhere('role', '>', 8)
->get();

whereBetween

You can write a BETWEEN clause (between A and B ) using the whereBetween () method.

$data = $users->whereBetween('id', [4, 6])->get();

Pass the target column in the first argument and the array in the second argument, respectively.

whereNotBetween

The whereNotBetween () method can write the NOT BETWEEN clause (not between A and B), the opposite of whereBetween ().

$data = $users->whereNotBetween('id', [4, 6])->get();

Pass the target column in the first argument and the array in the second argument, respectively.

IN clause

The methods related to the IN clause are as follows.

whereIn

You can write an IN clause (included) using the whereIn () method.

$data = $users->whereIn('id', [2, 5, 9])->get();

whereNotIn

For the whereNotIn () method, you can write a NOT IN clause (not included).

$data = $users->whereNotIn('id', [2, 5, 9])->get();

Please note that “included / not included” is an exact match. It does not mean “fuzzy”.

Read also >>  Shortcode Mastery Pro

Null discrimination

The method of null determination is as follows.

whereNull

Using the whereNull () method, you can write a condition where the column value is null.

$data = $users->whereNull('email')->get();

whereNotNull

On the other hand, whereNotNull () method can be used to describe the condition that column value is not null.

$data = $users->whereNotNull('email')->get();

Date and time comparison

Laravel’s query builder also has its own methods for comparing dates and times.

whereDate / orWhereDate

Use the dateDate () method for date comparison .

$data = $users->whereDate('updated_at', '2019-04-22')->get();

* OrWhereDate () is available since ver5.6.10. It seems to have been fixed because there are only two arguments in v5.6.19 (2018-04-30).

whereMonth / orWhereMonth

Use the whereMonth () method to compare months.

$data = $users->whereMonth('updated_at', 2)->get();

* OrWhereMonth () is available since ver5.6.10.

whereDay / orWhereDay

Use the whereDay () method to compare with the date value itself .

$data = $users->whereDay('updated_at', 25)->get();

* OrWhereDay () is available since ver5.6.10.

whereYear / orWhereYear

Use the whereYear () method for year comparisons .

$data = $users->whereYear('updated_at', 2018)->get();

* OrWhereYear () is available since ver5.6.10.

whereTime

Use the whereYear () method for time comparison .

$data = $users->whereTime('updated_at', '<', '12:00')->get();

There is a lot of freedom in searching around here (only time and date values ​​are not searched much, so it is necessary to use it in a complex way), so it is necessary to consider the usage, but usually DATE_FORMAT () must be used. It is very convenient to be able to realize the places where it should not be done by one method.

whereColumn

With the whereColumn () method. You can see that the two columns have the same value.

$data = $users->whereColumn('last_login_at', 'updated_at')->get();

The great thing about this method is that you can use comparison operators as well as equality tests.

$data = $users->whereColumn('last_login_at', '>', 'updated_at')->get();

And they can be set multiple by passing in an array.

$data = $users->whereColumn([
['last_login_at', '>', 'updated_at'],
['created_at', '<', 'updated_at']
])->get();

WHERE clause that combines AND and OR with ()

If you want to build a slightly complicated WHERE clause, there are situations where you want to build SQL statements like
AND (xxx OR xxx) and OR (xxx AND xxx)
.

Of course, Laravel’s query builder also supports such SQL statement construction.

Define the closure in the where () or orWhere () method.

$data = $users
->where('role', 5)
->orWhere(function ($query) {
$query->where('gender', 1)
->where('age', '>', 20);
})
->get();

// => SELECT * FROM `users` WHERE `role` = 5 OR (`gender` = 1 AND `age` > 20)

Where Exists clause

Correlated subqueries (subquery side refers to columns of main query) are also supported.

Define the closure in the whereExists () method and describe the required conditions in it.

$data = $users
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();

// => SELECT * FROM `users` WHERE EXISTS (select 1 from `orders` where orders.user_id = users.id)

WHERE clause of JSON column

If the DB you are using is MySQL 5.7 or higher, or PostgreSQL, SQL Server 2016, SQLite 3.9.0, you can also support queries on JSON columns.

The method to use is OK with normal where () or orWhere () etc., but the feature is that it is specified by the arrow operator .

$data = $users
->where('options->language', 'ja')
->where('preferences->dining->meal', 'salad')
->get();

whereJsonContains / orWhereJsonContains

You can also use the whereJsonContains () method. In addition to normal usage, you can also specify multiple values ​​for the condition value.

$data = $users
    ->whereJsonContains('options->language', 'ja')
    ->get();

// specify multiple values
$data = $users
    ->whereJsonContains('options->language', ['ja', 'en'])
    ->get();

* WhereJsonContains () can be used with ver5.6.25 or later. Normal usage is other than SQLite, multiple values ​​can only be used with MySQL and PostgreSQL.

whereJsonLength / orWhereJsonLength

You can specify the data length by using whereJsonLength () method.

$data = $users
->whereJsonLength('team->user', 5)
->get();

$data = $users
->whereJsonLength('team->user', '>', 5)
->get();

The data length is the number of pieces. In the above method example, it means “data with 5 (more) users”.

[{ 
"team" : {
"user" : [
{ "name" : "user01" } ,
{ "name" : "user02" } ,
{ "name" : "user03" } ,
{ "name" : "user04" } ,
{ "name" : "user05" } ,
]
}
} , {
"team" : {
"user" :[
{ "name" : "user001"} ,
{ "name" : "user002" } ,
{ "name" : "user003" } ,
]
}
}]

* WhereJsonLength () is available since ver5.7.

orderBy

Use the orderBy () method to sort the result data by the specified column.

$data = $users->orderBy('id', 'desc')->get();

Specify the column name as the first argument and ascending order ( asc ) or descending order ( desc ) as the second argument .

Simple sort

For simple sorts, dedicated sort methods are also available.

descending order

Use the latest () method to get in descending order.

$data = $users->latest()->get();

ascending order

Use the oldest () method to get in ascending order.

$data = $users->oldest()->get();

As for the above two methods, by default the created date (created_at column) is used as a key for sorting, but you can sort by the specified column by passing the column name as an argument .

Sort by random

You can use the inRandomOrder () method to order the result data in random order.

$data = $users->inRandomOrder()->get();

Grouping (GROUP BY) and filtering (HAVING)

There are many situations where you want to use GROUP BY or HAVING when doing aggregation, but in that case, use the groupBy () method and having () method.

$data = $users
->selectRaw('role, sum(role) AS role_cnt')
->groupBy('role')
->having('role', '>', 5)
->get();

// => SELECT role, sum(role) AS role_cnt FROM `users` GROUP BY `role` HAVING `role` > 5

Of course, you can specify multiple columns in the groupBy () method.

$data = $users
->selectRaw('`role`, sum(role) AS role_cnt')
->groupBy('role', 'gender')
->having('role', '>', 5)
->get();

// => SELECT `role`, sum(role) AS role_cnt FROM `users` GROUP BY `role`, `gender` HAVING `role` > 5

Limit / skip the number of records retrieved

You can also limit the number of result records returned for the condition, or skip (skip) the number of records.

LIMIT

To limit the number of fetched records , use the limit () method.

$data = $users->limit(3)->get();

OFFSET

Use the offset () method to specify the acquisition start position (from which record) to get the result record .

$data = $users
->offset(2)
->limit(3)
->get();

OFFSET is often obtained and used in combination with the LIMIT clause, but in the case of the above example, it means “acquire the second to third records”.

There are also methods called take () and skip () as synonyms for limit () and offset () (the same thing) .

$data = $users
->skip(2)
->take(3)
->get();

skip () is the same as offset () and take () is the same as limit ().

Read also >>  Laravel 5.7 tutorial - make a Post Api

LIMIT and OFFSET are familiar to those who know MySQL, but skip () and take () may be easier to understand for those who are not. Whichever you use, you get the same result.

Change the search condition by the judgment expression

The
flow like “Add this parameter to the search condition when that parameter comes in the request” is a common pattern.

Laravel’s query builder also supports SQL statement assembly with that pattern.

Use the when () method, specify the result of the judgment expression (true or fale) in the first argument, specify the closure in the second argument, and define the processing when it is true in the closure.

$user_id = 8;

$data = $users
->when($user_id, function ($query) use ($user_id) {
return $query->where('id', $user_id);
})
->get();

In the above example, the condition
“If $ user_id is true (that is, it contains a value), specify the id column by that value”
is added.

If it is false, it will be passed through the closure, so the behavior will be to get all records.

By the way, you can also describe the condition when it is false. In that case, define a closure in the third argument as well, and describe the processing in the case of false there.

$user_id = null;

$data = $users
->when($user_id, function ($query) use ($user_id) {
return $query->where('id', $user_id);
}, function ($query) {
return $query->limit(3);
})
->get();

With this, when it is false, it becomes the behavior that only 3 records are acquired instead of all records.

INSERT

Use the insert () method to register data (insert records) in the table .

$data = $users
->insert(
[
'name' => 'test11',
'email' => 'test11@test.com',
'password' => '123456',
'role' => 5
]
);

One data is stored in one array (property name is used as column name).

Multiple records can be inserted with one SQL statement by storing multiple records (in the form of associative array) in one array.

$data = $users
->insert([
[
'name' => 'test11',
'email' => 'test11@test.com',
'password' => '123456',
'role' => 5
],
[
'name' => 'test12',
'email' => 'test12@test.com',
'password' => '7891011',
'role' => 5
]
]);

Get auto increment id after insert

If the table has an auto increment ID (auto increment = AUTO_INCREMENT), you can get the auto increment ID in the return value by inserting a record with the insertGetId () method.

$data = $users
->insertGetId(
[
'name' => 'test11',
'email' => 'test11@test.com',
'password' => '123456',
'role' => 5
]
);

Note that when using the insertGetId () method in PostgreSQL, the auto-increment id column name must be id by default.

If the auto-incremental ID column name is something other than id, you must specify that column name in the second argument.

UPDATE

Use the update () method to update the record .

$data = $users
->where('id', 1)
->update([
'name' => 'test1-2',
]);

Specify the record to be updated using where clause, and specify the record (column / value) to be changed in the array with the update () method.

Update JSON column

Of course, the JSON column can also be updated. In that case, use the arrow operator (->) to specify the appropriate change target.

$data = $users
->where('id', 1)
->update(['options->enabled' => true]);

A simple update that increases or decreases the value of a column

For example, you often have a count in a column, and you want to increase or decrease that value by one, or increase or decrease it by any number.

At that time, you can easily implement it by using the increment () and decrement () methods.

$data = $users->where('id', 1)->increment('count');
$data = $users->where('id', 1)->increment('count', 3);

In the above, the value of count column is added by increment () method .

If you specify a value for the second argument, the number will be updated, and if you do not specify it, 1 will be added and updated.

$data = $users->where('id', 1)->decrement('count');
$data = $users->where('id', 1)->decrement('count', 3);

In the above, the value of count column is subtracted by decrement () method .

In the same way, if you specify a value for the second argument, the number will be updated, and if you do not specify it, 1 will be subtracted and updated.

By the way, the above updates one record, but it is also possible to update the target record collectively by changing the where clause.

And the place where the increment () and decrement () methods can be used is not only to increase / decrease but also to update other than increase / decrease by passing other normal update information to the third argument in an array.

$data = $users->where('id', 1)->increment('count', 3, ['name' => 'test1-2']);

By doing the above, it is possible to perform normal update to other columns while increasing or decreasing.

DELETE

Use the delete () method to delete a record from the table .

$users->where('id', 1)->delete();

Although only one is deleted in the above, it is also possible to delete all at once by changing the where clause.

TRUNCATE

The delete () method simply deletes the records, so if you delete all the records, the auto increment ID will not be reset.

If you want to delete the entire table and reset the auto increment , use the truncate () method.

$users->truncate();

Pessimistic lock

With Laravel’s query builder, you can lock pessimistically with a SELECT statement.

Shared lock

By using the sharedLock () method, you can prevent the row being SELECTed from being updated until the transaction is committed.

$data = $users->where('id', 1)->sharedLock()->get();

Exclusive lock

By using the lockForUpdate () method, you can prevent other shared locks from happening to update or SELECT the record.

$data = $users->where('id', 1)->lockForUpdate()->get();

Summary

In conclusion, As you can see, Laravel’s query builder has a wealth of features, and most SQL statements can be assembled. (I think it would be unnecessary to directly write SQL statements if the number of SELECTs in the aggregation system was improved.)

Also, knowing the grammar of the SQL statement itself will help you to understand these functions further.

We are adding new features even now, and I think it will be even more useful in the future, so please give it a try.

LEAVE A REPLY

Please enter your comment!
Please enter your name here