Detailed explanation of usage examples of laravel 5.1 framework database query builder

Time:2021-10-20

This article describes the usage of database query builder of laravel 5.1 framework. Share with you for your reference, as follows:

Today, let’s talk about query builder. It is simpler than running native SQL, and it has a wide range of operations.

1. Query results

Let’s take a look at its syntax:


  public function getSelect()
  {
    $result = DB::table('articles')->get();
    dd($result);
  }

The query builder is returned through the table method. You can use get () to return a result set (array type). Here, you can return all data. Of course, you can also link many constraints.

1.1 get a column / row of data

public function getSelect()
  {
    $result = DB::table('articles')->where('title', 'learn database')->get();  //  Get the entire column of data
    $articles = DB::table('articles')->where('title', 'learn database')->first(); //  Get a row of data
    dd($result, $articles);
  }

We can add conditions through where.

1.2 get data column value list

If you want to get the value of a column, you can use the lists method:


  public function getSelect()
  {
    $result = DB::table('articles')->where('id', '<', 2)->lists('title');
    $titles = DB::table('articles')->lists('title');
    dd($result, $titles);
  }

1.3 get chunk result set

When there are a lot of data in our data table, we can use chunk result set, which is to obtain a small piece of data at a time for processing


  public function getSelect()
  {
    DB::table('articles')->chunk(2, function ($articles){
      foreach ($articles as $article){
        echo $article->title;
        echo "<br />";
      }
    });
  }

If you want to terminate the block operation, you can return false:


  public function getSelect()
  {
    DB::table('articles')->chunk(2, function ($articles){
      return false;
    });
  }

1.4 aggregate function

The builder also provides many practical methods for us to use:

  • Count method: returns the amount of data queried by the builder.
  • Max method: pass in a column and return the largest value in this column.
  • Min method: similar to Max method, it returns the minimum value.
  • Sum method: returns the sum of a list of values.
  • AVG method: calculate the average value.

1.4.1 count


  public function getArticlesInfo()
  {
    $article_count = DB::table('articles')->count();
    dd($article_count);
  }

1.4.2 max


  public function getArticlesInfo()
  {
    $maxCommentCount = DB::table('articles')->max('comment_count');
    dd($maxCommentCount);
  }

1.4.3 sum


  public function getArticlesInfo()
  {
    $commentSum = DB::table('articles')->sum('comment_count');
  }

1.4.4 avg


  public function getArticlesInfo()
  {
    $commentAvg = DB::table('articles')->avg('comment_count');
    dd($commentAvg);
  }

1.5 select query

1.5.1 self defined clause

The select statement can obtain the specified column, and can customize the key:

public function getArticlesInfo()
  {
    $articles = DB::table('articles')->select('title')->get();
    //Output results:
//    array:3 [▼
//      0 => {#150 ▼
//          +"title": "laravel database"
//      }
//      1 => {#151 ▼
//          +"title": "learn database"
//       }
//       2 => {#152 ▼
//          +"title": "alex"
//       }
//      ]
    $articles1 = DB::table('articles')->select('title as articles_title')->get();
    //Output results:
//    array:3 [▼
//       0 => {#153 ▼
//          +"articles_title": "laravel database"
//       }
//       1 => {#154 ▼
//          +"articles_title": "learn database"
//       }
//       2 => {#155 ▼
//          +"articles_title": "alex"
//       }
//      ]
    $articles2 = DB::table('articles')->select('title as articles_title', 'id as articles_id')->get();
//    array:3 [▼
//       0 => {#156 ▼
//          +"articles_title": "laravel database"
//          +"articles_id": 1
//       }
//       1 => {#157 ▼
//          +"articles_title": "learn database"
//          +"articles_id": 2
//       }
//       2 => {#158 ▼
//          +"articles_title": "alex"
//          +"articles_id": 3
//       }
//      ]
  }

1.5.2 distinct method

As for the distinct method, I haven’t figured out what it means and what scenario it applies to. I also welcome the great gods to give an answer. Thank you

The distinct method allows you to force a query to return a non duplicate result set.


  public function getArticlesInfo()
  {
    $articles = DB::table('articles')->distinct()->get();
  }

1.5.3 addselect method

If you want to add a select, you can do this:


  public function getArticlesInfo()
  {
    $query = DB::table('articles')->select('title as articles_title');
    $articles = $query->addSelect('id')->get();
    dd($articles);
  }

2 where statement

The where statement is often used for conditional filtering.

2.1 where Basics

Now let’s introduce the where method in detail, which receives three parameters:

  1. Listing, there’s nothing to say about this.
  2. The database system supports operators such as “=”, “<“, “like”. If the second parameter is not passed in, the default is “=” equal to “.
  3. The value to compare.
public function getArticlesInfo()
  {
    $articles1 = DB::table('articles')->where('id','2')->get();     //  be equal to
    $articles2 = DB::table('articles')->where('id','>','2')->get();   //  greater than
    $articles3 = DB::table('articles')->where('id','<>','2')->get();  //  Not equal to
    $articles4 = DB::table('articles')->where('id','<=','2')->get();  //  Less than or equal to
    $articles5 = DB::table('articles')->where('title','LIKE','%base')->get();  //  similar
  }

2.2 orWhere

The parameters received by orwhere and where are the same. When the where logic does not find or, it finds the result returned by or, and when the where logic finds or, it also finds the result returned by them.


  public function getArticlesInfo()
  {
    $articles = DB::table("articles")->where('id','=','5')->orWhere('title','laravel database')->get();
    dd($articles);
  }

2.3 where between and where not between

Where between refers to whether the column value is between the given values:


  public function getArticlesInfo()
  {
    $articles = DB::table("articles")->whereBetween('id', [1, 3])->get();
    dd($articles);
  }

↑ the above code is a set of search IDS between 1 and 3.

Where not between is the opposite of where between:


  public function getArticlesInfo()
  {
    $articles = DB::table("articles")->whereNotBetween('id', [1, 3])->get();
    dd($articles);
  }

↑ the above code is to find a set whose ID is not between 1 and 3.

2.4 wherein and wherenotin

Where in is to find the column value in a given set of data:


  public function getArticlesInfo()
  {
    $articles = DB::table("articles")->whereIn('id', [1, 3, 5, 8])->get();
    dd($articles);
  }

↑ the above code is to find the set with ID 1, 3, 5 and 8, but our database only has data with ID 1 and 3, so it will only return the set with ID 1 and 3.

Wherenotin is the opposite of wherein:


  public function getArticlesInfo()
  {
    $articles = DB::table("articles")->whereNotIn('id', [1, 3, 5, 8])->get();
    dd($articles);
  }

↑ the above code is a set of search IDS other than 1, 3, 5 and 8.

2.5 wherenull and wherenotnull

Where null is to find data with empty column value:


  public function getArticlesInfo()
  {
    $articles = DB::table("articles")->whereNull('created_at')->get();
    dd($articles);
  }

↑ in the above code, find created_ Collection with empty at.

Where not null goes without saying:


  public function getArticlesInfo()
  {
    $articles = DB::table("articles")->whereNotNull('created_at')->get();
    dd($articles);
  }

↑ in the above code, find created_ Collection whose at is not empty.

3 insert data

Let’s first look at the simplest insertion method:

public function getInsertArticle()
  {
    //Insert a piece of data:
    DB::table('articles')->insert(
      ['title'=>'get more', 'body'=>'emmmmmm......']
    );
    //Insert multiple pieces of data:
    DB::table('articles')->insert([
      ['title'=>'testTitle1', 'body'=>'testBody1'],
      ['title'=>'testTitle2', 'body'=>'testBody2'],
      // ....
    ]);
  }

When you need to get the ID of the inserted data, you can use the method of obtaining the self increment ID:

public function getInsertArticle()
  {
    //Insert a piece of data:
    $id = DB::table('articles')->insertGetId(
      ['title'=>'get more', 'body'=>'emmmmmm......']
    );
    dd($id);
  }

4 update


  public function getUpdateArticle()
  {
    $result = DB::table('articles')->whereBetween('id', [1, 3])->update(['comment_count'=>0]);
    dd($result);
  }

↑ update can also return several pieces of data affected.

4.1 quick method of addition / subtraction


  public function getUpdateArticle()
  {
    $result = DB::table('articles')->whereBetween('id', [1, 3])->increment('comment_count',2);
    dd($result);
  }

↑ increment accepts 1 ~ 2 parameters. The first parameter is the column name, and the second parameter is optional, indicating how many to add (1 by default). The above statement is: comment_ The value of the count column increases by 2.


  public function getUpdateArticle()
  {
    $result = DB::table('articles')->whereBetween('id', [1, 3])->decrement('comment_count',2);
    dd($result);
  }

↑ increment accepts 1 ~ 2 parameters. The first parameter is the column name, and the second parameter is optional, indicating how much to reduce (1 by default). The above statement is: comment_ The value of the count column is reduced by 2.

Do you think the addition and subtraction shortcut only accepts two parameters? Nonnonit can also receive a third parameter:


  public function getUpdateArticle()
  {
    $result = DB::table('articles')->whereBetween('id', [1, 3])->increment('comment_count', 2, ['title' => 'testUpdate']);
    dd($result);
  }

↑ it can also modify other columns when increasing / decreasing.

5 delete


  public function getDeleteArticle()
  {
    $result = DB::table('articles')->whereBetween('id', [1, 3])->delete();
    dd($result);
  }

↑ delete is used to delete data, which also returns how many rows are affected.

When you want to delete all columns and set the auto increment ID to 0, you can do this:


  public function getDeleteArticle()
  {
    DB::table('articles')->truncate();
  }

6 lock

The query builder also contains some methods to help you implement “pessimistic lock” in the select statement. You can use the sharedlock method in the query to bring a “shared lock” when running the statement. Shared lock can prevent the selected row from being modified until the transaction is committed:


DB::table('articles')->where('id', '>', 100)->sharedLock()->get();

You can also use the lockforupdate method. ” For update “to prevent the selected row from being modified or deleted by other shared locks:


DB::table('articles')->where('id', '>', 100)->lockForUpdate()->get();

For more information about laravel, readers who are interested can see the special topics on this site: introduction and advanced tutorial of laravel framework, summary of PHP excellent development framework, introduction to PHP object-oriented programming, introduction to PHP + MySQL database operation, and summary of common database operation skills for PHP

I hope this article will be helpful to your PHP Programming Based on laravel framework.