Use advanced: realize advanced functions through query builder
brief introduction
The database query builder provides a convenient flow interface for creating and executing database queries. The query builder can be used to perform most database operations in the application, and can work on all database systems supported by Larave. Note: Stream interface is a design mode. For more information on the design and use of stream interface mode, see this tutorial: PHP Design Pattern Series - Stream Interface Pattern 。
The Larravel query builder uses PDO parameter binding to avoid SQL injection attacks. It no longer needs to filter strings passed by binding. Get result set
Get all rows from one table
We can go from DB Facade table Method start, table Method returns a streaming query builder instance for a given table, which allows you to link multiple constraints on the query and return the final query result. In this example, we use get Method to obtain all records in the table:
<? php namespace App\Http\Controllers; use Illuminate\Support\Facades\DB; use App\Http\Controllers\Controller; class UserController extends Controller { /** *Show user list * * @return Response */ public function index() { $users = DB::table('users')->get(); return view('user.index', ['users' => $users]); } }
get Method returns the Illuminate\Support\Collection , each result is PHP StdClass Object instance:
You can access the value of a field as you access the properties of an object:
foreach ($users as $user) { echo $user->name; }
Get a row/column from a table
If you just want to get a row of data from the data table, you can use first Method, which will return a single StdClass Object:
$user = DB::table('users')->where('name', 'John')->first(); echo $user->name;
If you don't need a complete line, you can use value Method to obtain a single value from the result. This method will directly return the value of the specified column:
$email = DB::table('users')->where('name', 'John')->value('email');
Get the list of data column values
If you want to get an array containing a single column value, you can use pluck Method, in this example, we get the role title array:
$titles = DB::table('roles')->pluck('title'); foreach ($titles as $title) { echo $title; }
You can also specify a custom key for the column value in the return array (the custom key must be the name of other fields and columns in the table, or an error will be reported):
$roles = DB::table('roles')->pluck('title', 'name'); foreach ($roles as $name => $title) { echo $title; }
Chunking result set
If you need to process thousands of database records, you can consider using chunk Method. This method obtains a small piece of the result set at a time, and then transfers each piece of data to the closure function for processing. This method is very useful when writing Artisan commands that process a large number of database records. For example, we can process all users Table data is divided into one processing one hundred Sub block of records: DB::table('users')->orderBy('id')->chunk(100, function($users) { foreach ($users as $user) { // } });
You can return false To terminate the operation of the module: DB::table('users')->orderBy('id')->chunk(100, function($users) { //Process Result Set return false; });
Aggregate function
The query builder also provides several aggregation methods, such as count , max , min , avg and sum , you can call these methods after constructing the query: $users = DB::table('users')->count(); $price = DB::table('orders')->max('price');
Of course, you can combine other query clauses and aggregate functions to build queries: $price = DB::table('orders') ->where('finalized', 1) ->avg('price');
Select
Specify Query Clause
Of course, we don't always want to get all the columns of the data table select Method, you can specify a custom select Clause:
$users = DB::table('users')->select('name', 'email as user_email')->get();
distinct Method allows you to force a query to return a non duplicate result set:
$users = DB::table('users')->distinct()->get();
If you already have a query builder instance and want to add a query column to the existing select clause, you can use addSelect method:
$query = DB::table('users')->select('name'); $users = $query->addSelect('age')->get();
Native expression
Sometimes you want to use native expressions in the query. These expressions will be injected into the query in the form of strings, so be careful to avoid SQL injection. To create a native expression, you can use DB::raw method: $users = DB::table('users') ->select(DB::raw('count(*) as user_count, status')) ->where('status', '', 1) ->groupBy('status') ->get();
native method
In addition to using DB::raw In addition, you can also use the following methods to insert native expressions into different parts of the query.
selectRaw
selectRaw Method can be used instead select(DB::raw(...)) , this method receives an optional binding array as the second parameter:
$orders = DB::table('orders') ->selectRaw('price * ? as price_with_tax', [1.0825]) ->get();
whereRaw / orWhereRaw
whereRaw and orWhereRaw Method can be used to inject native where Clause to query. These two methods receive an optional binding array as the second parameter:
$orders = DB::table('orders') ->whereRaw('price > IF(state = "TX", ?, 100)', [200]) ->get();
havingRaw / orHavingRaw
havingRaw and orHavingRaw Method can be used to set a native string as having Value of clause:
$orders = DB::table('orders') ->select('department', DB::raw('SUM(price) as total_sales')) ->groupBy('department') ->havingRaw('SUM(price) > 2500') ->get();
orderByRaw
orderByRaw Method can be used to set a native string as order by Value of clause:
$orders = DB::table('orders') ->orderByRaw('updated_at - created_at DESC') ->get();
Join
The query builder can also be used to write connection statements. The following figure shows several connection types of SQL:
Internal connection (equivalent connection)
To implement a simple "inner join", you can use the join Method, passed to join The first parameter of the method is the table name you need to connect to. The remaining parameters are the column constraints specified for the connection. Of course, as you can see, you can connect multiple 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 connection
If you want to perform "left connection" instead of "internal connection", you can use leftJoin method. This method and join The method is the same:
$users = DB::table('users') ->leftJoin('posts', 'users.id', '=', 'posts.user_id') ->get();
Cross connect
To perform Cross Connect, you can use the crossJoin Method, just pass the table name you want to cross connect to this method. Cross connect generates a Cartesian product between the first table and the connected table:
$users = DB::table('sizes') ->crossJoin('colours') ->get();
Advanced connection statement
You can also specify more advanced join clauses, passing a closure to join Method as the second parameter, the closure will receive a JoinClause Objects are used to specify join Clause constraints:
DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id')->orOn(...); }) ->get();
If you want to use the "where" style clause in the connection, you can use it in the query where and orWhere method. These methods compare columns and values instead of columns and columns:
DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.user_id', '>', 5); }) ->get();
Union
The query builder also provides a shortcut for "combining" two queries. For example, you can first create a query and then use union Method to associate it with the second query: $first = DB::table('users') ->whereNull('first_name'); $users = DB::table('users') ->whereNull('last_name') ->union($first) ->get();
Note: unionAll The method is also effective, and union Use the same way.
Where clause
Simple Where clause
Use the where Method can be added where Clause into query, call where In the most basic way, three parameters need to be passed. The first parameter is the column name, the second parameter is an operator supported by any database system, and the third parameter is the value to be compared for this column. For example, the following is a query to verify whether the value of the "votes" column is equal to 100:
$users = DB::table('users')->where('votes', '=', 100)->get();
For convenience, if you simply compare the column value with the given value, you can directly use the value as where The second parameter of the method:
$users = DB::table('users')->where('votes', 100)->get();
Of course, you can also use other operators to write 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 where Function:
$users = DB::table('users')->where([ ['status', '=', '1'], ['subscribed', '', '1'], ])->get();
Or statement
You can link multiple where Constraints are linked together, or you can add or Clause to query, orWhere Methods and where The method receives the same parameters: $users = DB::table('users') ->where('votes', '>', 100) ->orWhere('name', 'John') ->get();
More Where Clauses
whereBetween
whereBetween Method to verify whether the column value is between the given values:
$users = DB::table('users') ->whereBetween('votes', [1, 100])->get();
whereNotBetween
whereNotBetween Method validation column value is not between the given values:
$users = DB::table('users') ->whereNotBetween('votes', [1, 100]) ->get();
whereIn/whereNotIn
whereIn Method to verify whether the value of the given column is in the given array:
$users = DB::table('users') ->whereIn('id', [1, 2, 3]) ->get();
whereNotIn Method to verify that the value of the given column is not in the given array:
$users = DB::table('users') ->whereNotIn('id', [1, 2, 3]) ->get();
whereNull/whereNotNull
whereNull Method to verify that the value of the given column is NULL ：
$users = DB::table('users') ->whereNull('updated_at') ->get();
whereNotNull Method to verify that the value of the given column is not NULL ：
$users = DB::table('users') ->whereNotNull('updated_at') ->get();
whereDate / whereMonth / whereDay / whereYear
whereDate Method is used to compare field values and dates:
$users = DB::table('users') ->whereDate('created_at', '2016-10-10') ->get();
whereMonth The method is used to compare the field value with the specified month of the year:
$users = DB::table('users') ->whereMonth('created_at', '10') ->get();
whereDay Method is used to compare the field value with the specified date in the month:
$users = DB::table('users') ->whereDay('created_at', '10') ->get();
whereYear The method is used to compare field values with the specified year:
$users = DB::table('users') ->whereYear('created_at', '2017') ->get();
whereColumn
whereColumn Method is used to verify whether two fields are equal:
$users = DB::table('users') ->whereColumn('first_name', 'last_name') ->get();
You can also pass a comparison operator to the method:
$users = DB::table('users') ->whereColumn('updated_at', '>', 'created_at') ->get();
You can also pass multiple conditional arrays to whereColumn Method, these conditions are passed and Operator connection:
$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 groups. The Larravel query builder can also handle these. To begin, let's look at an example of grouping constraints in parentheses:
DB::table('users') ->where('name', '=', 'John') ->orWhere(function ($query) { $query->where('votes', '>', 100) ->where('title', '', 'Admin'); }) ->get();
As you can see, passing closures to orWhere Method to construct a query builder to start a constraint grouping. The closure will obtain a query builder instance used to set the constraints contained in parentheses. The above statement is equivalent to the following SQL:
select * from users where name = 'John' or (votes > 100 and title 'Admin')
Where exists clause
whereExists Method allows you to write where exists SQL clause, whereExists Method receives a closure parameter, which obtains a query builder instance to allow you to define the query placed in the "exists" clause:
DB::table('users') ->whereExists(function ($query) { $query->select(DB::raw(1)) ->from('orders') ->whereRaw('orders.user_id = users.id'); }) ->get();
The above query is equivalent to the following SQL statement:
select * from users where exists ( select 1 from orders where orders.user_id = users.id )
JSON Where clause
Larvel also supports the use of operators on databases that provide JSON field types (currently MySQL 5.7 and PostgresSQL) -> Get the specified JSON field value:
$users = DB::table('users') ->where('options->language', 'en') ->get(); $users = DB::table('users') ->where('preferences->dining->meal', 'salad') ->get();
Sort, group, limit
orderBy
orderBy Method allows you to sort the result set by a given field, orderBy The first parameter of should be the field you want to sort, and the second parameter controls the direction of sorting—— asc or desc ：
$users = DB::table('users') ->orderBy('name', 'desc') ->get();
latest / oldest
latest and oldest The method allows you to sort the results by date. By default, the result set is based on created_at Fields can be sorted, or you can pass in the fields you want to sort as field names:
$user = DB::table('users') ->latest() ->first();
inRandomOrder
inRandomOrder The method can be used to sort the query result set randomly. For example, you can use this method to obtain a random user:
$randomUser = DB::table('users') ->inRandomOrder() ->first();
groupBy / having
groupBy and having Method is used to group result sets, having Methods and where The method is similar to:
$users = DB::table('users') ->groupBy('account_id') ->having('account_id', '>', 100) ->get();
about having For more advanced usage of, see havingRaw method.
skip / take
To limit the number of result sets returned by the query, or skip the given number of results in the query, you can use skip and take method:
$users = DB::table('users')->skip(10)->take(5)->get();
As an alternative, you can also use limit and offset method:
$users = DB::table('users') ->offset(10) ->limit(5) ->get();
Conditional clause
Sometimes you might want something to be true Only when the conditional clause is applied to the query. For example, you may only apply the given value if it exists in the request where Statement, which can be accessed by when Method implementation:
$role = $request->input('role'); $users = DB::table('users') ->when($role, function ($query) use ($role) { return $query->where('role_id', $role); }) ->get();
when Method can only be used when the first parameter is true If the first parameter is false , the closure is not executed.
You can pass another closure as when The third parameter of the method, the closure will false Is executed. To demonstrate how to use this feature, let's configure the default sorting of a query:
$sortBy = null; $users = DB::table('users') ->when($sortBy, function ($query) use ($sortBy) { return $query->orderBy($sortBy); }, function ($query) { return $query->orderBy('name'); }) ->get();
Insert
The query builder also provides insert Method is used to insert records into the data table. insert Method receives the field name and field value in the form of an array for insertion:
DB::table('users')->insert( ['email' => ' john@example.com ', 'votes' => 0] );
You can even insert multiple records by passing in multiple arrays at one time. Each array represents the record to be inserted into the data table:
DB::table('users')->insert([ ['email' => ' taylor@example.com ', 'votes' => 0], ['email' => ' dayle@example.com ', 'votes' => 0] ]);
Self increasing ID
If the data table has a self increasing ID, use insertGetId Method to insert a record and return the ID value:
$id = DB::table('users')->insertGetId( ['email' => ' john@example.com ', 'votes' => 0] );
Note: When PostgresSQL is used insertGetId By default, the self increasing column is named as id. If you want to get the ID from other "sequences", you can pass the sequence name as the second parameter to insertGetId method.
Update
Of course, in addition to inserting records into the database, the query builder can also use the update Method to update an existing record. update Methods and insert In the same way, receive the key value pair array of field name and field value, and the corresponding field name is the column to be updated. You can use where Clause update Query to constrain:
DB::table('users') ->where('id', 1) ->update(['votes' => 1]);
Update JSON fields
When updating JSON fields, you need to use -> Syntax access the corresponding value on the JSON object. This operation can only be used for databases that support JSON field types:
DB::table('users') ->where('id', 1) ->update(['options->enabled' => true]);
Increase/decrease
The query builder also provides convenience for increasing or decreasing the value corresponding to a given field name. Compared to writing update Statement, which is a shortcut and provides a better experience and test interface.
Both methods receive at least one parameter: the column to be modified. The second parameter is optional and is used to control the number of column value increases/decreases.
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 additional columns to update during the operation:
DB::table('users')->increment('votes', 1, ['name' => 'John']);
Delete
Of course, the query builder can also delete Method to delete a record from the table. You can call delete Method by adding where Clause to add constraints:
DB::table('users')->delete(); DB::table('users')->where('votes', '>', 100)->delete();
If you want to clear the whole table, that is, delete all columns and set the auto increment ID to 0, you can use truncate method:
DB::table('users')->truncate();
Pessimistic lock&optimistic lock
Pessimistic Lock, as its name implies, is very pessimistic. Every time I go to get data, I think others will modify it, so every time I get data, I lock it, so that others will block it until they get the lock. Many of these locking mechanisms are used in traditional relational databases, such as row locks, table locks, read locks, and write locks, which are locked before operations.
Optimistic Lock, as its name implies, is very optimistic. Every time you go to get the data, you think that others will not modify it, so you will not lock it. But when you update it, you will judge whether others have updated the data during this period, and you can use the version number and other mechanisms. Optimistic locks are applicable to multi read applications, which can improve throughput. For example, if a database provides write_condition The mechanisms are actually optimistic locks.
Let's take a look at the use of pessimistic locks and optimistic locks in Laravel:
Pessimistic lock use
Larravel query builder provides some methods to help you select Statement. Can be used in queries sharedLock The shared lock can prevent the selected row from being modified until the transaction is committed
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
The above query is equivalent to the following SQL statement:
select * from `users` where `votes` > '100' lock in share mode
In addition, you can also use lockForUpdate method. The "for update" lock prevents the selected row from being modified or deleted by other shared locks:
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();
The above query is equivalent to the following SQL statement:
select * from `users` where `votes` > '100' for update
for update And lock in share mode Both are used to ensure that the selected record value cannot be updated (locked) by other transactions. The difference between the two is lock in share mode It will not block other transactions to read the value of the locked row record, and for update It will block other locking reads from reading locked rows (non locking reads can still read these records, lock in share mode and for update All are locked reads).
So it's more abstract. Let's take a counter example: read a value in one statement, and then update the value in another statement. use lock in share mode In this case, two transactions are allowed to read the same initialization value, so the final counter value after executing two transactions is+1; If you use for update In this case, the reading of the record value by the second transaction will be locked until the execution of the first transaction is completed, so the final result of the counter is+2.
Optimistic lock usage
Optimistic locks are mostly implemented based on the data version recording mechanism. What is a data version? That is, add a version ID to the data. In database table based version solutions, it is generally achieved by adding a "version" field to the database table.
When reading out data, read out this version number together, and then add one to this version number when updating later. At this time, the version data of the submitted data will be compared with the current version information of the corresponding record in the database table. If the version number of the submitted data is greater than the current version number of the database table, it will be updated, otherwise it will be considered as expired data.
summary
The two types of locks have their own advantages and disadvantages. One cannot be considered as better than the other. For example, optimistic locks are suitable for situations where there are few writes, that is, conflicts are really rare. This saves the cost of locks and increases the overall throughput of the system. However, if there are frequent conflicts, the upper application will constantly retry, which in turn reduces the performance. Therefore, it is appropriate to use pessimistic locks in this case.
