Query Builder
brief introduction
Swoft's database query builder provides a convenient interface for creating and running database queries. It can be used to perform most database operations in the application and can run on all supported database systems.
Swoft's query builder uses PDO parameter binding to protect your application from SQL injection attacks. Therefore, it is not necessary to clean up the strings passed as bindings.
You can use DB::table('xxxx ') to get a Builder object, or you can use
The return results of the two methods of Builder:: new() - > from ('xxx ') are the same. The builder object will not allocate connections and will only be obtained from the connection pool when executing sql
Get results
Get all rows from a data table
You can use the table method on the DB to start the query. The table method returns a query constructor instance for a given table, allowing you to call more constraints on the query chain, and finally use the get method to obtain the results:
// sql = select * from `user`$users = DB::table('user')->get();The get method returns a result containing a Collection, where each result is an instance of the StdClass object. You can access fields as properties of objects to access the values of each column:
foreach ($users as $user) { echo $user->name;}You can also use all methods of Collection, which provides powerful object manipulation methods.
If you want to query all the data faster, you can use cursor method, and the bottom layer adopts yield implementation. Each of these results is an instance of the StdClass object
$users = DB::table('user')->cursor(); foreach ($users as $user){ echo $user->name; }Get a single row or column from a data table
If you only need to get one row of data from the data table, you can use the first method. This method returns an StdClass object:
$user = DB::table('user')->where('name', 'Sakura')->first();if ($user) { echo $user->name; }If you don't even need a whole 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 this field:
$name = DB::table('users')->where('name', 'Sakura')->value('name');Gets the value of a column
If you want to get a collection containing single column values, you can use the plug method. In the following example, we will get the collection of titles in the role table
$titles = DB::table('roles')->pluck('title');foreach ($roles as $title) { echo $title;}You can also specify the custom key value of the field in the returned collection:
$roles = DB::table('users')->pluck('email', 'name');foreach ($roles as $name => $email) { echo $email;}Block result
If you need to process thousands of database records, you can consider using the chunk method. This method obtains a small piece of the result set at a time and passes it to the closure function for processing. It is applicable when repairing data. For example, we can cut all user table data into a small piece that processes 100 records at a time:
DB::table('users')->orderBy('id')->chunk(100, function (\Swoft\Stdlib\Collection $users) { foreach ($users as $user) { echo $user->name; }});You can terminate and continue to obtain blocking results by returning false in the closure:
DB::table('users')->orderBy('id')->chunk(100, function (\Swoft\Stdlib\Collection $users) { // Process the records... return false;});The $users passed in the closure is a Collection object, and each method is also implemented through chunk, but the parameters are in different positions.
polymerization
The query constructor also provides various aggregation methods, such as count, max, min, avg, and sum. You can call any method after constructing the query.
$userNum = 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('status', 1) ->avg('price');If no data is queried, the return value is null. avg is the alias of the average method, and the return is a float type.
Determine whether the record exists
In addition to using the count method to determine whether the results of query criteria exist, you can also use the exists and doesntExist methods:
return DB::table('orders')->where('id', 1)->exists(); return DB::table('orders')->where('id', 1)->doesntExist();query
Specify a Select statement
Of course, you may not always want to get all the columns from the database table. Using the select method, you can customize a select query statement to query the specified fields:
// select `name`, `age` as `user_age` from `user`$users = DB::table('user')->select('name', 'age as user_age')->get();The distinct method will force the results returned by the query not to be repeated:
$users = DB::table('users')->distinct()->get();If you already have a query constructor instance and want to add a field to the existing query statement, you can use the addSelect method:
$query = DB::table('users')->select('name');$users = $query->addSelect(['age'])->get();Native expression
Sometimes you may need to use native expressions in queries. You can use the selectRaw method to create a native expression:
// select count(*) as `user_count`, `name` from `user` $users = DB::table('user') ->selectRaw('count(*) as `user_count`, `name`')); ->get();Note that native expressions will be injected into queries as strings, so you should use them carefully to avoid creating SQL injection vulnerabilities.
whereRaw / orWhereRaw
The whereRaw and orWhereRaw methods convert the native where
Inject into your query. The second parameter of these two methods is optional, and the value is also an array of bound parameters:
// select `name`, `age` as `user_age` from `user` where age > 18$users = DB::table('user') ->whereRaw('age > :age', ['age' => 18]) ->select('name', 'age as user_age') ->get();havingRaw / orHavingRaw
The havingRaw and orHavingRaw methods can be used to set a native string to the value of a having statement:
$orders = DB::table('user') ->selectRaw('sum(age) as age') ->groupBy('user_desc') ->havingRaw('age > ?', [17]) ->get();orderByRaw
The orderByRaw method can be used to set the native string to the value of the order by clause:
$time = time();$orderBy = 'if(`dead_time`>' . $time . ', update_time,0) desc,create_time desc'; $orders = DB::table('ticket') ->orderByRaw($orderBy) ->get();fromRaw
Custom FROM keyword parameters of fromRaw method, such as using forced index:
$sql = DB::table('') ->select('id', 'name') ->fromRaw('`user` force index(`idx_user`)') ->get();Joins
Inner Join Clause
The query constructor can also write a join method. To perform basic
Inner link, you can use the join method on the query constructor 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" instead of "inner join", you can use the leftJoin method. The leftJoin method is used in the same way as the join method:
$users = DB::table('users') ->leftJoin('posts', 'users.id', '=', 'posts.user_id') ->get();Cross Join statement
Use the crossJoin method to "cross connect" with the table name you want to connect. Cross join generates Cartesian product between the first table and the connected table:
// select * from `user` cross join `count` on `count`.`user_id` = `user`.`id`$users =Builder::new() ->from('user') ->crossJoin('count', 'count.user_id', '=', 'user.id') ->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 to specify the constraint 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 use "where" style statements on the connection, you can use the where and orWhere methods on the connection. 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();Subjoin query
You can associate a query as a subquery using the joinSub, leftJoinSub and rightJoinSub methods. Each of their methods will receive three parameters: subquery, table alias and closure defining associated fields:
$latestPosts = DB::table('posts') ->select('MAX(created_at) as last_created_at') ->where('is_published', true) ->groupBy('user_id');// $latestPosts is a query object $users = DB:: table ('users') - > joinsub ($latestPosts, 'latest_posts', function ($join) {$join - > on ('users. ID' = ','latest_posts. User_id');}) - > get();Unions
The query constructor also provides a shortcut to "join" two queries. For example, you can first create a query, and then use the union method to combine it with the second query:
// (select * from `user`) union all (select * from `user`) union (select * from `user`)Builder::new() ->from('user') ->unionAll(function (Builder $builder) { $builder->from('user'); }) ->union(Builder::new()->from('user')) ->get();Tip: you can also use the unionAll method. The usage of the union method is the same.
Where statement
Simple Where statement
In constructing an instance of where query, 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 an operator supported by any database system, and the third is the value of the column to be compared.
For example, the following is a query to verify that the value of the "money" field is equal to 100:
$users = DB::table('user')->where('money', '=', 100)->get();For convenience, if you simply compare whether the column value is equal to the given value, you can directly use the value 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 where clauses:
$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();Mixed array where form. It is also possible to nest an array inside the array
$wheres = [ 'name' => 'sakuraovq', ['status', '>=', 2], ['money', '>', 0, 'or']];// select * from `user` where (`name` = ? and `status` >= ? or `money` > ?)$users = User::where($wheres)->get();
Or statement
You can chain call the where constraint together, or add the or clause to the query. The orWhere method and the where method receive the same parameters:
$users = DB::table('user') ->where('money', '>', 100, 'or') ->orWhere('name', 'John') ->get();Other Where statements
whereBetween
The whereBetween method verifies that the field value is between the given two values:
$users = DB::table('user') ->whereBetween('money', [1, 100])->get();whereNotBetween
The whereNotBetween method verifies that the field value is outside the given two values:
$users = DB::table('user') ->whereNotBetween('money', [1, 100]) ->get();whereIn / whereNotIn
The value of the validation field of the whereIn method must exist in the specified array:
$users = DB::table('user') ->whereIn('id', [1, 2, 3]) ->get();The value of the validation field of the whereNotIn method must not exist in the specified array:
$users = DB::table('user') ->whereNotIn('id', [1, 2, 3]) ->get();whereNull / whereNotNull
The field specified for where NULL method validation must be NULL:
$users = DB::table('user') ->whereNull('created') ->get();The field specified for the whereNotNull method validation must not be NULL:
$users = DB::table('users') ->whereNotNull('created') ->get();whereDate / whereMonth / whereDay / whereYear / whereTime
When using these date functions, pay attention to the MySQL time zone,
The whereDate method is used to compare the field value with the given date:
$users = DB::table('users') ->whereDate('created', '2018-09-08') ->get();The whereMonth method is used to compare the field value with the month specified in the year:
$users = DB::table('users') ->whereMonth('created', '9') ->get();The whereDay method compares the field value with the date specified in January:
$users = DB::table('users') ->whereYear('created_at', '2018') ->get();The whereTime 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
The whereColumn 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();Where column you can also pass an array and link it with 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 groups. Swoft's query builder can also handle these. Let's take a look at an example of grouping constraints in parentheses:
DB::table('user') ->where('name', '=', 'John') ->where(function ($query) { $query->where('votes', '>', 100) ->orWhere('title', '=', 'Admin'); }) ->get();As you can see, a query constructor is built to constrain a group through a Closure write where method. This Closure receives a query instance, which you can use to set the constraints that should be included. The above example will generate the following SQL:
select * from `user` where `name` = 'sakura' and (`money` > 100 or `title` = 'test')
WhereExists
The whereExists method allows you to use the where exists SQL statement. The whereExists method receives a Closure parameter. The whereExists method accepts a Closure parameter. The Closure obtains a query builder instance, allowing you to define the query placed in the exists sentence:
DB::table('users') ->whereExists(function ($query) { $query->from('orders') ->whereRaw('orders.user_id = users.id'); }) ->get();The above query will produce the following SQL statements:
select * from `users`where exists ( select * from `orders` where `orders.user_id` = `users.id`)
JsonWhere
Swoft also supports querying JSON type fields (only on databases that support JSON types). Currently, this feature only supports MySQL 5.7 +.
Query JSON data using - > operator:
$users = DB::table('users') ->where('options->language', 'en') ->get();$users = DB::table('users') ->where('preferences->dining->meal', 'cookie') ->get();You can also use whereJsonContains to query JSON arrays:
$users = DB::table('users') ->whereJsonContains('options->languages', 'en') ->get();MySQL's whereJsonContains can support multiple values:
$users = DB::table('users') ->whereJsonContains('options->languages', ['en', 'de']) ->get();Ordering,Grouping,Limit,Offset
orderBy
The orderBy method allows you to sort 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 sorting direction, which can be asc or desc:
latest / oldest
The latest and oldest methods make it easy for you to sort by date. It uses created by default_ The at column is used as the sort basis. Of course, you can also pass custom column names:
$user = DB::table('users') ->oldest() ->first();inRandomOrder
The inRandomOrder method is used to sort the results randomly. For example, you can use this method to obtain a record at random.
$randomUser = DB::table('user') ->inRandomOrder() ->first();groupBy / having
The groupBy and having methods can group the results. The use of the having method is very similar to the where method:
$users = DB::table('users') ->selectRaw("count(*) count") ->groupBy('type') ->having('count', '>', "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 having raw method.
skip / take
To limit the number of results returned or skip a specified number of results, you can use the skip and take methods:
$users = DB::table('users')->skip(10)->take(5)->get();Alternatively, you can use the limit and offset methods:
$users = DB::table('users') ->offset(10) ->limit(5) ->get();If you want a fast paging query, you can use the forPage method
// $this->skip(($page - 1) * $size)->take($size); = forPage($page, $size)DB::table('users') ->forPage($page, $size) ->get();Conditional statement
Sometimes you may want to execute a query only if the clause is true. For example, you may want to apply the where statement only if the given value exists in the request. You can use the when method:
$role = true;$users = DB::table('users') ->when($role, function ($query, $role) { return $query->where('role_id', $role); }) ->get();The when method executes the given closure only when the first parameter is true. If the first parameter is false, the closure will not be executed
You can pass another closure as the third parameter of the when method. The closure is executed when the first parameter is false. To illustrate how to use this feature, let's configure the default sorting of a query:
$sortBy = null;$users = DB::table('users') ->when($sortBy, function ($query, $sortBy) { return $query->orderBy($sortBy); }, function ($query) { return $query->orderBy('name'); }) ->get();The query constructor also provides an insert method for inserting records into the database. The insert method receives the field name and field value in the form of array for insertion:
insert
The query constructor also provides an insert method for inserting records into the database. The insert method receives the field name and field value in the form of array for insertion:
DB::table('users')->insert( ['email' => 'john@example.com', 'votes' => 0]);You can even pass the array to the insert method to insert multiple records into the table
DB::table('users')->insert([ ['email' => 'taylor@example.com', 'votes' => 0], ['email' => 'dayle@example.com', 'votes' => 0]]);Self increment ID
If the data table has a self incrementing ID, use the insertGetId method to insert the record and return the ID value
$id = DB::table('user')->insertGetId([ 'age' => 18, 'name' => 'Sakura',]);to update
Of course, in addition to inserting records into the database, the query constructor can also update existing records through the update method. The update method, like the insert method, accepts an array containing the fields and values to be updated. You can constrain the update query through the where clause:
Update JSON fields
When updating JSON fields, you can use - > syntax to access the corresponding values in JSON objects. This operation can only be used in databases that support JSON field types:
DB::table('users') ->where('id', 1) ->update(['options->enabled' => true]);Self increasing and self decreasing
The query constructor also provides a convenient way to increment or decrement a given field. This method provides a more expressive and concise interface than manually writing update statements.
Both methods accept at least one parameter: the column 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 update during the operation:
DB::table('users')->where('id', 1)->increment('votes', 1, ['updated' => 1]);If you want to customize the update, you can do this:
$res = DB::table('user')->where('id', $id)->update([ 'posts' => DB::raw('`posts` + 1'), 'views' => Expression::new('`views` + 1'), 'name' => 'updated', ]);DB::raw(xxx) is equivalent to Expression::new(xxx). Prevent SQL injection when using these two methods
delete
The query constructor can also delete records from the table using the delete method. Before using delete, you can add a where clause to constrain the delete syntax:
DB::table('users')->where('votes', '>', 100)->delete();If you need to empty the table, you can use the truncate method, which will delete all rows and reset the self increment ID to zero:
DB::table('users')->truncate();lock
The query constructor also contains some functions that can help you implement "pessimistic locking" in the select syntax. To implement a "shared lock" in a query, you can use the read lock sharedLock method. The shared lock prevents the selected data column from being tampered with until the transaction is committed:
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();Alternatively, you can use the write lock lockForUpdate method. Use the "update" lock to prevent rows from being modified or selected by other shared locks:
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();Select connection pool
If there are multiple connection pools, the default assigned connection is in dB Pool is obtained from the default connection pool. If you want to obtain the connection from your own connection pool:
// The connection pool name configured in the bean is $poolName = 'pool order2';$ user = DB::query($poolName)->from('user')->where('id', $id)->get();The DB::query($poolName) method obtains the same Builder object
When is the connection released
The bottom layer only takes connections from the DB Connection pool for execution when executing sql, and will automatically release them after execution. The Builder object is no longer dependent on Connection
Release connection: return the connection to the connection pool