mysql - Laravel:将结果与 UnionAll 和 where 条件结合起来

Question

假设我有 2 张 table

resume_profiles 表:

   user_id  current_location      city
   | 3 |     | Chennai  |    | Kolkatta   |
   | 4 |     | Mumbai   |    | Ahmaedabad |
   | 5 |     | Pune     |    | Kolkatta   |
   | 6 |     | Kolkatta |    | Pune       |

resume_locations 表:

   user_id     location
   | 2 |     | Chennai  |
   | 2 |     | Mumbai   |
   | 3 |     | Pune     |
   | 4 |     | Kolkatta |

我需要使用 Union ALL 将这些结果表位置合并到一个集合中，其中resume_profiles.user_id =resume_locations.user_id，所以我会得到如下所示的内容:

   City        Aggregate
| Chennai    |    | 1 |
| Mumbai     |    | 1 |
| Pune       |    | 2 |
| Kolkatta   |    | 3 |
| Ahmaedabad |    | 1 |

没有 where user_id 的工作查询:

$preferred_locations = ResumeLocation::selectRaw('location as city');

$current_locations = ResumeProfile::selectRaw('current_location as city');

$subquery = ResumeProfile::selectRaw('city')
            ->unionAll($current_locations)
            ->unionAll($preferred_locations);

$locations = DB::table(DB::raw("({$subquery->toSql()}) AS s"))
            ->selectRaw('s.city,count(*) as aggregate')
            ->groupBy('s.city')
            ->get();

** 使用 were 条件时在查询中出现异常:**

$preferred_locations = ResumeLocation::selectRaw('location as city')
            ->where('resume_locations.user_id','resume_profiles.user_id');

$current_locations = ResumeProfile::selectRaw('current_location as city');

$subquery = ResumeProfile::selectRaw('city')
            ->unionAll($current_locations)
            ->unionAll($preferred_locations);

$locations = DB::table(DB::raw("({$subquery->toSql()}) AS s"))
            ->selectRaw('s.city,count(*) as aggregate')
            ->groupBy('s.city')
            ->get();

我不知道如何在表中实现此 where 条件。

Answer 1

地点:

$preferred_locations = ResumeLocation::selectRaw('location as city')
->whereIn('user_id', function($q) {
  $q->select('user_id')->from('resume_profiles');
});