雄辩有问题 [英] Eloquent havingRaw problems

问题描述

我有2个SQL表：一个表示旅游，另一个表示这些旅行的预订。结构如下：

  tours 
（int）id 
（int）max_participants 
 （int）状态
 
预订
（int）id 
（fk）tour 
（fk）date 
（int）num_passengers 
  

预订表格有一个日期FK，但表格非常简单，并不重要，因为它没有给我任何麻烦。 / p>

现在我需要过滤我的旅行，只向客户展示仍然可以容纳他想要的乘客人数的旅游。所以，假设旅游的最大参与人数为50人，并且在特定日期的多次预订中已经有48名乘客，我必须拒绝3名乘客（48 + 3> = 50）的额外预订，但接受2名乘客的额外预订（作为48 + 2> = 50）。

我出来了这个SQL，它的工作原理很好（一些值是硬编码的可读性，但他们正在使用变量）：

  select * from`tours`其中`state` = 1 
并存在（select * from`bookings` ````````````````````````````````具有SUM（num_passengers）+2 <=`tours```````````````` 
  

当我尝试以雄辩的方式做到这一点：

 code> $ tours = Tour :: where（'state'，1）
  - > whereHas（'toursBookings'，function（$ query）use（$ request，$ date）{
 return $ query-> where（'date'，$ date-> id）
  - > havingRaw（'SUM（num_passengers）+'。$ request-> get（'fi lter_number_passengers'）。 '< =？'，[50]）; 
}）
  - > get（）; 
  

这个作品就像一个魅力，做所有这些应该做的，但是数字50是硬编码的！这是一个严重的问题，因为每个旅程都有不同数量的max_participants。

有人可以找出一种方法来解决这个问题，而不是硬编码吗？它应该是 tours 。 max_participants 如SQL中所示。

解决方案

如果我理解正确，你可以做这样的事情

  $ max_participants = [ 50]; 
 $ tours = Tour :: where（'state'，1）
  - > whereHas（'toursBookings'，function（$ query）use（$ request，$ date，$ max_participants）{
 return $ query-> where（'date'，$ date-> id）
  - > havingRaw（'SUM（num_passengers）+'。$ request-> get（'filter_number_passengers'）。 '< = tours.max_participants'）; 
}）
  - > get（）; 
  

I have 2 SQL tables: one that represent tours and the other that represents bookings for those tours. Structure is as follows:

tours
(int) id 
(int) max_participants
(int) state

bookings
(int) id
(fk)  tour
(fk)  date
(int) num_passengers

The bookings table has a date FK but that table is very simple and not important as it is not giving me any trouble.

Now I need to filter my tours and only show to the client the tours that can still accommodate his desired number of passengers. So, assuming a tour has a max_participants of 50 and there are already 48 num_passengers in several bookings for a certain date, I must refuse an additional booking of 3 passengers (as 48+3>=50) but accept an additional booking of 2 passengers (as 48+2>=50).

I came out with this SQL and it works great (some values are hardcoded for readability but they are working with variables):

select * from `tours` where `state` = 1
and exists (select * from `bookings` where `bookings`.`tour` = `tours`.`id` and `date` = 280 having SUM(num_passengers) + 2 <= `tours`.`max_participants`)

When I try to do it in eloquent I got this far:

$tours = Tour::where('state', 1)
->whereHas('toursBookings', function ($query) use ($request, $date) {
    return $query->where('date', $date->id)
        ->havingRaw('SUM(num_passengers) + ' . $request->get('filter_number_passengers') . ' <= ?', [50]);
})
->get();

The works like a charm and does all that it is supposed to do BUT the number 50 is hardcoded! This is a serious problem as each tour has a different number of max_participants.

Can anyone figure out a way to solve this and un-hardcode that 50? It should be tours.max_participants as in the SQL.

解决方案

If I understood correctly you can do something like this

$max_participants = [50];
$tours = Tour::where('state', 1)
->whereHas('toursBookings', function ($query) use ($request, $date,$max_participants) {
    return $query->where('date', $date->id)
        ->havingRaw('SUM(num_passengers) + ' . $request->get('filter_number_passengers') . ' <= tours.max_participants');
})
->get();

这篇关于雄辩有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！