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 is float|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 to Expression::new(xxx) to prevent SQL 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.