Laravel framework database CURD operations, consistent operation summary, laravelcurd

Laravel framework database CURD operations, consistent operation summary, laravelcurd

I. Selects

Retrieve all rows in a table
Copy codeThe Code is as follows:
$ Users = DB: table ('users')-> get ();
Foreach ($ users as $ user)
{
Var_dump ($ user-> name );
}

Retrieve a single row from a table
Copy codeThe Code is as follows:
$ User = DB: table ('users')-> where ('name', 'john')-> first ();
Var_dump ($ user-> name );

Retrieve rows of A Single Column
Copy codeThe Code is as follows:
$ Name = DB: table ('users')-> where ('name', 'john')-> pluck ('name ');

Retrieve a column Value List
Copy codeThe 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.
Copy codeThe Code is as follows:
$ Roles = DB: table ('roles ')-> lists ('title', 'name ');

Specify a Select clause
Copy codeThe 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 ');
Copy codeThe Code is as follows:
$ Users = $ query-> addSelect ('age')-> get ();

Where
Copy codeThe Code is as follows:
$ Users = DB: table ('users')-> where ('votes ','> ', 100)-> get ();

OR
Copy codeThe Code is as follows:
$ Users = DB: table ('users')-> where ('votes ','> ', 100)-> orWhere ('name', 'john ') -> get ();

Where

Copy codeThe Code is as follows:
$ Users = DB: table ('users')-> whereBetween ('votes ', array (1,100)-> get ();

Where Not

Copy codeThe Code is as follows:
$ Users = DB: table ('users')-> whereNotBetween ('votes ', array (1,100)-> get ();

Where In With An Array

Copy codeThe 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
Copy codeThe Code is as follows:
$ Users = DB: table ('users')-> whereNull ('updated _ at')-> get ();

Order By, Group By, And Having
Copy codeThe Code is as follows:
$ Users = DB: table ('users')-> orderBy ('name', 'desc')-> groupBy ('Count')-> having ('Count ', '>', 100)-> get ();

Offset & Limit

Copy codeThe 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

Copy codeThe 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

Copy codeThe 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:

Copy codeThe 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:
Copy codeThe 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:
Copy codeThe 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
Copy codeThe 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

Copy codeThe 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
Copy codeThe 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:
Copy codeThe Code is as follows:
DB: table ('users')-> increment ('votes ', 1, array ('name' => 'john '));

Inserts

Insert records into the table

Copy codeThe 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:
Copy codeThe 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
Copy codeThe 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
Copy codeThe Code is as follows:
DB: table ('users ')
-> Where ('id', 1)
-> Update (array ('votes '=> 1 ));

V. Deletes

Delete records in a table
Copy codeThe Code is as follows:
DB: table ('users')-> where ('votes ',' <', 100)-> delete ();

Delete all records in the table
Copy codeThe Code is as follows:
DB: table ('users')-> delete ();

Delete a table
Copy codeThe Code is as follows:
DB: table ('users')-> truncate ();

Vi. Unions

The Query Builder also provides a fast way to "Consortium" two queries:

Copy codeThe 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:
Copy codeThe 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:
Copy codeThe 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:
Copy codeThe 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:
Copy codeThe Code is as follows:
$ Users = DB: table ('users')-> cacheTags (array ('others', 'author')-> remember (10)-> get ();

Php_laravel framework

In the 280907494 development group, many of them are engaged in this.

PHP laravel framework, the first access entry is normal, and then refresh to report an error

Open debug and see the detailed error.