Laravel Learning-Sample code sharing for database operations and query constructors

Recent small series in learning the world's most bull frame –laravel. In fact, learning framework is the idea of learning the framework! I would like to record in my blog Some of my experience in the Laravel study, Welcome to my other GitHub blog and Jane Book, communicate with each other!

Version: Laravel 5.2
Database: MySQL 5.7
php:php7.1

Database operations and query constructors

There are two ways to perform database operations in Laravel, one is to \DB execute SQL queries directly using the static method of the Skin object, and the other is to use the static method of the model class (which is actually the implementation of facade, which accesses the model using static access, internally using the The callStatic Magic method proxies access to the member methods.

Query operations

To perform a select query operation using an SQL statement #

$results = Db::select (' select * from users where id =? ', [1]); foreach ($results as $res) {    echo $res->name;}

Returns the result of an array in which each value is an StdClass object.
You can also use a named binding, which is recommended to make it clearer

$results = Db::select (' select * from users WHERE id =: id ', [' id ' = 1]);

Get all columns of data from the data table #

$users = db::table (' users ')->get (); foreach ($users as $user) {    var_dump ($user->name);}

Querying a single row/column from a table

Returns a single row of data using the first method, which returns a Stdclass object

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

If you need only one column of values, you can use the value method to get the value of a single row directly

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

Block lookup data columns from a data table

This method is used for operations with large amounts of data in a data table, each time a portion is taken out of the result set, processed using a closure function, and then processed in the next section, which is typically used to process large amounts of data in a artisan command-line program.

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

In the closure function, if returned false , the subsequent processing is stopped.

Querying a list of columns from a data table #

For example, we want to query out all the field values in the role table title

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

The pluck function here has two parameters

Collection Pluck (String $column, string|null $key = null)

The first parameter is the column to query, and the second parameter is the key for each column

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

Aggregation functions

The Query Builder also provides some aggregation functions such as count，max，min，avg，sum

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

Specify select query criteria

Query the specified column #

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

If you have specified select, but want to add some fields again, use it Addselect method

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

Querying for different results distinct#

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

Using Native Expressions #

Use the DB::raw method to inject the required SQL fragment into the query, but it is highly deprecated and does not produce SQL injection

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

Join operation

Internal Connection Inner join#

Use join to perform an inner JOIN operation, the first parameter of the function is the name of the table to be joined, and the other parameters specify the connection constraints

$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 to the left join#
Use the Leftjoin method to perform a left JOIN operation with the same parameters as the join $users =

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

Advanced Join Method #

If the constraints of the Join method are more complex, you can use the closure function to specify

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

If you want to use column values in a join constraint to compare with a specified array, you can use the Where and Orwhere methods

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

Union operation

To use the union operation, you can first create a query and then use the Union method to bind the second query

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

Similarly, unionAll the method is also available, with the same parameters as the Union.

where query criteria

A simple wehere condition #

Using the Where method to add a where condition to a query, the function typically requires three parameters: column name, operator (any database-supported operator is allowed), column value.

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