sql - Laravel Query:为 Query Builder 实现 Eloquent Scope
问题描述
在 Laravel Query Builder 中,我想在 Eloquent 中实现类似 Scope 的东西。参考:Laravel 查询:添加自定义功能,如 Soft Deletes。
我有一些复杂的查询(有连接和什么没有),但我希望能够轻松应用 WHERE 条件,其工作方式如下:
原来的:
Select * from t1 join t2 ... join t3 ... etc
Where t1.c1 = x OR t3.c4 like "%like"
通缉:
Select * from t1 join t2 ... join t3 ... etc
Where (t1.c1 = x OR t3.c4 like "%like") AND (t1.isTest = false AND t3.isTest = false)
我写了以下方法:
public static function scopeNoTest($query, $tables=[false])
{
if (!is_array($tables)) $tables = [$tables];
foreach ($tables as $table)
{
$field = ($table) ? $table . '.isTest' : 'isTest';
$query = $query->where(function ($q) use ($query, $field)
{
$q->where($field, false)
->orWhereNull($field);
}
);
}
return $query;
}
它像这样运行:
$select = <parameter driven select statement>
$where[$role] = <array of different where condition based on passed in parameter?
$bindings = <query bindings based on passed in parameters>
$query = DB::table('Transactions AS trans')
->leftJoin('Buyers AS b', 'trans.ID', '=', 'b.Transactions_ID')
->leftJoin('Sellers AS s', 'trans.ID', '=', 's.Transactions_ID')
->leftJoin('Agents AS ba', 'trans.BuyersAgent_ID', '=', 'ba.ID')
->leftJoin('Agents AS sa', 'trans.SellersAgent_ID', '=', 'sa.ID')
->leftJoin('TransactionCoordinators AS btc', 'trans.BuyersTransactionCoordinators_ID', '=', 'btc.ID')
->leftJoin('TransactionCoordinators AS stc', 'trans.SellersTransactionCoordinators_ID', '=', 'stc.ID')
->leftJoin('lu_UserRoles AS lu_ur', 'trans.ClientRole', '=', 'lu_ur.Value')
->leftJoin('Properties AS p', 'trans.Properties_ID', '=', 'p.ID')
->selectRaw($select);
// ... Adds code to Only Select records with isTest NOT True
$query = Model_Parent::scopeNoTest($query, ['trans', 'ba', 'sa', ]);
$query->whereRaw($where[$role].$whereUser, $bindings)->distinct();
$transactions = $query->get();
这段代码的问题在于它没有将原始的 [passed in] 查询放在括号中——所以查询是错误的!.
代码创建的 WHERE 是:
where
(`trans`.`isTest` = 0 or `trans`.`isTest` is null)
and (`ba`.`isTest` = 0 or `ba`.`isTest` is null)
and (`sa`.`isTest` = 0 or `sa`.`isTest` is null)
and trans.BuyersTransactionCoordinators_ID = 1 OR trans.SellersTransactionCoordinators_ID = 1
OR trans.CreatedByUsers_ID = 1 OR trans.OwnedByUsers_ID = 1
而且我要
where
(`trans`.`isTest` = 0 or `trans`.`isTest` is null)
and (`ba`.`isTest` = 0 or `ba`.`isTest` is null)
and (`sa`.`isTest` = 0 or `sa`.`isTest` is null)
and (trans.BuyersTransactionCoordinators_ID = 1 OR trans.SellersTransactionCoordinators_ID = 1
OR trans.CreatedByUsers_ID = 1 OR trans.OwnedByUsers_ID = 1)
有没有办法做到这一点 ??
解决方案
看起来以下行导致了这种情况:
$query->whereRaw($where[$role].$whereUser, $bindings)->distinct();
我认为有两种方法可以解决这个问题:
// 1
->whereRaw('(' . $where[$role].$whereUser . ')', $bindings)->
// 2
->where(function ($query) use (...) {
$query->whereRaw(...);
})->
推荐阅读
- c - 计算和打印矩阵的对角线之和
- javascript - 如何在 Angular 中使用 addEventListener 和 postMessage?
- php - 如何将日期、时间和值从单选按钮放入数据库
- c# - 逐个像素地动态绘制和显示,有一些延迟
- npm - 如何从 Nexus oss 代理获取依赖于 github 项目的 npm 包
- django - Django 测试 - 发送包含整数的数组数组
- android - 在 android 中使用改造登录
- json - 我如何在 nunjuncks 中乘以数据?
- swift - 为什么不能在swift中将可变参数标记为inout?
- vue.js - Vue Js 2 / Vue-CLI 3 / 托管时显示空白页