laravel雄辩的3个查询合为一

3qpi33ja 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(352)

我试图实现的是一次性执行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 
]

我怎样才能做到呢？

mysql group-by php laravel laravel-5.6

来源：https://stackoverflow.com/questions/51055275/laravel-eloquent-3-queries-into-one

2条答案

按热度按时间

oyxsuwqo1#

出行模型关系

public function city(){
   return $this->belongsTo(City::class);
}

public function ratings(){
   return $this->hasMany(Rating::class, 'ratable_id'); //assuming ratable_id is an id of trips table
}

获取数据

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

打印数据

foreach($trips as $trip){
    $trip->city->name." - ". $trip->price." - ". $trip->ratings()->avg('rate');
}

赞(0）回复(0）举报 2021-06-24

yftpprvb2#

有两种方法，使用雄辩的方法，这是首选的方法，或者使用连接一个查询来获得您想要的结果
以雄辩的方式向前推进，我假设您已经基于关系类型（1:m，m:m）定义了模型及其Map

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

使用join向前移动

$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();

赞(0）回复(0）举报 2021-06-24

我来回答

laravel雄辩的3个查询合为一

2条答案

相关问题

热门标签

最新问答