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

There are two ways to perform database operations in Laravel. one is to use the static method of the \ DB appearance object to directly execute SQL queries, another method is to use the static method of the Model class (in fact, it is also the implementation of the Facade, the method of accessing the Model using the static access method, the _ callStatic magic method is used internally to proxy access to the member method.

Recently, Xiaobian is learning Laravel, the world's best framework. Actually, the learning framework is the idea of the learning framework! I want to record some of my experiences in laravel's learning in my blog. You are welcome to share with me other Github blogs and short books!

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

Database operations and query constructor

There are two ways to perform database operations in Laravel:\DBThe static method of the appearance object directly executes SQL query. The other method is to use the static method of the Model class (in fact, it is also the implementation of Facade. the method of accessing the Model through static access is adopted internallycallStaticThe magic method proxy accesses member methods.

Query operations

Use an SQL statement to perform the select query operation #

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

The returned result is an array. each value in the array is an array.StdClassObject.
You can also use Name Binding. we recommend that you use this method to make it clearer.

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

Retrieve all data columns from a data table #

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

Query a single row/column from a table

Returns a single row of data using the first method, which returns an stdClass object.

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

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

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

Split data columns from data tables

This method is used to operate a data table with a large amount of data. each time a part is taken from the result set, the closure function is used for processing, and then the next part is processed, this command is generally used to process a large amount of data in the Artisan command line program.

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

In the closure function, iffalse.

Query the list of a column from a data table #

For example, we want to query alltitleField value

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

HerepluckThe function 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 the key of each column.

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

Aggregate Functions

The query constructor also provides clustering functions suchcount，max，min，avg，sumAnd so on

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

Select query conditions

Query the specified column #

$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 results distinct #

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

Use native expressions #

UseDB::rawThis method can inject the required SQL segments into the query. However, this method is not recommended.

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

Join operation

Inner Join #

Use join to perform the internal join operation. The first parameter of the function is the name of the table to be connected. Other parameters specify the join constraint.

$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 parameter is the same as the join operation $ users =

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

Advanced Join method #

If the constraints of the join method are 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 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 bind the second query using the union method.

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

Similarly,unionAllThe method is also usable, and the parameters are the same as those of union.

Where query conditions

Simple wehere condition #

The where method is used to add the where condition to the query. this function generally requires three parameters: column name, operator (any operator supported by the database), and column value.

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

The above is the details shared by Laravel learning-sample code for database operations and query constructor. For more information, see other related articles in the first PHP community!