ThinkPHP: Basic principles of data query

Please try to incorporate the several basic principles of data query involved in this article into your project specifications, which is also the best practice advocated by the official. Before that, I hope you have read a previous blog: "Do you really understand the correct usage posture of Db classes and models?".

Try not to use array conditional query

Most of the confusing query syntax is caused by the use of array query, and the usage of array conditional query in 5.1 is the same as that in 5.0 Completely different. If you are used to the array query method of 5.0, I suggest you read this article: "Teach you how to use the array object query of 5.1".

The following may be a query mistake that many novices tend to make.

$where['id'] = ['in', '1,2,3'];
User::where($where)->select();

Obviously, this query thinking is deeply influenced by the old version. Compared with 5.0, the query syntax of version 5.1 is more object-oriented. The following is the correct usage.

$where['id'] = [1,2,3];
User::where($where)->select();

Perhaps because PHP arrays are so easy to use, many people enjoy array query conditions (or are they anxious about objects?). But if you use the query builder correctly and cooperate with the related features of the model, your query logic can become clearer and easier to maintain.

Moreover, under some more complex query conditions, you cannot use arrays to complete the query, such as the following query usage.

User::where('id', '>', 100)
    ->whereOr(&#39;id&#39;, &#39;<&#39;, 10)
    ->where(&#39;name&#39;, &#39;like&#39;, &#39;think%&#39;)
    ->whereColumn(&#39;name&#39;, &#39;nickname&#39;)
    ->when(&#39;80&#39;== $condition, function ($query) {
        $query->where(&#39;score&#39;, &#39;>&#39;, 80)->limit(10);
    })->select();

So, unless you know the usage of array query in 5.1, please try not to use array condition query.

Use string query conditions safely

When using string query conditions, if there are external variables, be sure to use parameter binding, and preferably use whereRaw method, which can be mixed with other query builder methods.

User::whereRaw("id = :id AND name = :name", [
        'id' => [$id, \PDO::PARAM_INT] , 
        'name' => $name
    ])->where('status', 1)
    ->order('id', 'desc')
    ->select();

For some queries that are more concerned about performance, you can also directly use the query or execute method, but you must also pay attention to the safety of parameters and consider the transplantation issues of different databases.

Db::query("select * from think_user where id=? AND status=?", [8, 1]);
Db::execute("update think_user set name=:name where status=:status", ['name' => 'thinkphp', 'status' => 1]);

Use Raw mechanism for queries that use SQL functions

If your query contains SQL functions, please use whereRaw (or whereExp), orderRaw or fieldRaw method.

User::whereExp('nickname', "= CONCAT(name, '-', id)")
    ->orderRaw("field(name,'thinkphp', 'kancloud')")
    ->fieldRaw('id,SUM(score)')
    ->select();

Use closures appropriately, but don’t abuse them

Closure queries have some special uses in the query constructor, but there is no need to abuse them unless necessary.

Typical usage scenarios of closure queries include the following.

Closures are usually used in conditional queries to represent a set of conditional queries.

User::when($condition, function ($query) {
    // 满足条件后执行
    $query->where('score', '>', 80)->limit(10);
}, function ($query) {
    // 不满足条件执行
    $query->where('score', '>', 60);
})->select();

Closures are often used in some subqueries.

User::whereIn('id', function ($query) {
    $query->table('profile')
        ->where('name', 'like', 'think%')
        ->field('id');
})->select();

Generate a set of closed query conditions

User::where('id', '>', 100)
    ->whereOr(function($query) {
        $query->where('name', 'like', 'think%')
        ->whereColumn('name', 'nickname');
    })->select();

In this query usage, the query conditions in the closure will be added with parentheses on both sides to become a closed query condition.

In many related preloading queries, closures can be used to filter related data.

User::with(['profile' => function($query) {
$query->field('user_id,email,phone');
}])->select([1,2,3]);

Try to reuse your query conditions

All query conditions should be defined in one place and reused in multiple places, such as encapsulating them into model methods, especially Don't write a bunch of complex query conditions directly into your controller code, otherwise once the business is adjusted, it will be a nightmare for the world's search codes to change your query conditions.

You may have seen many ways of directly encapsulating query conditions in the controller in the official manual or some tutorials, but that is only for the convenience of showing usage, and is not advisable.

In some medium and large application architecture designs, the model is usually divided into data layer, logic layer and service layer, and the controller will only call service layer methods. The query logic is basically encapsulated into the logic layer, and the data layer only makes various definitions of the model.

In simple applications, PHP's Trait mechanism can also be used to implement the code reuse mechanism.

Use query scope or searcher to simplify query

If you use model query, try to encapsulate your query conditions into query scope or searcher method, query The main difference between a range and a searcher is that the query range is more suitable for defining a set of (multiple fields) query conditions. If you want to call multiple query ranges, you need to call multiple times, while the searcher is more suitable for defining a field (in fact, it is not absolute) query. Conditions, you only need to call the withSearch method once.

Examples of using query scopes and searchers.

<?php
namespace app\index\model;
use think\Model;
class User extends Model
{
    public function scopeVip($query)
    {
        $query->where(&#39;user_type&#39;, &#39;vip&#39;)
            ->where(&#39;status&#39;, 1)
            ->field(&#39;id,name&#39;);
    }
    
    public function searchAgeAttr($query, $age)
    {
        $query->where(&#39;age&#39;,&#39;>&#39;,$age);
    }    
    
    public function searchScoreAttr($query, $score)
    {
        $query->where(&#39;score&#39;,&#39;<=&#39;,$score)->where(&#39;score&#39;, &#39;>&#39; ,0);
    }    
}

Controller code

<?php
namespace app\index\controller;
use think\Controller;
use think\Request;
class index extends Controller
{
    public function index(Request $request)
    {
        // 查询VIP会员
        User::vip()->select();
        // 查询年龄和分数
        User::withSearch([&#39;age,&#39;score&#39;&#39;], $request->param())->select();
    }
}

In the controller code, we only focus on the business logic itself, and do not need to pay attention to the query conditions inside this logic. For more detailed information about searchers and query ranges, please refer to the official manual.

PHP Chinese website has a large number of free ThinkPHP introductory tutorials, everyone is welcome to learn!

This article is reproduced from: https://blog.thinkphp.cn/833794

The above is the detailed content of ThinkPHP: Basic principles of data query. For more information, please follow other related articles on the PHP Chinese website!