dongliyan9190 2016-10-25 20:53
浏览 173

Laravel Eloquent ORM:构建嵌套的条件查询

I have 5 user titles, defined by booleans: CEO, executive, manager, employee & intern.

I'm building a user search API, and want to turn on/off eloquent queries, returning users with selected titles.

So if I were to search for managers and employees, the query should be

$users = User::where(function($query) 
->where([ADDITIONAL CONSTRAINTS... (like age)])->get();

The furthest I have came is:

$query = User::query();

//the respective titles are turned on by 1 and turned off by 0
if($CEO) {
$query = $query->orWhere('CEO');

if($executive) {
$query = $query->orWhere('executive');

//And so on for each title

In the end the additional where constraints get added like this:

$users = $query->where([Additional constraints])->get();

When searching for managers and employees, the final query would be:

$users = User::orWhere('manager')->orWhere('employee')

->where([ADDITIONAL CONSTRAINTS... (like age)])->get();

The result of this query is that the additional constraints are not always met, because there are orwhere queries before, which allow for unwanted instances to get selected.

I tried replacing the orWhere's with where's, but then users need to check positive for each selected title to get selected. So if I wanted to search for managers and employees, I might get none, because there isn't any user with both titles.

The goal:

  1. I want to add all these conditional 'title-queries' together.
  2. Put them all in one where(function($query) { $query->[all 'title-queries']; }).

Additional comments:

  1. I know that I could also eliminate every other model instead of searching for wanted models. If I would search for managers and employees, I could set where('CEO', '!=', 1) for each unwanted title. I don't want this, because Users with two titles, like employee and interim would get excluded in some cases.

  2. I know that I could write nested conditional queries for each scenario i.e. (manager & ceo, interim & ceo & executive and on ...), but that would take 25 queries and simply is not easily scalable (exponential more queries) if additional user titles are added.

  3. It has to be an Eloquent solution.

  4. Users can have multiple titles.

I have thought hard about this problem, thanks!

  • 写回答

1条回答 默认 最新

  • douya2982 2016-10-25 21:00

    Maybe you can do something like this ? (with use function keyword)

    $filters = ['manager', 'employee'];
    $users = User::where(function($query) use($filters) {
       foreach( $filters as $filter )
          $query = $query->orWhere($filter);
    ->where([ADDITIONAL CONSTRAINTS... (like age)])->get();
    本回答被题主选为最佳回答 , 对您是否有帮助呢?



    • ¥15 我不明白为什么c#微软的官方api浏览器为什么不支持函数说明的检索,有支持检索函数说明的工具吗?
    • ¥15 ORBSLAM2框架跑ICL-NUIM数据集
    • ¥15 在我想检测ros是否成功安装时输入roscore出现以下
    • ¥30 老板让我做一个公司的投屏,实时显示日期,时间,安全生产的持续天数,完全没头绪啊
    • ¥15 Google Chrome 所有页面崩溃,三种解决方案都没有解决,我崩溃了
    • ¥20 使用uni-app发起网络请求,获取重定向302返回的cookie
    • ¥20 手机外部浏览器拉起微信小程序支付 (相关搜索:微信小程序)
    • ¥20 怎样通过一个网址找到其他同样模版的网址
    • ¥30 XIAO esp32c3 读取FDC2214的数据
    • ¥15 在工控机(Ubuntu系统)上外接USB蓝牙硬件进行蓝牙通信