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:\DB
The 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 internallycallStatic
The 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.StdClass
Object.
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 alltitle
Field value
$titles = DB::table('roles')->pluck('title');foreach ($titles as $title) { echo $title;}
Herepluck
The 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,sum
And 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::raw
This 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,unionAll
The 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!