Laravel 5.2 database query example

1. Introduction

The database query builder provides a convenient and smooth interface for creating and running database queries. The query builder can be used to perform most database operations in an application and work on all supported database systems.

Note: The Laravel Query Builder uses the PDO parameter binding to prevent SQL injection attacks and does not need to filter the strings passed to the binding.
2. Obtain the result set

Retrieve all rows from a table

Before querying, use the table method of the DB facade. The table method returns a query builder for a given table, allowing you to link more constraints to the query and finally return the query result. In this example, we use the get method to retrieve all records in the table:

<? Php

Namespace App \ Http \ Controllers;

Use DB;
Use App \ Http \ Controllers \ Controller;

Class UserController extends Controller {
/**
* Display the user list
     *
* @ Return Response
*/
Public function index ()
    {
$ Users = DB: table ('users')-> get ();

Return view ('user. Index', ['users' => $ users]);
    }
}
Like native queries, the get method returns an array of result sets. Each result is an StdClass instance of the PHP object. You can access the value of a column like the attribute of an object:

Foreach ($ users as $ user ){
Echo $ user-> name;
}

Retrieve a row/column from a table

If you only want to obtain a row of data from the data table, you can use the first method, which will return a single StdClass object:

$ User = DB: table ('users')-> where ('name', 'John')-> first ();

Echo $ user-> name;
If you do not need a complete row, you can use the 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 ');

Obtain the block result set from a table.

If you need to process hundreds of thousands of database records, you can use the chunk method to obtain a small part of the result set at a time, and then fill each small piece of data into the closure to be processed, this method is useful when writing Artisan commands that process a large number of database records. For example, we can process all users tables into a group block that processes 100 records at a time:

DB: table ('users')-> chunk (100, function ($ users ){
Foreach ($ users as $ user ){
//
    }
});

You can abort block execution by returning false from the closure function:

DB: table ('users')-> chunk (100, function ($ users ){
// Processing result set...
Return false;
});
Get the data column value list

If you want to obtain an array containing a single column value, you can use the lists method. In this example, we obtain the array of all titles:

$ 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 (this custom key must be another field column name of the table; otherwise, an error is reported ):

$ Roles = DB: table ('roles ')-> lists ('title', 'name ');

Foreach ($ roles as $ name => $ title ){
Echo $ title;
}

Aggregate Functions

The queue builder also provides many aggregation methods, such as count, max, min, avg, and sum. You can call these methods after constructing a query:

$ Users = DB: table ('users')-> count ();
$ Price = DB: table ('orders ')-> max ('price ');
Of course, you can join other query clauses and aggregate functions to build a query:

$ Price = DB: table ('orders ')
-> Where ('finalized', 1)
-> Avg ('price ');

3. Query (Select)

Query clause

Of course, we do not always want to get all columns of the 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 forcibly query returned result sets that are not repeated:

$ Users = DB: table ('users')-> distinct ()-> get ();
If you already have a query builder instance and want to add a query column to an existing select clause, 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 the query. These expressions will be injected into the query in the form of strings, so be careful not to be 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. Join)

Internal connection (equivalent connection)

The query builder can also be used to compile basic SQL "internal connections". 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. Of course, as you can see, you can join 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 JOIN

If you want to execute "left join" instead of "inner join", you can use the leftJoin method. This method is the same as the join method:

$ Users = DB: table ('users ')
-> LeftJoin ('posts', 'users. ID', '=', 'posts. user_id ')
-> Get ();
Advanced connection statement

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

DB: table ('users ')
-> Join ('contacts', function ($ join ){
$ Join-> on ('users. ID', '=', 'Contacts. user_id ')-> orOn (...);
})
-> Get ();
If you want to use a "where" clause in the connection, you can use the where and orWhere methods in the query. These methods compare the column and value, instead of comparing the column and column:

DB: table ('users ')
-> Join ('contacts', function ($ join ){
$ Join-> on ('users. ID', '=', 'Contacts. user_id ')
-> Where ('contacts. user_id ','> ', 5 );
})
-> Get ();

5. Union)

The query builder also provides a "union" shortcut for two queries. For example, you need to create an independent query and then use the union method to combine it with the 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 usage as the union method.

6. Where clause

Simple where clause

You can add a where clause to the query by using the where method on the query builder. To call the where most basic method, three parameters are required. The first parameter is the column name, the second parameter is an arbitrary operator supported by the database system, and the third parameter is the value to be compared in this column.

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

$ Users = DB: table ('users')-> where ('votes ',' = ', 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 use other operators to compile the where clause:

$ Users = DB: table ('users ')
-> Where ('vots', '> =', 100)
-> Get ();

$ Users = DB: table ('users ')
-> Where ('votes ',' <> ', 100)
-> Get ();

$ Users = DB: table ('users ')
-> Where ('name', 'like', 't% ')
-> Get ();
Or

You can use the method chain to link multiple where constraints, or add the or clause to the query. The orWhere method and the where method receive the same parameters:

$ Users = DB: table ('users ')
-> Where ('votes ','> ', 100)
-> OrWhere ('name', 'John ')
-> Get ();
More Where clauses

WhereBetween

The whereBetween method verifies whether the column value is between the given values:

$ Users = DB: table ('users ')
-> WhereBetween ('votes ', [1,100])-> get ();
WhereNotBetween

The whereNotBetween method verifies that the column value is not between the given values:

$ Users = DB: table ('users ')
-> WhereNotBetween ('votes ', [1,100])
-> Get ();
WhereIn/whereNotIn

The whereIn method verifies whether the value of a given column is in a given array:

$ Users = DB: table ('users ')
-> WhereIn ('id', [1, 2, 3])
-> Get ();
The whereNotIn method verifies that the value of a 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 a given column is NULL:

$ Users = DB: table ('users ')
-> WhereNull ('updated _ ')
-> Get ();
The whereNotNull method is used to verify that the value of a given column is not NULL:

$ Users = DB: table ('users ')
-> WhereNotNull ('updated _ ')
-> Get ();
Advanced Where clause

Parameter group

Sometimes you need to create more advanced where clauses such as "where exists" or nested parameter groups. The Laravel query builder can also process this. Let's start with 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, the closure is passed to the orWhere method to construct the query builder to start a constraint group ,, this closure obtains an instance of the query builder used to set the constraints contained in the brackets. The preceding statement is equivalent to the following SQL statement:

Select * from users where name = 'John' or (votes> 100 and title <> 'admin ')
Exists statement

The whereExists method allows you to write a where existSQL clause. The whereExists method receives a closure parameter, which obtains a query builder instance and allows you to define a 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 preceding query is equivalent to the following SQL statement:

Select * from users
Where exists (
Select 1 from orders where orders. user_id = users. id
)

7. Sorting, grouping, and limitation

OrderBy

The orderBy method allows you to sort the result set by the given column. The first parameter of orderBy should be the column you want to sort, and the second parameter controls the direction of sorting-asc or desc:

$ Users = DB: table ('users ')
-> OrderBy ('name', 'desc ')
-> Get ();
GroupBy/having/havingRaw

The groupBy and having methods are used to group result sets. The usage of the having method and the where method is similar:

$ Users = DB: table ('users ')
-> GroupBy ('account _ id ')
-> Having ('account _ id', '>', 100)
-> Get ();
The havingRaw method can be used to set the native string as the value of the having clause. For example, we need to find all the parts with prices 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 returned result sets in a query, or skip the specified number of results in a query, you can use the skip and take methods:

$ Users = DB: table ('users')-> skip (10)-> take (5)-> get ();

8. Insert)

The query builder also provides the insert method to insert records into the data table. The insert method is used to insert column names and values in the array format:

DB: table ('users')-> insert (
['Email '=> 'John @ example.com', 'Votes '=> 0]);
You can even insert multiple records by inputting multiple arrays at a time. Each array indicates the records to be inserted into the data table:

DB: table ('users')-> insert ([
['Email '=> 'Taylor @ example.com', 'Votes '=> 0],
['Email '=> 'dayle @ example.com', 'Votes '=> 0]
]);
Auto-increment ID

If the data table has an auto-increment ID, using the insertGetId method to insert records will return the ID value:

$ Id = DB: table ('users')-> insertGetId (
['Email '=> 'John @ example.com', 'Votes '=> 0]
);
Note: When ssql is used, the insertGetId method is named as id by default. If you want to obtain the ID from other "sequences", you can pass the sequence name as the second parameter to the insertGetId method.
9. Update)
Of course, in addition to inserting records into the database, the query builder can use the update method to update existing records. The update method is the same as the insert method. The key-value pair for receiving columns and values contains the columns to be updated. You can use the where clause to constrain the update query:

DB: table ('users ')
-> Where ('id', 1)
-> Update (['votes '=> 1]);
Increase/decrease

The query builder also provides methods to increase or decrease the value of a given column name. Compared with writing update statements, this 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 values that increase/decrease.

DB: table ('users')-> increment ('votes ');
DB: table ('users')-> increment ('votes ', 5 );
DB: table ('users')-> decrement ('votes ');
DB: table ('users')-> decrement ('votes ', 5 );
During the operation, you can specify additional columns for update:

DB: table ('users')-> increment ('votes ', 1, ['name' => 'John']);
10. Delete)
Of course, the query builder can also delete records from the table using the delete method:

DB: table ('users')-> delete ();
Before calling the delete method, you can add a where clause to restrict the delete statement:

DB: table ('users')-> where ('votes ',' <', 100)-> delete ();
If you want to clear the entire table, that is, delete all columns and set the auto-increment ID to 0, you can use the truncate method:

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

11. Pessimistic lock

The query builder also contains some methods to help you implement the "pessimistic lock" in the select statement ". You can use the sharedLock method in the query to include a "shared lock" when running the statement. The shared lock prevents the selected row from being modified until the transaction is committed:

DB: table ('users')-> where ('votes ','> ', 100)-> sharedLock ()-> get ();
You can also use the 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 ();