[Laravel 5.2 Document] Database--Query Builder

1. Introduction

The database Query Builder provides a convenient, smooth interface for creating and running database queries. The query Builder can be used to perform most of the database operations in your app and be able to work on all supported database systems.

Note: The Laravel Query Builder uses the PDO parameter binding to avoid SQL injection attacks, eliminating the need to filter strings passed to the binding.

2. Get the result set

Remove all rows from a single table

Before querying, using the table method of the DB façade, the table method returns a query builder for the given table, allowing you to link more constraints on the query and ultimately return the query results. In this case, we use the Get method to get all the records in the table:

 
  Get ();        Return view (' User.index ', [' users ' = ' $users]);}    }

Like a native query, the Get method returns an array of result sets, each of which is a stdclass instance of the PHP object. You can access the values of the columns as you would access the object's properties:

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 a data table, you can use the first method, which returns a single Stdclass object:

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

If you don't need a complete row, you can use the value method to get a single value from the result, which returns the value of the specified column directly:

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

Get a block result set from a table

If you need to work with hundreds of thousands of database records, consider using the chunk method, which gets a small chunk of the result set at a time, and then fills each small piece of data to the closure you want to process, which is useful when writing the Artisan command that handles a large number of database records. For example, we can process all the users tables into a block that processes 100 records at a time:

Db::table (' users ')->chunk (function ($users) {    foreach ($users as $user) {        //    }});

You can abort the operation of a block by returning false from the closure function:

Db::table (' users ')->chunk (function ($users) {    //processing result set    ... return false;});

Get list of data column values

If you want to get an array that contains a single column value, you can use the lists method, in this case we get an array of all the title:

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

You can also specify more custom keys for the column values in the returned array (the custom key must be the other field column name for the table, or it will be an error):

$roles = db::table (' roles ')->lists (' title ', ' name '); foreach ($roles as $name = + $title) {    echo $title;}

Aggregation functions

The Queue Builder also provides a number of aggregation methods, such as Count, Max, Min, Avg, and Sum, which you can call 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 a query:

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

3. Query (Select)

Specifying query clauses

Of course, we don't always want to get all the columns of a data table, using the Select method, you can specify a custom SELECT clause for the query:

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

The distinct method allows you to force a query to return a non-repeating result set:

$users = db::table (' users ')->distinct ()->get ();

If you already have a query builder instance and want to add a query column to a SELECT clause that already exists, you can use the Addselect method:

$query = db::table (' users ')->select (' name '); $users = $query->addselect (' Age ')->get ();

Native expression

Sometimes you want to use native expressions in queries that will be injected into the query as strings, so take extra care to avoid being injected by SQL. To create a native expression, you can use the Db::raw method:

$users = db::table (' users ')                     ->select (Db::raw (' count (*) as User_count, Status '))                     ->where (' status ', ' <> ', 1)                     ->groupby (' status ')                     ->get ();

4. Connection (join)

Internal connection (equivalent connection)

The query Builder can also be used to write basic SQL "inner joins," where you can use the Join method on the Query Builder instance, the first parameter passed to the Join method is the name of the table you need to connect to, and the remaining parameters are the column constraints specified for the connection, and of course, as you see, You can connect more than one table 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

You can use the Leftjoin method if you want to perform a "left connection" instead of an "inner join." This method is used in the same way as the Join method:

$users = db::table (' users ')            ->leftjoin (' posts ', ' users.id ', ' = ', ' posts.user_id ')            ->get ();

Advanced Connection Statements

You can also specify more advanced connection clauses, pass a closure to join method as the 2nd parameter of the method, and the closure will return the JoinClause object that allows you to specify the JOIN clause constraint:

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 the where and Orwhere methods in the query. These methods compare columns to values rather than columns and columns:

Db::table (' users ')        ->join (' Contacts ', function ($join) {            $join->on (' users.id ', ' = ', ' Contacts.user_ Id ')                 ->where (' contacts.user_id ', ' > ', 5);        })        ->get ();

5. Union (Union)

The Query Builder also provides a shortcut to a "federated" two query, for example, you want to create a stand-alone query, and then use the Union method to federate it with a second query:

$first = db::table (' users ')            ->wherenull (' first_name '); $users = db::table (' users ')            ->wherenull (' Last_Name ')            ->union ($first)            ->get ();

The UnionAll method is also valid and has the same method of use as the Union.

6. WHERE clause

Simple WHERE clause

Use the Where method on the Query builder to add a WHERE clause to the query, call where the most basic method requires three parameters, the first parameter is the column name, the second parameter is any operator supported by the database system, and the third parameter is the value that the column is to compare.

For example, here is a query that verifies that the value of the "votes" column is equal to 100:

$users = db::table (' users ')->where (' votes ', ' = ', +)->get ();

For convenience, if you simply compare the column values with the given values for equality, you can use the values directly as the second parameter of the Where method:

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

Of course, you can use other operators to write a WHERE clause:

$users = db::table (' users ')                ->where (' votes ', ' >= ', ')                ->get (); $users = db::table (' users ')                - >where (' votes ', ' <> ', '->get ')                ; $users = db::table (' users ')                ->where (' name ', ' like ', ' t% ')                ->get ();

Or

You can link multiple where constraints together through a method chain, or you can add or clauses to a query, the Orwhere method and the Where method receive parameters:

$users = db::table (' users ')                    ->where (' votes ', ' > ', ')                    ->orwhere (' name ', ' John ')                    ->get ();

More WHERE clauses

Wherebetween

The Wherebetween method verifies that the column values are between the given values:

$users = db::table (' users ')                    ->wherebetween (' votes ', [1])->get ();

Wherenotbetween

The Wherenotbetween method verifies that the column values are not between the given values:

$users = db::table (' users ')                    ->wherenotbetween (' votes ', [1])                    ->get ();

Wherein/wherenotin

The wherein method verifies that the value of the given column is in the given array:

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

The Wherenotin method verifies 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

The Wherenull method verifies that the value of the given column is null:

$users = db::table (' users ')                    ->wherenull (' updated_at ')                    ->get ();

The Wherenotnull method verifies that the value of the given column is not null:

$users = db::table (' users ')                    ->wherenotnull (' updated_at ')                    ->get ();

Advanced WHERE clause

Parameter grouping

Sometimes you need to create more advanced where clauses such as "where exists" or nested parameter groupings. The Laravel Query Builder can also handle these. As a starting point, let's look at an example of grouping constraints in parentheses:

Db::table (' users ')            ->where (' name ', ' = ', ' John ')            ->orwhere (function ($query) {                $query->where (' Votes ', ' > ', '                      ->where ' (' title ', ' <> ', ' Admin ');            })            ->get ();

As you can see, the transitive closure to the Orwhere method constructs the query Builder to begin a constraint grouping, and the closure gets a query builder instance that sets the constraints contained in the parentheses. The above statement is equivalent to the following SQL:

SELECT * from users where name = ' John ' or (votes > title <> ' Admin ')

exists statements

The Whereexists method allows you to write a WHERE EXISTSQL clause, and the Whereexists method receives a closure parameter that gets a query builder instance that allows you to define the query that is 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 Userswhere exists (    Select 1 from orders where orders.user_id = users.id)

7. Sorting, grouping, qualification

By

The order by method allows you to sort the result set by a given column, the first parameter of the order should be the column you want to sort, the second parameter controls the direction of the sort--ASC or desc:

$users = db::table (' users ')                ->orderby (' name ', ' desc ')                ->get ();

Groupby/having/havingraw

The GroupBy and having methods are used to group the result set, similar to the use of the having method and the Where method:

$users = db::table (' users ')                ->groupby (' account_id ')                ->having (' account_id ', ' > ')                ->get ();

The Havingraw method can be used to set the native string as the value of the HAVING clause, for example, we want to find all parts that are priced greater than $2500:

$users = db::table (' orders ')                ->select (' Department ', Db::raw (' SUM (price) as Total_sales '))                ->groupby (' Department ')                ->havingraw (' SUM (price) > 2500 ')                ->get ();

Skip/take

To limit the number of result sets returned by a query, or to skip a given number of results in a query, you can use the Skip and take methods:

$users = db::table (' users ')->skip (Ten)->take (5)->get ();

8. Inserting (insert)

The Query Builder also provides an Insert method to insert records into a data table. The Insert method receives an array of column names and values for inserting operations:

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

You can even insert multiple records by passing in more than one array at a time, each representing the records to insert into the data 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-increment ID, using the Insertgetid method to insert the record will return the ID value:

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

Note: When using Postgressql, the Insertgetid method default self-increment column is named ID, and if you want to get the ID from another "sequence", you can pass the sequence name as the second argument to the Insertgetid method.

9. Updates (update)

Of course, in addition to inserting records into a database, the Query Builder can also update existing records by using the Update method. The Update method, like the Insert method, receives the column and value of the key value array containing the columns to be updated, and you can constrain the update query through the WHERE clause:

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

Increase/decrease

The Query Builder also provides a convenient way to increase or decrease the value of a given column name. Compared to writing an UPDATE statement, this is a shortcut that provides a better experience and test interface.

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 number of increment/decrease in column values.

Db::table (' users ')->increment (' votes ');D b::table (' users ')->increment (' votes ', 5);D b::table (' users ') Decrement (' votes ');D b::table (' users ')->decrement (' votes ', 5);

You can also specify additional columns to update during the operation:

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

10. Deletion (delete)

Of course, the Query Builder can also delete records from a table by using the Delete method:

Db::table (' users ')->delete ();

You can constrain the DELETE statement by adding a WHERE clause before calling the Delete method:

Db::table (' users ')->where (' votes ', ' < ', +)->delete ();

If you want to clear the entire table, that is, delete all the columns and set the self-increment ID to 0, you can use the Truncate method:

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

11, Pessimistic lock

The Query Builder also contains methods to help you implement pessimistic locking in the SELECT statement. You can use the Sharedlock method in a query to carry a "shared lock" when you run the statement. A shared lock prevents the selected row from being modified until the transaction commits:

Db::table (' users ')->where (' votes ', ' > ', ')->sharedlock ()->get ();

Also you can use the Lockforupdate method. The "for update" lock avoids the choice of rows being modified or deleted by other shared locks:

Db::table (' users ')->where (' votes ', ' > ', ')->lockforupdate ()->get ();

