Laravel skills: Query Builder overlay chain call method explanation

This article mainly introduces you to the relevant information about the query builder Query Builder overlay chain call in laravel skills. The article introduces it in detail through the example code, which has certain reference learning value for everyone's study or work. Friends who need it can come and take a look below.

Introduction to Query Builder

Laravel query builder (query builder) provides a convenient and fluent interface for establishing and executing database searches. Syntax

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

Basically, all database operations can be satisfied, and can be executed on all supported database systems

Introduction

Today I will introduce to you a little trick of laravel query constructor. It is not mentioned in detail in the official document examples. It is not a high-end skill. Many people may be using it. No. Students who know it can take a look.

In business code, queries are often made based on different conditions. For a simple example, we now want to query the user list, arranged in reverse chronological order, and there may be status and type as qualifying conditions.

This is how I wrote it 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, there are many common codes, such as ->latest() ->get(), written four times. If the product says that today we want to sort it in order, then you have to change four places. Although it is quick to change it with the help of an editor, you must know that this is only the simplest example.

After reading the document, there is a when method for conditional judgment. A bunch of closures are not ideal. I firmly believed that there must be a more elegant way of writing, so I searched on stackoverflow, and sure enough, the almighty Waiguoren gave me the answer.

Improved writing method:

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

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

And we can also pass $query as a parameter into a method or function, encapsulating common logic together to facilitate multiple calls:

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

There is a caveat in this way of writing. Once you call a constraint method such as where on $query, the query will be changed. Sometimes we need to clone a query in advance.

For example, for example, we want to get users of type 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();
 // 错误 $users_2 = $query_1->where('type', 1)->latest()->get();
 // 这样写得到得是type = 1 and $type = 2

Although this is not written in the laravel document Example, but mentioned:

You can use the table method of the DB facade to start the query. This table method returns a query builder instance for the query table, allowing you to chain more constraints when querying, and use the get method to obtain the final result

Digression

Previous I heard some seniors say that they don’t want programmers who only know Baidu. At that time, I felt really pretentious, not all search engines, because I didn’t use Google at that time. Now I don’t want to work with someone who only knows Baidu. Baidu is just an advertising search, and all the search results are nonsense.

Google and stackoverflow are really good things. Many of them have rich knowledge and professional answers. From computer history to operating systems, databases, and various programming languages, they have helped me debug a lot of bugs. Is it bad to advertise like this in segmentfault? Get away!

Summary

Reference：

1. How to create multiple where clause query using Laravel Eloquent? - stackoverflow

2. Model::query - laravelAPI

Sharing of PHP code to implement Fibonacci sequence

Example explanation of PHP implementation of array search function based on dichotomy

Detailed explanation of classes and objects in php

The above is the detailed content of Laravel skills: Query Builder overlay chain call method explanation. For more information, please follow other related articles on the PHP Chinese website!