Method of laravel Skill Query Constructor Query Builder Overlay Chain Call

Introduction to Query Constructor

The Laravel Query Constructor (query builder) provides a convenient and smooth interface for establishing and executing database lookup syntax

Use PDO parameter binding to protect the application from SQL injection. Therefore, the passed-in parameters do not need to escape extra special characters

It can basically satisfy all database operations and can be executed on all supported database systems

Introduction

Today, I will introduce you to a small skill of laravel query constructor, which is not mentioned in detail in the official document example, and is not a high-end skill. Many people may use it. Students who don't know can have a look.

In business code, we often query according to different conditions. For a simple example, we now want to query the user list, which is arranged in reverse order in time, and may have status and type as qualification conditions.

1 This is what I wrote at the beginning

 if($status && $type) {
 $users = User::where('status', $status)->where('type', $type)->latest()->get();
 } else if ($status) {
 $users = User::where('status', $status)->latest()->get(); 
 } else if ($type) {
 $users = User::where('status', $type)->latest()->get();
 } else {
 $users = User::latest()->get(); 
 }

This code is really ugly, a lot of common code, such as- > latest()- > get (), written 4 times, if the product says that we want to arrange in positive order today, then you have to change 4 places. Although it is quick to change 1 with the help of editor, it is only the simplest example.

Looking at the following document, there is an when method for conditional judgment, and 1 heap closure is not ideal. I firmly believe that there must be a more elegant writing, so I searched for a wave on stackoverflow, and sure enough, the omnipotent nuts gave me the answer.

Improved writing:

 $query = User::query();
 //  If you use DB: $query = DB::table('user'); 
 if ($status) {
  $query->where('status', $status);
 }
 if ($type) {
  $query->where('type', $type);
 } 
 $users = $query->latest()->get();

Save the query constructor instance with variables, then superimpose constraints on it, and finally the get collection. The public part is placed at the beginning and end, and the structure is clear. Is it a judgment?

Moreover, we can also pass $query as a parameter into a method or function, and encapsulate the common logic in 1, which is convenient for multiple calls:

 function foo($query) {
  $query->with(['girl', 'gay'])
    ->latest()
    ->get();
 } 
 $query = User::query();
 $users = foo($query);

One thing to note about this is that once you call a constraint such as where on $query, it will change this query, and sometimes we need to advance clone1 query.

For example, for example, we want to get users with type of 1 and 2 at the same time

 $query_1 = User::query();
 $query_2 = clone $query_1; 
 $users_1 = $query_1->where('type', 1)->latest()->get();
 $users_2 = $query_2->where('type', 2)->latest()->get();
 //  Errors  $users_2 = $query_1->where('type', 1)->latest()->get();
 //  What you can write in this way is type = 1 and $type = 2

Although this example is not written in the document of laravel, it mentions 1:

You can start the query using the table method of DB facade. This table method returns 1 query constructor instance for the query table, allowing you to chain up more constraints while querying and use the get method to get the final result

Digression

Before listening to some old-timers said they don't only Baidu programmers, at that time, I felt really loaded beep, not all search engines, because I didn't use google at that time. Now I don't want to work with people who only know Baidu. Baidu is just an advertisement search. What are the things that are searched out?

google, stackoverflow is really a good thing. Many nuts are rich in knowledge and professional in answering questions, from computer history to operating system, database and various programming languages, which help me de a lot of bug. Is it not good to advertise like this in segmentfault, and slip away!

Summarize

Reference:

How to create multiple where clause query using Laravel Eloquent? - stackoverflow Model::query - laravelAPI