我想减少laravel中的查询大小.
我的查询看起来像这样(我缩短了它,它大约是这行数量的10倍)：
$users = User::where("interface_art",'=',1)->where('role',2)->where('commstatus',$unavailableCheck)
->orWhere("interface_art",1)
->orWhere("web_art",$unavailableCheck)
->orWhere("web_art",1)
->orWhere("illustration_art",$unavailableCheck)
->orWhere("illustration_art",1)
->orWhere("brush_art",$unavailableCheck)
->orWhere("brush_art",1)
->orWhere("typography_art",$unavailableCheck)
->orWhere("typography_art",1)
->orWhere("identity_art",$unavailableCheck)
->orWhere("identity_art",1)
->orWhere("vector_art",$unavailableCheck)
->orWhere("vector_art",1)
->orderBy($orderByString,'desc')
->paginate(1);
如您所见,它有点多余.
对于每种艺术类型,如果他们的commstatus等于“1”或“$unavailable”,我希望用户获得“2”的角色.
起初,我试图通过不在每个“where”子句的末尾添加“role”或“commstatus”来缩短它,并在底部写另一个$users = $users :: where(“role”,“=”,例如,“2”),但我似乎无法找到正确的语法.
有没有办法缩短这个查询？
解决方法
正如其他人所说,您的架构可能会从重构到规范化中受益.但是,我相信您仍然可以重构现有查询,以减少冗余和可读性.
Laravel能够处理包含嵌套参数分组的Advanced Where Clauses.从文档：
The Closure will receive a query builder instance which you can use to set the constraints that should be contained within the parenthesis group.
考虑到这一点,您应该能够像这样重构查询：
$users = User::whereIn('commstatus',[$unavailableCheck,1])
->where('role',2)
->where(function ($query) {
$query->where("interface_art",1)
->orWhere("web_art",1)
->orWhere("illustration_art",1)
->orWhere("brush_art",1)
->orWhere("typography_art",1)
->orWhere("identity_art",1)
->orWhere("vector_art",1);
})
->orderBy($orderByString,'desc')
->paginate(1);
这将创建一个SQL查询：
SELECT * FROM users
WHERE commstatus IN ($unavailableCheck,1)
AND role = 2
AND (interface_art = 1 OR illustration_art = 1 OR ... etc)
