Laravel framework database CURD operations and consistent operation summary

This article mainly introduces the Laravel framework database CURD operations, coherent operations, and Chain Operations Summary. This article contains a large number of common database operations methods. For more information, see Laravel.

I. Selects

Retrieve all rows in a table

The code is as follows:

$ Users = DB: table ('users')-> get ();
Foreach ($ users as $ user)
{
Var_dump ($ user-> name );
}

Retrieve a single row from a table

The code is as follows:

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

Retrieve rows of a single column

The code is as follows:

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

Retrieve a column value list

The code is as follows:

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

This method returns an array title. You can also specify an array returned by a custom key column.

The code is as follows:

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

Specify a Select clause

The code is as follows:

$ Users = DB: table ('users')-> select ('name', 'Email ')-> get ();
$ Users = DB: table ('users')-> distinct ()-> get ();
$ Users = DB: table ('users')-> select ('name as user_name ')-> get ();

Add the Select clause to an existing query $ query = DB: table ('users')-> select ('name ');

The code is as follows:

$ Users = $ query-> addSelect ('age')-> get ();

Where

The code is as follows:

$ Users = DB: table ('users')-> where ('votes ','> ', 100)-> get ();

OR

The code is as follows:

$ Users = DB: table ('users')-> where ('votes ','> ', 100)-> orWhere ('name', 'John ') -> get ();

Where

The code is as follows:

$ Users = DB: table ('users')-> whereBetween ('votes ', array (1,100)-> get ();

Where Not

The code is as follows:

$ Users = DB: table ('users')-> whereNotBetween ('votes ', array (1,100)-> get ();

Where In With An Array

The code is as follows:

$ Users = DB: table ('users')-> whereIn ('id', array (1, 2, 3)-> get ();
$ Users = DB: table ('users')-> whereNotIn ('id', array (1, 2, 3)-> get ();

Using Where Null To Find Records With Unset Values

The code is as follows:

$ Users = DB: table ('users')-> whereNull ('updated _ at')-> get ();

Order By, Group By, And Having

The code is as follows:

$ Users = DB: table ('users')-> orderBy ('name', 'desc')-> groupBy ('count')-> having ('count ', '>', 100)-> get ();

Offset & Limit

The code is as follows:

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

2. connection

Joins

The query builder can also be used to write connection statements. Take a look at the following example:

Basic Join Statement

The code is as follows:

DB: table ('users ')
-> Join ('contacts', 'users. ID', '=', 'Contacts. user_id ')
-> Join ('orders ', 'users. ID',' = ', 'orders. user_id ')
-> Select ('users. ID', 'Contacts. phone', 'orders. price ')
-> Get ();

Left join statement

The code is as follows:

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

3. Grouping

Sometimes you may need to create more advanced where clauses, such as "exist" or nested parameter groups. Laravel query builder can handle the following:

The code is as follows:

DB: table ('users ')
-> Where ('name', '=', 'John ')
-> OrWhere (function ($ query)
{
$ Query-> where ('votes ','> ', 100)
-> Where ('title', '<>', 'admin ');
})
-> Get ();

The preceding query generates the following SQL:

The code is as follows:

Select * from users where name = 'John' or (votes> 100 and title
<> 'Admin ')
Exists Statements
DB: table ('users ')
-> WhereExists (function ($ query)
{
$ Query-> select (DB: raw (1 ))
-> From ('orders ')
-> WhereRaw ('orders. user_id = users. id ');
})
-> Get ();

The preceding query generates the following SQL:

The code is as follows:

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

IV. aggregation

The query builder also provides various aggregation methods, such as statistics, Max, min, avg, and sum.

Using Aggregate Methods

The code is as follows:

$ Users = DB: table ('users')-> count ();
$ Price = DB: table ('orders ')-> max ('price ');
$ Price = DB: table ('orders ')-> min ('price ');
$ Price = DB: table ('orders ')-> avg ('price ');
$ Total = DB: table ('users')-> sum ('votes ');

Raw Expressions

Sometimes you may need to query with an original expression. These expressions inject query strings, so be careful not to create any SQL injection points! To create an original expression, you can use DB: rawmethod:

Using A Raw Expression

The code is as follows:

$ Users = DB: table ('users ')
-> Select (DB: raw ('count (*) as user_count, status '))
-> Where ('status', '<>', 1)
-> GroupBy ('status ')
-> Get ();

Increment or decrease the value of a column

The code is as follows:

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 additional column updates:

The code is as follows:

DB: table ('users')-> increment ('votes ', 1, array ('name' => 'John '));

Inserts

Insert records into the table

The code is as follows:

DB: table ('users')-> insert (
Array ('email '=> 'John @ example.com', 'Votes '=> 0)
);

ID automatically added to insert records into the table

If the table has an auto-incrementing id field, insert a record and search id using insertGetId:

The code is as follows:

$ Id = DB: table ('users')-> insertGetId (
Array ('email '=> 'John @ example.com', 'Votes '=> 0)
);

Note: When PostgreSQL insertGetId method is used, the auto-increment column is named "id ".

Insert multiple records into the table

The code is as follows:

DB: table ('users')-> insert (array (
Array ('email '=> 'Taylor @ example.com', 'Votes '=> 0 ),
Array ('email '=> 'dayle @ example.com', 'Votes '=> 0 ),
));

IV. Updates

Update records in a table

The code is as follows:

DB: table ('users ')
-> Where ('id', 1)
-> Update (array ('votes '=> 1 ));

V. Deletes

Delete records in a table

The code is as follows:

DB: table ('users')-> where ('votes ',' <', 100)-> delete ();

Delete all records in the table

The code is as follows:

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

Delete a table

The code is as follows:

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

VI. Unions

The query builder also provides a fast way to "consortium" two queries:

The code is as follows:

$ First = DB: table ('users')-> whereNull ('First _ name ');
$ Users =
DB: table ('users')-> whereNull ('last _ name')-> union ($ first)-> get ();

The unionAll method can also have the same method signature.

Pessimistic Locking

The query builder includes some "pessimistic locking" functions to help you execute your SELECT statement. Run the SELECT statement "shared Lock". you can use the sharedLock method to query:

The code is as follows:

DB: table ('users')-> where ('votes ','> ',
100)-> sharedLock ()-> get ();

Update the "lock" in a SELECT statement. you can use the lockForUpdate method to query:

The code is as follows:

DB: table ('users')-> where ('votes ','> ', 100)-> lockForUpdate ()-> get ();

VII. cache query

You can easily cache the query results using the memory method:

The code is as follows:

$ Users = DB: table ('users')-> remember (10)-> get ();

In this example, the query result is cached for 10 minutes. When the query result is cached, it does not run on the database, and the results will load the driver from the default cache to specify your application. If you are using a driver that supports caching, you can also add tags for caching:

The code is as follows:

$ Users = DB: table ('users')-> cacheTags (array ('others', 'author')-> remember (10)-> get ();