Laravel learning - sample code sharing for database operations and query constructors-PHP Tutorial-php.cn

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

黄舟

Release： 2023-03-06 19:36:02

Original

1493 people have browsed it

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(&#39;select * from users where id = ?&#39;, [1]);foreach ($results as $res) {    echo $res->name;
}

Copy after login

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(&#39;select * from users where id = :id&#39;, [&#39;id&#39; => 1]);

Copy after login

Get all the objects from the data table. Data column

$users = DB::table(&#39;users&#39;)->get();

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

Copy after login

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(&#39;users&#39;)->where(&#39;name&#39;, &#39;John&#39;)->first();
echo $user->name;

Copy after login

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(&#39;users&#39;)->where(&#39;name&#39;, &#39;John&#39;)->value(&#39;email&#39;);

Copy after login

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(&#39;users&#39;)->chunk(100, function($users){
    foreach ($users as $user)
    {        //
    }
});

Copy after login

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(&#39;roles&#39;)->pluck(&#39;title&#39;);foreach ($titles as $title) {    echo $title;
}

Copy after login

The

pluck function here has two parameters

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

Copy after login

The first parameter is the column to be queried, and the second parameter is each column The key

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

Copy after login

Aggregation function

The query builder also provides some aggregate functions such as

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

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

Copy after login

Specify select query conditions

Query the specified columns

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

Copy after login

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

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

Copy after login

Query different Result distinct

$users = DB::table(&#39;users&#39;)->distinct()->get();

Copy after login

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(&#39;users&#39;)
      ->select(DB::raw(&#39;count(*) as user_count, status&#39;))
      ->where(&#39;status&#39;, &#39;<>&#39;, 1)
      ->groupBy(&#39;status&#39;)
      ->get();

Copy after login

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(&#39;users&#39;)
  ->join(&#39;contacts&#39;, &#39;users.id&#39;, &#39;=&#39;, &#39;contacts.user_id&#39;)
  ->join(&#39;orders&#39;, &#39;users.id&#39;, &#39;=&#39;, &#39;orders.user_id&#39;)
  ->select(&#39;users.*&#39;, &#39;contacts.phone&#39;, &#39;orders.price&#39;)
  ->get();

Copy after login

Left Join

#Use the leftJoin method to perform the left join operation. The parameters are the same as join $users =

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

Copy after login

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>

Copy after login

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(&#39;users&#39;)  
->join(&#39;contacts&#39;, function ($join) {       
$join->on(&#39;users.id&#39;, &#39;=&#39;, &#39;contacts.user_id&#39;)            
->where(&#39;contacts.user_id&#39;, &#39;>&#39;, 5);
   })   ->get();

Copy after login

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(&#39;users&#39;)
            ->whereNull(&#39;first_name&#39;);
$users = DB::table(&#39;users&#39;)
            ->whereNull(&#39;last_name&#39;)
            ->union($first)
            ->get();

Copy after login

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(&#39;users&#39;)->where(&#39;votes&#39;, &#39;=&#39;, 100)->get();
$users = DB::table(&#39;users&#39;)->where(&#39;votes&#39;, 100)->get();

Copy after login

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!