带有联合查询的下拉菜单 [英] Dropdown with union query
问题描述
我正在开发一个预订系统，在我的预订表格中，我有一个下拉菜单元素，用于返回(仍然)预订系统的可用开始时间段.
I'm developing a booking system and in my booking form I have a dropdown element which is returning (still) available start time slots for a booking system.
通过创建新的预订，我创建的查询可以正常工作，并且所有可用的开始时间段都可以正确返回.
By creating a new booking the query I have created is working fine and all available start time slots are returned correctly.
查询:
WHERE {thistable}.id
IN (
SELECT id +3
FROM (
SELECT p1.book_date, t.*, count(p1.book_date) AS nbre
FROM fab_booking_taken AS p1
CROSS JOIN fab_booking_slots AS t
WHERE NOT ((t.heuredepart_resa < p1.book_end AND
t.heurearrivee_resa > p1.book_start))
AND DATE(p1.book_date)=DATE('{fab_booking___book_bookingdate}')
GROUP BY t.id) AS x
WHERE nbre =
(
SELECT count(p2.book_date)
FROM fab_booking_taken AS p2
WHERE p2.book_date = x.book_date
)
) ORDER BY id ASC
请观看视频:预订创建g
我通过编辑现有预订使用同一查询遇到的问题是返回了可用的开始时隙，这是很好的:
The problem I have by using the same query by editing an existing booking the available start time slots are returned which is fine :
18:00 18:30 19:00 19:30
18:00 18:30 19:00 19:30
但不是客户已经选择的(并已保存在数据库中的)时间段，在我的示例中为14:00.
but not the already by the customer chosen (and in the database saved) time slot which is in my example 14:00.
请观看视频:使用相同查询编辑预订
下拉菜单应使用以下选项填充:
Dropdown should be populated with the following options :
14:00 18:00 18:30 19:00 19:30
14:00 18:00 18:30 19:00 19:30
我试图创建一个联合查询，以获取客户已选择的开始时隙和(仍然)可用的开始时隙.
I tried to create an union query to get the already by the customer chosen start time slot and the (still) available start time slots.
查询:
{thistable}.id
IN (
SELECT id + 3
FROM (
SELECT p1.book_date, t.*, count(p1.book_date) AS nbre
FROM fab_booking_taken AS p1
CROSS JOIN fab_booking_slots AS t
WHERE NOT ((t.heuredepart_resa < p1.book_end
AND t.heurearrivee_resa > p1.book_start))
AND p1.book_date = DATE_FORMAT('{fab_booking___book_bookingdate}', '%Y-%m-%d')
GROUP BY t.id
) as foobar2
UNION (
SELECT id + 3
FROM (
SELECT p1.book_date, t.*, count(p1.book_date) AS nbre
FROM fab_booking_taken AS p1
CROSS JOIN fab_booking_slots AS t
WHERE ( ( t.heuredepart_resa < p1.book_end
AND t.heurearrivee_resa > p1.book_start ) )
AND t.id = '{fab_booking___book_starttime}'
AND p1.book_date = DATE_FORMAT('{fab_booking___book_bookingdate}', '%Y-%m-%d')
GROUP BY t.id
) AS x
WHERE nbre = (
SELECT count(p2.book_date)
FROM fab_booking_taken AS p2
WHERE p2.book_date = x.book_date
)
)
)
已由客户选择的开始时隙已返回(14:00)，但其他可用的返回开始时隙不正确.
The already by the customer chosen start time slot is returned (14:00) but the other available returned start time slots are not correct.
请观看视频:使用联合查询编辑预订
我被困住了，我不知道如何解决这个问题，因此，在这里我会提供一些帮助.
I'm stuck and I have no clue how I could solve this issue, so I would appreciate some help here.
谢谢
相关数据库表
将有关预订的fab_booking预订到视频中
fab_booking with the booking concerned into the video
fab_booking_taken并已在25 11 2016上预订，id = 347
fab_booking_taken with the already existing bookings on 25 11 2016 id = 347
id 347是相关的预订
id 347 is the concerned booking
fab_booking_slots表，其中包含所有可能的时隙
fab_booking_slots table which contains all possible time slots
填充下拉列表元素的fab_heuredepart_resa表
fab_heuredepart_resa table which populate the dropdown element
推荐答案
我不得不承认尝试解开该查询以理解其背后的逻辑是一项艰巨的任务，但我认为以下查询应返回您所得到的结果需要.
I have to admit that it is daunting to try to untangle that query to understand the logic behind it but I think that the following query should return the results that you need.
{thistable}.id IN (
/*Finds booking slots where the booking slot does not overlap
with any of the existing bookings on that day,
or where the booking slot id is the same as the current slot.*/
SELECT t.id + 3
FROM fab_booking_slots AS t
WHERE t.id = '{fab_booking___book_starttime}'
OR NOT EXISTS (
Select 1
From fab_booking_taken AS p1
Where Date(p1.book_date) = Date('{fab_booking___book_bookingdate}')
And p1.book_end > t.heuredepart_resa
And p1.book_start < t.heurearrivee_resa
)
)
Order By id Asc;
我非常确定这在逻辑上是等效的，一旦以这种简化形式表示，就更容易看到如何获取它并返回额外的时隙.
I'm pretty sure that this is logically equivalent, and once expressed in a simplified form like this it's easier to see how you can get it to also return the additional time slot.
在为没有现有时间段的新预订填充时间段时，您应该使用一个单独的查询，在这种情况下，您只需删除单行t.id = '{fab_booking___book_starttime}' OR.
You should have a separate query to use when populating the time slots for a new booking that doesn't have an existing time slot, in which case you can just remove the single line t.id = '{fab_booking___book_starttime}' OR.
这篇关于带有联合查询的下拉菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
