php - Laravel Eloquent 将 3 个查询合二为一

Question

我想要实现的是一次性执行 3 个查询，以限制 n1+ 问题:

假设我们有 3 个模型:

trips
  id => int
  price => float
  city_id => uint
........

cities
  id => int
  name => varchar
........

ratings:
  id => int
  ratable_id => int
  rate => small-int
......

伪代码:

select from tours where price >= 100
-then from the result 
select from cities where id in result.city_id as cities
select count from ratings where ratable_id in result.id as rates groupBy rate

所以结果是

[
  trips => list of the trips where price more than or equal 100
  cities=> list of the cities those trips belongs to
  rates => list of rating with it's count so like [1 => 5, 2 => 100] assuming that '1 and 2' are the actual rating , and '5,100' is the trips count 
]

我该如何实现这一目标？

Answer 1

有两种方法，使用 Eloquent 方法(这是首选方法)或使用连接单个查询来获得所需的结果

以 Eloquent 方式前进，我假设您已经根据模型的关系类型(1:m，m:m)定义了模型及其映射

$trips= Trips::with('city')
            ->withCount('ratings')
            ->where('price', '>=', 100)
            ->get();

通过加入继续前进

$trips = DB::table('trips as t')
            ->select('t.id', 't.price','c.name',DB::raw('count(*) as rating_count'))
            ->join('cities as c' ,'t.city_id', '=' , 'c.id')
            ->join('ratings as r' ,'t.ratable_id', '=' , 'r.id')
            ->where('t.price', '>=', 100)
            ->groupBy('t.id', 't.price','c.name')
            ->get();