query builder


Database: Query Builder

    Introduction
    • Get results
    • Blocked results
      • Aggregation
    • Query
    • Native expression
    • Joins
    • Unions
    • Where statement
    • Parameter grouping
      • Where Exists syntax
      • JSON Where Syntax
    ##Ordering, Grouping, Limit, & Offset
  • Conditional Statement
  • Insert
  • Update
  • Update JSON
    • Auto increment and auto Minus
    Delete
  • Pessimistic Lock

##

Introduction

Laravel's database query builder provides a convenient interface for creating and running database queries. It can be used to perform most database operations in an application and runs on all supported database systems.

Laravel’s query builder uses PDO parameter binding to protect your application from SQL injection attacks. So there is no need to sanitize the string passed as a binding.

Get results

Get all rows from a data table

You can use the table method on DB facade to start a query. The table method returns a query builder instance for the given table, allowing you to chain more constraints on the query, and finally get the result using the get method: 

<?php
    namespace App\Http\Controllers;
    use Illuminate\Support\Facades\DB;
    use App\Http\Controllers\Controller;
    class UserController extends Controller{  
     /**
     * 显示所有应用程序的用户列表。
     *
     * @return Response
     */   
     public function index()   
      {     
        $users = DB::table('users')->get();        
        return view('user.index', ['users' => $users]);    
       }
   }

The get method returns a result containing Illuminate\Support\Collection where each result is PHP StdClass An instance of the object. You can access the fields as properties of the object to access the value of each column: 

foreach ($users as $user) { 
   echo $user->name;
  }

Get a single row or column from the data table

If you just need to get a single row or column from the data table To get a row of data, you can use the first method. This method returns a StdClass object: 

$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;

If you don't even need the entire row of data, you can use the value method to get a single value from the record. This method will directly return the value of the field: 

$email = DB::table('users')->where('name', 'John')->value('email');

Get the value of a column

If you want to get a collection containing a single column value, you can use pluck method. In the following example, we will get a collection of titles in the role table: 

$titles = DB::table('roles')->pluck('title');
foreach ($titles as $title) { 
   echo $title;
 }

You can also specify custom key values ​​for fields in the returned collection: 

$roles = DB::table('roles')->pluck('title', 'name');
foreach ($roles as $name => $title) {  
  echo $title;
 }

Chunked results

If you need to process thousands of database records, you may consider using the chunk method. This method takes a small piece of the result set at a time and passes it to the Closure function for processing. This method is useful when writing Artisan commands to process thousands of pieces of data. For example, we can cut the entire users table data into a small piece that processes 100 records at a time: 

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

You can do this by returning ## in a closure #false to terminate the continued acquisition of chunked results: 

DB::table('users')->orderBy('id')->chunk(100, function ($users) {   
 // Process the records...    
 return false;
});

If you want to update the database record when chunking the results, the chunked results may be inconsistent with the expected return results. Therefore, when updating records in chunks, it is best to use the

chunkById method. This method will automatically paginate the results based on the record's primary key: 

DB::table('users')->where('active', false) 
   ->chunkById(100, function ($users) {     
      foreach ($users as $user) {        
          DB::table('users')               
           ->where('id', $user->id)                
           ->update(['active' => true]);      
         }  
   });

{Tip} When updating or deleting records inside a block's callback, any changes to the primary key or foreign key may affect the block query. . This may result in records not being included in chunked results.

Aggregation

The query builder also provides various aggregation methods, such as

count , max, min, avg, and sum. You can call any method after constructing the query: 

$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');

Of course, you can also combine these aggregation methods with other query statements: 

$price = DB::table('orders')             
   ->where('finalized', 1)                
   ->avg('price');

Determine whether the record Exists

In addition to using the

count method to determine whether the result of the query condition exists, you can also use the exists and doesntExist methods: 

return DB::table('orders')->where('finalized', 1)->exists();
return DB::table('orders')->where('finalized', 1)->doesntExist();

##Selects

Specify a Select statement

Of course you may not It is not always desirable to obtain all columns from a database table. Using the

select

method, you can customize a select query statement to query the specified field: The 

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

distinct

method will force the query to return The results are not duplicated: 

$users = DB::table('users')->distinct()->get();
If you already have a query builder instance and want to add a field to the existing query statement, then you can use the

addSelect

method: 

$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

Native expressions

Sometimes you may need to use native expressions in queries. You can create a raw expression using

DB::raw

: 

$users = DB::table('users')               
      ->select(DB::raw('count(*) as user_count, status'))                     
      ->where('status', '<>', 1)                     
      ->groupBy('status')                    
       ->get();

{Tip} The raw expression will be injected into the query as a string, so you should be careful Use to avoid creating SQL injection vulnerabilities.

Native method

You can use the following method instead

DB::raw

, inserting native expressions into various parts of the query.

selectRaw

selectRaw method can be used instead of select(DB::raw(...)). The second parameter of this method is optional, and the value is an array of bound parameters: 

$orders = DB::table('orders')            
    ->selectRaw('price * ? as price_with_tax', [1.0825])                
    ->get();

whereRaw / orWhereRaw

## The #whereRaw and orWhereRaw methods inject the native where into your query. The second parameter of these two methods is still optional, and the value is still an array of bound parameters: 

$orders = DB::table('orders')          
      ->whereRaw('price > IF(state = "TX", ?, 100)', [200])                
      ->get();

##havingRaw / orHavingRaw

# The ##havingRaw

and

orHavingRaw methods can be used to set a raw string to the value of the having statement: 

$orders = DB::table('orders')         
       ->select('department', DB::raw('SUM(price) as total_sales'))                
       ->groupBy('department')                
       ->havingRaw('SUM(price) > ?', [2500])                
       ->get();

## The #orderByRaw

orderByRaw method can be used to set a raw string to the value of the

order by

clause: 

$orders = DB::table('orders')            
    ->orderByRaw('updated_at - created_at DESC')                
    ->get();

Joins

Inner Join Clause

The query constructor can also be written
join

method. To perform basic

"inner joining", you can use the

join method on the query builder instance. The first parameter passed to the join
method is the name of the table you want to join, while the other parameters use the field constraints that specify the join. You can also join multiple data tables in a single query: 

$users = DB::table('users')         
   ->join('contacts', 'users.id', '=', 'contacts.user_id')            
   ->join('orders', 'users.id', '=', 'orders.user_id')            
   ->select('users.*', 'contacts.phone', 'orders.price')            
   ->get();
Left Join statement

If you want to use "left join" or "right join" instead of " "Inner Join", you can use the
leftJoin

or

rightJoin

method. These two methods have the same usage as the join method: 

$users = DB::table('users')  
       ->leftJoin('posts', 'users.id', '=', 'posts.user_id')            
       ->get();     $users = DB::table('users')            
       ->rightJoin('posts', 'users.id', '=', 'posts.user_id')            
       ->get();
Cross Join statement

Use the
crossJoin

method as you want The name of the table to be connected is called "cross connection". A cross join generates a Cartesian product between the first table and the joined table: 

$users = DB::table('sizes')         
   ->crossJoin('colours')            
   ->get();

Advanced Join Statement

You can specify more advanced join statements . For example, pass a
closure

as the second parameter of the

join

method. This Closure receives a JoinClause object, thereby specifying the constraints specified in the join
statement: 

DB::table('users')    
    ->join('contacts', function ($join) {         
       $join->on('users.id', '=', 'contacts.user_id')->orOn(...);    
         })        
    ->get();
If you want to To use "where" style statements on connections, you can use the where and

orWhere

methods on connections. These methods compare columns to values ​​instead of columns to columns: 

DB::table('users')     
   ->join('contacts', function ($join) {           
    $join->on('users.id', '=', 'contacts.user_id')               
   ->where('contacts.user_id', '>', 5);     
    })       
   ->get();
Subjoin query

You can use
joinSub

The ,

leftJoinSub

and rightJoinSub methods join a query as a subquery. Each of their methods receives three parameters: a subquery, a table alias, and a closure that defines the associated fields: 

$latestPosts = DB::table('posts')         
        ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))                   
        ->where('is_published', true)                   
        ->groupBy('user_id');$users = DB::table('users')        
        ->joinSub($latestPosts, 'latest_posts', function($join) {          
             $join->on('users.id', '=', 'latest_posts.user_id');     
           })
        ->get();

Unions

The query builder also provides a shortcut to "union" two queries. For example, you can create a query and then union it with a second query using the union method: 

$first = DB::table('users')         
   ->whereNull('first_name');$users = DB::table('users')            
   ->whereNull('last_name')            
   ->union($first)            
   ->get();

{Tip} You can also use unionAll Method, usage union The method is the same.

Where statement

Simple Where statement

In constructing a where query instance, you can use the where method. The most basic way to call where is to pass three parameters: the first parameter is the column name, the second parameter is any operator supported by the database system, and the third parameter is the column to be compared. value.

For example, the following is a query to verify that the value of the "votes" field is equal to 100: 

$users = DB::table('users')->where('votes', '=', 100)->get();

For convenience, if you are simply comparing whether the column value is equal to a given value, you can The value is directly used as the second parameter of the where method: 

$users = DB::table('users')->where('votes', 100)->get();

Of course, you can also use other operators to write the where clause: 

$users = DB::table('users')           
     ->where('votes', '>=', 100)                
     ->get();$users = DB::table('users')                
     ->where('votes', '<>', 100)                
     ->get();$users = DB::table('users')                
     ->where('name', 'like', 'T%')                
     ->get();

You can also pass the condition array to the where function: 

$users = DB::table('users')->where([
    ['status', '=', '1'],   
     ['subscribed', '<>', '1'],
  ])->get();

Or statement

You can chain the where constraints together, You can also add or phrases to the query. The orWhere method receives the same parameters as the where method: 

$users = DB::table('users')         
      ->where('votes', '>', 100)                    
      ->orWhere('name', 'John')                    
      ->get();

Other Where statements

whereBetween

whereBetween The method verifies whether the field value is between the two given values: 

$users = DB::table('users')                 
   ->whereBetween('votes', [1, 100])->get();

whereNotBetween

whereNotBetween method verifies whether the field value is outside the given two values: 

$users = DB::table('users')          
       ->whereNotBetween('votes', [1, 100])                    
       ->get();

whereIn / whereNotIn

whereIn The value of the method verification field must exist in the specified array, :

$users = DB::table('users')     
      ->whereIn('id', [1, 2, 3])                    
      ->get();

whereNotIn The value of the method verification field Must not exist in the specified array: 

$users = DB::table('users')                 
   ->whereNotIn('id', [1, 2, 3])                    
   ->get();

whereNull / whereNotNull

##whereNull The method verifies that the specified field must be NULL:

$users = DB::table('users')                 
   ->whereNull('updated_at')                    
   ->get();

whereNotNull The method verifies that the specified field must not be NULL:

        $users = DB::table('users')               
             ->whereNotNull('updated_at')                    
             ->get();

whereDate / whereMonth / whereDay / whereYear / whereTime

whereDate method is used to compare the field value with the given date: 

        $users = DB::table('users')           
             ->whereDate('created_at', '2018-09-08')                
             ->get();

whereMonth method is used to compare the field value with The specified month of the year: 

$users = DB::table('users')          
      ->whereMonth('created_at', '9')                
      ->get();

whereDay method is used to compare the field value with the specified day of the month: 

  $users = DB::table('users')           
       ->whereDay('created_at', '8')                
       ->get();

whereYear method Used to compare the field value with the specified year: 

$users = DB::table('users')           
     ->whereYear('created_at', '2018')                
     ->get();

whereTime The method is used to compare the field value with the specified time (hours, minutes and seconds): 

     $users = DB::table('users')              
       ->whereTime('created_at', '=', '11:20:45')                
       ->get();

whereColumn

whereColumn The method is used to compare whether the values ​​​​of two fields are equal: 

        $users = DB::table('users')             
           ->whereColumn('first_name', 'last_name')                
           ->get();

You can also pass in a comparison operator: 

$users = DB::table('users')             
   ->whereColumn('updated_at', '>', 'created_at')                
   ->get();

whereColumn You can also pass arrays linked using the and operator: 

$users = DB::table('users')             
   ->whereColumn([                
       ['first_name', '=', 'last_name'],                    
       ['updated_at', '>', 'created_at']              
  ])->get();

Parameter Grouping

Sometimes you need to create more advanced where clauses, such as "where exists" or nested parameter grouping. Laravel's query builder can handle this as well. Next, let's look at an example of grouping constraints within parentheses: 

DB::table('users')        
    ->where('name', '=', 'John')            
    ->where(function ($query) {               
     $query->where('votes', '>', 100)                   
        ->orWhere('title', '=', 'Admin');           
     })          
   ->get();

You can see that a query construct is built by a

Closure write where method Container to constrain a group. This Closure receives a query instance that you can use to set the constraints that should be included. The above example will generate the following SQL:

select * from users where name = 'John' and (votes > 100 or title = 'Admin')

{Tip} You should call this group using

orWhere to avoid unexpected application global effects.

Where Exists statement

whereExists method allows you to use the where exists SQL statement . The whereExists method accepts a Closure parameter. The whereExists method accepts a Closure parameter. This closure obtains a query builder instance allowing you to define the exists Query in the sentence: 

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

The above query will generate the following SQL statement: 

select * from users
where exists (
    select 1 from orders where orders.user_id = users.id)

JSON Where statement

Laravel also supports querying JSON type fields (only on databases that support JSON type). Currently, this feature only supports MySQL 5.7, PostgreSQL, SQL Server 2016, and SQLite 3.9.0 (with the JSON1 extension). Use the -> operator to query JSON data: 

$users = DB::table('users')            
    ->where('options->language', 'en')                
    ->get();$users = DB::table('users')                
    ->where('preferences->dining->meal', 'salad')                
    ->get();

You can also use whereJsonContains to query JSON arrays: 

$users = DB::table('users')          
      ->whereJsonContains('options->languages', 'en')                
      ->get();

MySQL and PostgreSQL whereJsonContains Can support multiple values: 

$users = DB::table('users')           
     ->whereJsonContains('options->languages', ['en', 'de'])                
     ->get();

You can use whereJsonLength to query the length of the JSON array: 

$users = DB::table('users')          
      ->whereJsonLength('options->languages', 0)                
      ->get();$users = DB::table('users')                
      ->whereJsonLength('options->languages', '>', 1)                
      ->get();

Ordering, Grouping, Limit, & Offset

orderBy

orderBy method allows you to pass Sorts the result set by a given field. The first parameter of orderBy should be the field you want to sort, and the second parameter controls the direction of sorting, which can be asc or desc:

The 
$users = DB::table('users')           
     ->orderBy('name', 'desc')                
     ->get();

latest / oldest

latest and oldest methods allow you to sort by date easily. By default it uses the created_at column as the sort by. Of course, you can also pass custom column names: 

$user = DB::table('users')              
  ->latest()                
  ->first();

inRandomOrder

inRandomOrder method is used to randomly order the results. For example, you can use this method to find a random user. 

$randomUser = DB::table('users')          
      ->inRandomOrder()                
      ->first();

groupBy / having

groupBy and having methods can group the results. The usage of the having method is very similar to the where method: 

$users = DB::table('users')             
   ->groupBy('account_id')                
   ->having('account_id', '>', 100)                
   ->get();

You can pass multiple parameters to the groupBy method: 

$users = DB::table('users')             
   ->groupBy('first_name', 'status')                
   ->having('account_id', '>', 100)                
   ->get();

For more advanced having syntax, see the havingRaw method.

skip / take

To limit the number of results returned, or to skip a specified number of results, you can use skip and take method: 

$users = DB::table('users')->skip(10)->take(5)->get();

Or you can also use limit and offset method: 

$users = DB::table('users')             
   ->offset(10)                
   ->limit(5)                
   ->get();

Conditional Statements

Sometimes you may want a clause to only execute a query if a certain condition is true. For example, you might only apply a where statement if a given value exists in the request. You can do this by using the when method: 

$role = $request->input('role');$users = DB::table('users')             
   ->when($role, function ($query, $role) {              
         return $query->where('role_id', $role);             
        })                
    ->get();

when The method will only execute the given closure when the first parameter is true Bag. If the first parameter is false, then this closure will not be executed

You can pass another closure as the third parameter of the when method . This closure will be executed if the first parameter is false. To illustrate how to use this feature, let's configure the default ordering for a query: 

$sortBy = null;$users = DB::table('users')         
       ->when($sortBy, function ($query, $sortBy) {                
           return $query->orderBy($sortBy);               
          }, function ($query) {             
          return $query->orderBy('name');         
         })               
       ->get();

INSERT

Query Construction The processor also provides the insert method for inserting records into the database. The insert method receives field names and field values ​​in the form of arrays for insertion operations: 

DB::table('users')->insert(    ['email' => 'john@example.com', 'votes' => 0]);

You can even pass an array to the insert method to insert multiple records into the table Medium

DB::table('users')->insert([
    ['email' => 'taylor@example.com', 'votes' => 0],    
    ['email' => 'dayle@example.com', 'votes' => 0]
  ]);

Auto-increment ID

If the data table has an auto-increment ID, use the insertGetId method to insert the record and return the ID value

$id = DB::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
  );

{Note} When using PostgreSQL, the insertGetId method will default to id as the name of the auto-increment field. If you want to get the ID from another "sequence", you can pass the field name as the second parameter to the insertGetId method.

Update

Of course, in addition to inserting records into the database, the query builder can also pass update Method updates existing records. The update method is the same as the insert method, accepting an array containing the fields and values ​​to be updated. You can constrain the update query through the where clause: 

DB::table('users')         
   ->where('id', 1)            
   ->update(['votes' => 1]);

Update or add

Sometimes you may Want to update an existing record in the database, or create a matching record if one does not exist. In this case, the updateOrInsert method can be used. updateOrInsert The method accepts two parameters: a condition array for finding records, and an array of key-value pairs containing the record to be updated.

updateOrInsert The method will first try to find a matching database record using the key and value pair of the first parameter. If the record exists, use the value in the second parameter to update the record. If the record is not found, a new record will be inserted and the updated data is a collection of two arrays: 

DB::table('users')  
  ->updateOrInsert(      
    ['email' => 'john@example.com', 'name' => 'John'],        
    ['votes' => '2']   
  );

Update JSON field

When updating a JSON field, you can use the -> syntax to access the corresponding value in the JSON object. This operation only supports MySQL 5.7: 

DB::table('users')       
     ->where('id', 1)            
     ->update(['options->enabled' => true]);

Increment and Decrement

The query constructor also provides convenient methods for incrementing or decrementing a given field. This method provides a more expressive and concise interface than manually writing update statements.

Both methods receive at least one parameter: the column that needs to be modified. The second parameter is optional and controls the amount by which the column is incremented or decremented: 

DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);

You can also specify the fields to be updated during the operation: 

DB::table('users')->increment('votes', 1, ['name' => 'John']);

Delete

The query builder can also delete records from the table using the delete method. Before using delete, you can add a where clause to constrain delete Syntax: 

DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();

If you need to clear the table, you can use truncate method, which will delete all rows and reset the auto-incremented ID to zero: 

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

##Pessimistic lock

The query builder also contains some functions that can help you implement "pessimistic locking" on the

select syntax. If you want to implement a "shared lock" in a query, you can use the sharedLock method. Shared locks prevent selected data columns from being tampered with until the transaction is committed: 

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();

Alternatively, you can use the

lockForUpdate method. Using the "update" lock can prevent rows from being modified or selected by other shared locks: 

DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

This article was first published on the
LearnKu.com website.
←
→