Laravel learning - sample code sharing for database operations and query constructors

Recently, the editor is studying what is known as the best framework in the world – Laravel. In fact, learning framework is also the idea of ​​learning framework! I want to record some of my experiences in laravel learning in my blog. Everyone is welcome to follow my other Github blogs and short books to communicate with each other!

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

Database operationandQueryConstructor

There are two ways to perform database operations in Laravel, one One is to use the static method of the \DB appearance object to directly execute the sql query, and the other is to use the static method of the Model class (actually also the implementation of Facade, using the static access method to access the Model, which is used internally callStaticThe magic method proxies access to the member method

Query operation

Use sql statement to execute the select query operation

#

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

The return result is. Array, each value in the array is a StdClass object.
You can also use named binding. It is recommended to use this method, which is more clear.

$results = DB::select('select * from users where id = :id', ['id' => 1]);

Get all the objects from the data table. Data column

#

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

foreach ($users as $user)
{
    var_dump($user->name);
}

Query a single row/column from the table

Use the first method to return a single row of data. This method returns a stdClass object

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

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

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

Search the data column in blocks from the data table

This method is used for operations with a large amount of data in the data table. Take out a part of the result set at a time, use the closure function to process it, and then process the next part. This command is generally used in the Artisan command line program to process large amounts of data

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

In the closure function, if it returns#. ##false, will stop subsequent processing

Query the list of a certain column from the data table

#For example, we want to query all

titles in the role table. Field value

$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 be queried, and the second parameter is each column The key

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

Aggregation function

The query builder also provides some aggregate functions such as

count, max, min, avg, sum, etc.

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

Specify select query conditions

Query the specified columns

#

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

If select has been specified, but you want to add some fields again, use its addSelect method

$query = DB::table('users')->select('name');$users = $query->addSelect('age')->get();

Query different Result distinct

#

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

Use native

expression

#Use the

DB::raw method to inject the required sql fragment into the query, but It is highly not recommended to use this method. If used incorrectly, sql injection may occur.

$users = DB::table('users')
      ->select(DB::raw('count(*) as user_count, status'))
      ->where(&#39;status&#39;, &#39;<>&#39;, 1)
      ->groupBy(&#39;status&#39;)
      ->get();

Join operation

Inner Join

#Use join to perform the inner join operation. This function is One parameter is the name of the table to be connected, 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

#Use the leftJoin method to perform the left join operation. The parameters are the same as 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 specify them in the form of a closure function.

<p style="margin-top: 7px;">DB::table(&#39;users&#39;)<br/>   ->join(&#39;contacts&#39;, function ($join) {<br/>       $join->on(&#39;users.id&#39;, &#39;=&#39;, &#39;contacts.user_id&#39;)->orOn(...);<br/>   })<br/>   ->get();<br/></p>

If you want to use the column value in the join constraint to compare with the 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, the

unionAll method can also be used, with the same parameters as union.

Where query conditions

Simple where conditions

#Use the where method to add where conditions to the query. This function generally requires three parameters: column name, operator (any All operators supported by the database can be used), column values.

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

The above is the detailed content of Laravel learning - sample code sharing for database operations and query constructors. For more information, please follow other related articles on the PHP Chinese website!