时间戳

首页 > 解决方案 > Laravel - 如何用 builder 上的 where 子句包围所有先前的子句？

php - Laravel - 如何用 builder 上的 where 子句包围所有先前的子句？

问题描述

在数据库表中，我有一行：

users
id|email|is_deleted
1|test@test.com|1

我有这个代码：

User::where('email', 'test@test.com')
    ->orWhere('email', 'test2@test2.com')
    ->get();

并生成此查询：

select * from users where email = 'admin@myzone.com' or email = 'asdasdas'

有一个结果。现在我想申请 where is_deleted = 0

如果我喜欢这样：

User::where('email', 'test@test.com')
    ->orWhere('email', 'test2@test2.com')
    ->where('is_deleted', 0)
    ->get();

生成的查询是：

select * from "users" where "email" = ? or "email" = ? and "users"."deleted_at" is null

到目前为止，一切都按预期工作，此查询返回一个结果，但我只想不删除用户，我可以执行以下操作：

User::where(function($query){
    $query->where('email', 'test@test.com')
        ->orWhere('email', 'test2@test2.com')
})->where('is_deleted', 0)
->get();

这会起作用，但在我的代码中我已经返回了 builder ：

function applyNotDeleted(Builder $builder){
    //here I want to filter only not deleted users, 
    //but this is already triggered on builder $query->where('email', 'test@test.com')->orWhere('email', 'test2@test2.com')

    //currently generated query on builder is select * from users where email = 'admin@myzone.com' or email = 'asdasdas'
    //but at this stage I want to create query which will look like select * from "users" where "email" = ? or "email" = ? and "users"."deleted_at" is null

    //something like this 
    $builderNew = $builderNew->where(function($query){
        $query->applyAllLogicFromCurrentBuilder($builder)
    })
    ->where('is_deleted', 0)
    ->get();
}

任何想法？

标签： phplaravellaravel-5eloquentlaravel-5.6

解决方案

我个人会使用查询范围来获取未删除的记录

public function scopeNotDeleted(Builder $query): Builder
{
    return $query->where('is_deleted', 0);
}

然后在获取记录时使用它

User::notDeleted()->where(function(Builder $query) {
    $query->where('email', 'test@test.com')->orWhere('email', 'test2@test2.com');
})->get();

如果您正在使用applyAllLogicFromCurrentBuilder方法，您还可以将其提取到查询范围，然后将其与您的调用链接起来，如下所示：

User::allLogicFromCurrentBuilder()->notDeleted()->where(function(Builder $query) {
    $query->where('email', 'test@test.com')->orWhere('email', 'test2@test2.com');
})->get();

您也可以保持applyNotDeleted方法不变，但不调用get()方法 - 这样您可以在需要时向其附加任何进一步的语句。我可能会将它转换为一种public static方法，这样您就可以在不实例化模型的情况下调用它：

User::applyNotDeleted(User::where(function(Builder $query) use ($email) {
    $query->where('email', 'test@test.com')->orWhere('email', 'test2@test2.com');
}))->get();

就个人而言，我更喜欢范围方法，因为它看起来更干净。

推荐阅读