具有来自两个表的条件的棘手 SQL 查询

Question

我正在开发一个Rails 5应用程序。

我有一个乘车模型和一个司机模型。 每个游乐设施都有一个scheduled_at和一个driver_id(belongs_to)场地。 创建新骑行时，driver_id为空。然后，司机可以接受还没有司机的行程，因此为了向司机显示他可以接受的所有行程，我在司机模型中创建：

def open_rides    
Ride.where('driver_id IS NULL')
end

棘手的部分是，我想从司机已经拥有相同scheduled_at日期时间或接下来 3 小时的open_rides中排除所有行程。

因此，换句话说，我想确保已经接受时间 X 的乘车司机看不到时间 X 和接下来 3 小时的任何行程。

我希望能够在我的open_rides方法中将其表述为 WHERE 的位置，但非常欢迎任何其他想法。

下面是一个示例：

我的驱动程序表中有一个驱动程序：

{
id: 1,
name: 'Thommy'
}

我的游乐设施表中有 3 个游乐设施：

{
id: 1,
scheduled_at: 2018-04-04 12:00:00,
driver_id: 1
},
{
id: 2,
scheduled_at: 2018-04-04 13:00:00,
driver_id: NULL
},
{
id: 3,
scheduled_at: 2018-04-04 20:00:00,
driver_id: NULL
}

驾驶员已分配给骑行#1。骑行 #2 还没有司机，但骑行 #2 的scheduled_at在司机已经接受的另一个骑行(在本例中为 Ride #1)的 3 小时内，因此不应包含在open_rides中)。 乘车#3没有司机，并且在司机已经接受的乘车后超过3小时，它应该包含在open_rides中。

Answer 1

您的open_rides方法应查询 NULL driver_id超出 3 小时范围的乘车，因此您必须将所有计划的驱动器日期(dateParam 列表)传递到方法中

。

select * from ride
where driver_id is null
and (scheduled_at < @dateParam and scheduled_at > @dateParam + INTERVAL 3 HOUR)

您必须为司机预订乘车日期列表中的每个日期参数添加一个 and 语句，如下所示：

select * from ride
where driver_id is null
and (scheduled_at < @dateParam and scheduled_at > @dateParam + INTERVAL 3 HOUR)
and (scheduled_at < @dateParam1 and scheduled_at > @dateParam1 + INTERVAL 3 HOUR)
and (scheduled_at < @dateParam2 and scheduled_at > @dateParam1 + INTERVAL 3 HOUR)

我对MySQL或Rails不太熟悉，但我想你可以这样做，虽然它不是最好的方法，但它将处理留给了数据库。 另一种选择是使用 null 加载所有游乐设施driver_id并在 open_rides 方法中进行过滤，这可能更容易但内存效率不高，因为会有大量的开放游乐设施。