Query constructor
Introduction
Swoft's database query constructor provides a convenient interface for creating and running database queries. It can be used to perform most database operations in an application and can run on all supported database systems.
Swoft's query constructor uses PDO
parameter binding to protect your application from SQL injection attacks. Therefore there is no need to clean up the string passed as a binding.
You can use DB::table('xxxx')
get a Builder
object or you can use Builder::new()->from('xxx')
The two methods return the same result. The Builder
object does not assign a connection. Only get from the connection pool when executing sql
Get results
Get all rows from one 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 get the result using the get method:
// sql = select * from `user`
$users = DB::table('user')->get();
The get
method returns a result containing a Collection
, where each result is an Array
. You can access the value of each column by accessing the field as an attribute of the object:
foreach ($users as $user) {
echo $user->name;
}
You can also use the Collection
all methods Collection
provides a powerful method of operation of the object.
If you want to query all the data faster, you can use the cursor
method, and the bottom layer is implemented by yield
. Each of these results is an Array
$users = DB::table('user')->cursor();
foreach ($users as $user){
echo $user->name;
}
Get a single row or single column from the 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 Array
:
$user = DB::table('user')->where('name', 'Sakura')->first();
if ($user) {
echo $user->name;
}
If you don't even need an entire row of data, you can use the value method to get a single value from the record. This method will return the value of this field directly:
$name = DB::table('users')->where('name', 'Sakura')->value('name');
Get the value of a column
If you want to get a collection that contains a single column value, you can use the pluck method. In the following example, we will get a collection of headings in the role table.
$titles = DB::table('roles')->pluck('title');
foreach ($roles as $title) {
echo $title;
}
You can also specify a custom key-value for the field in the returned collection:
$roles = DB::table('users')->pluck('email', 'name');
foreach ($roles as $name => $email) {
echo $email;
}
Blocking result
If you need to process thousands of database records, you can consider using the chunk method. This method takes a small chunk of the result set at a time and passes it to the closure function for processing. It works well when fixing data. For example, we can cut all user table data into one 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 stop continuing to get chunked 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 the each
method is also implemented by chunk
, but the parameters are opposite in different positions.
polymerization
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:
$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 returned to the query, the return value is an int of type 0
. avg
is an alias for the average
method, and the return is a float|int
type.
Count fixed return
int
,max
,min
,avg
,sum
These functions may involve floating-point calculations. The underlying is not cast. The return type isfloat|int
This value is the value returned by the数据库
.
Determine if the record exists
In addition to the existence of the result of the query condition by the count method, 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();
Inquire
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 to query the specified field:
// select `name`, `age` as `user_age` from `user`
$users = DB::table('user')->select('name', 'age as user_age')->get();
The distinct method forces the result returned by the query to not be repeated:
$users = DB::table('users')->distinct()->get();
If you already have a query constructor instance and want to include a field in an existing query, 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 a native expression in your query. You can create a native expression using the selectRaw
method:
// select count(*) as `user_count`, `name` from `user`
$users = DB::table('user')
->selectRaw('count(*) as `user_count`, `name`'));
->get();
Prompt native expressions will be injected into the query as strings, so you should be careful to avoid creating SQL injection vulnerabilities.
whereRaw / orWhereRaw
The whereRaw and orWhereRaw methods inject the native where into your query. The second argument to these two methods is still an option, and the value is still 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 the native string to the value of the having statement:
$orders = DB::table('user')
->selectRaw('sum(age) as age')
->groupBy('user_desc')
->havingRaw('age > ?', [17])
->get();
orderByRaw
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 arguments to the fromRaw
method, such as using a 强制索引
:
$sql = DB::table('')
->select('id', 'name')
->fromRaw('`user` force index(`idx_user`)')
->get();
Joins
Inner Join Clause
The query constructor can also write join
methods. To perform a basic "internal link", you can use the join
method on the query constructor instance. The first argument passed to the join
method is the name of the table you need to join
, while the other arguments are bounded by the fields of the specified 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 Connection" instead of "Inner Connection", you can use the leftJoin
method. leftJoin
method is the same as the join
method:
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
Cross Join statement
Use the crossJoin
method to do a "cross-connect" with the table name you want to connect to. A cross-connection creates a Cartesian product between the first table and the joined 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 a more advanced join
statement. For example, passing a closure as the second argument to the join
method. This closure receives a JoinClause
object, 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 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();
Sub-join query
You can associate a query as a subquery using the joinSub
, leftJoinSub
and rightJoinSub
methods. Each of them receives three parameters: a subquery, a table alias, and a closure that defines the associated field:
$latestPosts = DB::table('posts')
->select('MAX(created_at) as last_created_at')
->where('is_published', true)
->groupBy('user_id');
// $latestPosts 是一个 query 对象
$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 "join" two queries. For example, you can create a query first and then union it with the second query using the union method:
// (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();
Hint: You can also use the unionAll method, which is the same as the usage union method.
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 the operator supported by any database system, and the third is the value to be compared for the column.
For example, here 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 the column values to the given values, you can use the values directly as the second argument to 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 a conditional array to the where function:
$users = DB::table('users')->where([
['status', '=', '1'],
['subscribed', '<>', '1'],
])->get();
Mixed array where
form, nesting an array inside the array is also possible
$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 the where constraint together, or you can add an 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 statement
whereBetween
whereBetween
method verifies that the field value is between the two values given:
$users = DB::table('user')
->whereBetween('money', [1, 100])->get();
whereNotBetween
whereNotBetween
method verifies that the field value is outside of the given two values:
$users = DB::table('user')
->whereNotBetween('money', [1, 100])
->get();
whereIn / whereNotIn
The value of the whereIn
method validation field must exist in the specified array:
$users = DB::table('user')
->whereIn('id', [1, 2, 3])
->get();
The value of the whereNotIn
method validation field must not exist in the specified array:
$users = DB::table('user')
->whereNotIn('id', [1, 2, 3])
->get();
whereNull / whereNotNull
whereNull
method verifies that the specified field must be NULL
:
$users = DB::table('user')
->whereNull('created')
->get();
whereNotNull
method verifies that the specified field must not be NULL
:
$users = DB::table('users')
->whereNotNull('created')
->get();
whereDate / whereMonth / whereDay / whereYear / whereTime
Use these date functions to pay attention to the MySQL
time zone, where the whereDate
method is used to compare field values with a given date:
$users = DB::table('users')
->whereDate('created', '2018-09-08')
->get();
whereMonth
method is used to compare field values with the month specified in the year:
$users = DB::table('users')
->whereMonth('created', '9')
->get();
whereDay
method is used to compare the field value with the date specified in January:
$users = DB::table('users')
->whereDay('created_at', '20')
->get();
whereYear
method is used to compare field values with the specified year:
$users = DB::table('users')
->whereYear('created_at', '2018')
->get();
whereTime
method is used to compare field values with the specified time (time, minute, and second):
$users = DB::table('users')
->whereTime('created_at', '=', '11:20:45')
->get();
whereColumn
The whereColumn method is used to compare the values of two fields for equality:
$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 an array 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 a more advanced where clause, such as "where exists" or nested parameter grouping. Swoft's query constructor can also handle these. Let's 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, construct a query constructor to constrain a grouping by writing a where
method to the Closure
. 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 `user` where `name` = 'sakura' and (`money` > 100 or `title` = 'test')
WhereExists
whereExists
method allows you to use the where exists SQL statement. whereExists
method receives a Closure
parameter, and the whereExists
method accepts a Closure
parameter that gets a query builder instance allowing you to define the query placed in the exists
clause:
DB::table('users')
->whereExists(function ($query) {
$query->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();
The above query will produce the following SQL statement:
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 with the ->
operator:
$users = DB::table('users')
->where('options->language', 'en')
->get();
$users = DB::table('users')
->where('preferences->dining->meal', 'cookie')
->get();
You can also use the whereJsonContains
to query a JSON array:
$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
orderBy
method allows you to sort the result set by the 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:
Latest / oldest
latest
and oldest
methods allow you to easily sort by date. It uses the created_at
column as the sort by default. Of course, you can also pass a custom column name:
$user = DB::table('users')
->oldest()
->first();
inRandomOrder
inRandomOrder
method is used to randomly sort the results. For example, you can use this method to get a random record.
$randomUser = DB::table('user')
->inRandomOrder()
->first();
groupBy / having
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 the more advanced having
syntax, see the havingRaw
method.
Skip / take
To limit the number of results returned, or to skip the specified number of results, you can use the skip
and take
methods:
$users = DB::table('users')->skip(10)->take(5)->get();
Or you can use the limit and offset methods:
$users = DB::table('users')
->offset(10)
->limit(5)
->get();
If you want a quick paged 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 statements
Sometimes you may want a clause to only apply to a query if it is true. For example, you might only want to apply a where statement if the given value exists in the request. You can do this by using the when
method:
$role = true;
$users = DB::table('users')
->when($role, function ($query) use ($role) {
return $query->where('role_id', $role);
})
->get();
when
method only executes the given closure when
the first argument is true
. If the first argument is false
then the closure will not be executed
You can pass another closure as the third argument to the when
method. The closure will be executed if the first argument is false
. To illustrate how to use this feature, let's configure the default ordering 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();
The Query Builder 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 an array for insertion:
insert
The Query Builder 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 an array for insertion:
DB::table('users')->insert(
['email' => 'john@example.com', 'votes' => 0]
);
You can even pass an array to the insert
method and insert
multiple records into the table.
DB::table('users')->insert([
['email' => 'taylor@example.com', 'votes' => 0],
['email' => 'dayle@example.com', 'votes' => 0]
]);
Self-increase 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('user')->insertGetId([
'age' => 18,
'name' => 'Sakura',
]);
Update
Of course, in addition to inserting records into the database, the query constructor can also update existing records via the update
method. update
method, like the insert
method, accepts an array containing the fields and values to be updated. You can constrain the update
query with the where
clause:
Update JSON field
When updating a JSON field, you can use the -> syntax to access the corresponding value in the JSON object. This operation can only be used with databases that support JSON field types:
DB::table('users')
->where('id', 1)
->update(['options->enabled' => true]);
Self-increase and self-reduction
The Query Builder also provides a convenient way to increment or decrement a given field. This method provides a more expressive and more refined interface than writing an update
statement manually.
Both methods receive at least one parameter: the column that needs to be modified. The second parameter is optional and is used to control the amount of column increment or decrement:
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 which 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)
equivalent toExpression::new(xxx)
to preventSQL
injection when using these two methods.
delete
The query constructor can also delete records from a 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 auto increment ID to zero:
DB::table('users')->truncate();
Print the last executed sql
You can use the toSql()
method to print the last executed sql
DB::table('users')->where('id', $id)->toSql();
lock
The Query Builder also contains functions that can help you implement "pessimistic locking" on the select syntax. If you implement a "shared lock" in the query, you can use the read lock sharedLock
method. A shared lock prevents the selected data column from being tampered 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 lines 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 connection is obtained in the default connection pool of db.pool
. If you want to get the connection of your own connection pool:
// 在 bean 里面配置的连接池名
$poolName = 'pool.order2';
$user = DB::query($poolName)->from('user')->where('id', $id)->get();
DB::query($poolName)
method gets the same as a Builder
object.
When is the connection released?
The underlying layer will only take the connection from the DB connection pool when executing sql, and will be released automatically after execution. Builder
object is not dependent on Connection
Release the connection: return the connection to the connection pool
FQA
Using such an example in a where closure is wrong
$res = DB::table('user')
->where(function (Builder $query) {
$query->forPage(1, 10)
->orderBy('age', 'ase')
->where('id', 1);
})
->orderBy('id', 'desc')
->get();
In the case of closure of orderBy
and forPage
not take effect only where
the relevant restrictions to take effect.