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 otherGithub blogs and short books to communicate with each other!

Version: Laravel 5.2
Database:mysql5.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\DBappearance 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 internallycallStaticThe 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 aStdClassobject.
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

titles in the role table.Field value

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

pluckfunction here has two parameters

Collection pluck( string $column, string|null $key = null)

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

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');

#

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

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

#

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

expression

DB::rawmethod 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('status', '<>', 1) ->groupBy('status') ->get();

$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();

#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();

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();

$first = DB::table('users') ->whereNull('first_name'); $users = DB::table('users') ->whereNull('last_name') ->union($first) ->get();

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

$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!