我在PostgreSQL上有以下函数(存储过程),用于计算小型精品酒店原型应用程序的可用性和定价:
-- Function that emulates Transact-SQL's IIF (if-and-only-if)
CREATE OR REPLACE FUNCTION IIF(BOOLEAN, DATE, DATE) RETURNS DATE
AS $$
SELECT CASE $1 WHEN True THEN $2 ELSE $3 END
$$
LANGUAGE SQL IMMUTABLE;
-- Function to have together all steps that lead to availability and pricing calculation
CREATE OR REPLACE FUNCTION availability(check_in DATE, check_out DATE, guests INTEGER, room INTEGER[] DEFAULT '{}')
RETURNS TABLE (
r_id INTEGER,
r_floor_no INTEGER,
r_room_no INTEGER,
r_name VARCHAR,
r_sgl_beds INTEGER,
r_dbl_beds INTEGER,
r_accommodates INTEGER,
r_code VARCHAR,
t_nights INTEGER,
t_price REAL
) AS $$
BEGIN
RETURN QUERY
(
WITH p AS (
-- Sum of nights and prices per season (0..N)
SELECT SUM(IIF($1 > t.date_to, t.date_to, $2) - IIF($1 > t.date_from, $1, t.date_from)) AS nights,
SUM((IIF($2 > t.date_to, t.date_to, $2) - IIF($1 > t.date_from, $1, t.date_from)) * (t.base_price + t.bed_price * $3)) AS price
FROM rate AS t
WHERE (t.date_from, t.date_to) OVERLAPS ($1, $2)
AND t.published = True
),
a AS (
-- Room availability
SELECT r.id AS r_id,
r.floor_no AS r_floor_no,
r.room_no AS r_room_no,
r.name AS r_name,
r.sgl_beds AS r_sgl_beds,
r.dbl_beds AS r_dbl_beds,
r.accommodates AS r_accommodates,
r.supplement AS r_supplement,
r.code AS r_code
FROM room AS r
WHERE r.id NOT IN (
SELECT b.id_room
FROM booking as b
WHERE (b.check_in, b.check_out) OVERLAPS ($1, $2)
AND b.cancelled IS NULL
)
AND r.accommodates >= $3
AND CASE WHEN $4 = '{}'::INTEGER[] THEN r.id > 0 ELSE r.id = ANY($4) END
)
SELECT a.r_id AS r_id,
a.r_floor_no AS r_floor_no,
a.r_room_no AS r_room_no,
a.r_name AS r_name,
a.r_sgl_beds AS r_sgl_beds,
a.r_dbl_beds AS r_dbl_beds,
a.r_accommodates AS r_accommodates,
a.r_code AS r_code,
p.nights::INTEGER AS t_nights,
(a.r_supplement * p.nights + p.price)::REAL AS t_price
FROM a, p
ORDER BY t_price ASC, r_accommodates ASC, r_sgl_beds ASC, r_dbl_beds ASC, r_floor_no ASC, r_room_no ASC
);
END
$$ LANGUAGE plpgsql;
我试图将这段代码迁移到SQLAlchemy中,但是我似乎无法处理在SQLAlchemy上以WITH p AS [..]
和{
p.check_in
(日期)、p.check_out
(日期)、p.guests
(int)和{
我得到的错误是:
AttributeError: 'CTE' object has no attribute 'check_in'
在这条线上:
(tuple_(p.check_in, p.check_out))
位于子查询块内:
# Room availability using a sub-select
subq = session.query(Booking.id_room.label('id')).\
filter(
tuple_(Booking.check_in, Booking.check_out).
op('OVERLAPS')
(tuple_(p.check_in, p.check_out))
).\
filter(Booking.cancelled.is_(None)).\
subquery('subq')
我有一种感觉,SQLAlchemy只需要对cte()
进行一次调用,但我无法从documentation online中找到它。我尝试过逐块构建大查询,然后将它们组合在一起,但没有成功。在
为了帮助上下文化,这里是room
表中的数据:
id | floor_no | room_no | name | sgl_beds | dbl_beds | supplement | code | deleted
----+----------+---------+----------------------------------------------------------+----------+----------+------------+--------+---------
1 | 1 | 1 | Normal bedroom with two single beds | 2 | 0 | 20 | pink |
2 | 1 | 2 | Large bedroom with two single and one double beds | 2 | 1 | 40 | black |
3 | 1 | 3 | Very large bedroom with three single and one double beds | 3 | 1 | 50 | white |
4 | 1 | 4 | Very large bedroom with four single beds | 4 | 0 | 40 | purple |
5 | 1 | 5 | Large bedroom with three single beds | 3 | 0 | 30 | blue |
6 | 1 | 6 | Normal bedroom with one double bed | 0 | 1 | 20 | brown |
accommodates
现在是Room
模型类中的一个混合属性,但它以前是表中的一个列(可以还原为它,通过触发器更新)。在
这是rate
表:
id | date_from | date_to | base_price | bed_price | published
----+------------+------------+------------+-----------+-----------
1 | 2017-03-01 | 2017-04-30 | 10 | 19 | t
2 | 2017-05-01 | 2017-06-30 | 20 | 29 | t
3 | 2017-07-01 | 2017-08-31 | 30 | 39 | t
4 | 2017-09-01 | 2017-10-31 | 20 | 29 | t
5 | 2018-03-01 | 2018-04-30 | 10 | 21 | t
6 | 2018-05-01 | 2018-06-30 | 20 | 31 | t
7 | 2018-07-01 | 2018-08-31 | 30 | 41 | t
8 | 2018-09-01 | 2018-10-31 | 20 | 31 | t
9 | 2019-03-01 | 2019-04-30 | 10 | 20 | t
10 | 2019-05-01 | 2019-06-30 | 20 | 30 | t
11 | 2019-07-01 | 2019-08-31 | 30 | 40 | t
12 | 2019-09-01 | 2019-10-31 | 20 | 30 | t
最后,这是booking
表的一个片段:
id | id_guest | id_room | reserved | guests | check_in | check_out | checked_in | checked_out | cancelled | base_price | taxes_percentage | taxes_value | total_price | locator | pin | status | meal_plan | additional_services | uuid | deleted
----+----------+---------+---------------------+--------+------------+------------+------------+-------------+-----------+------------+------------------+-------------+-------------+---------+------+-----------+-----------------+---------------------+--------------------------------------+---------
1 | 1 | 1 | 2016-12-25 17:00:04 | 2 | 2017-05-05 | 2017-05-09 | | | | 200 | 10 | 20 | 220 | AAAAA | 1234 | Confirmed | BedAndBreakfast | "PoolKit"=>"1" | 4df783c9-9375-47d6-8a9d-3309aa2c0a10 |
2 | 2 | 2 | 2016-12-26 09:03:54 | 3 | 2017-04-01 | 2017-04-11 | | | | 500 | 10 | 50 | 550 | AAAAB | 1234 | Confirmed | BedAndBreakfast | "PoolKit"=>"1" | 0428692a-267a-46e7-871f-a7a20c8e9406 |
3 | 3 | 3 | 2016-01-25 14:43:00 | 3 | 2017-06-02 | 2017-06-12 | | | | 500 | 10 | 50 | 550 | AAAAC | 1234 | Confirmed | BedAndBreakfast | "PoolKit"=>"1" | 12deeb14-1568-4b70-9247-5df2df433359 |
4 | 4 | 4 | 2016-01-25 14:43:00 | 3 | 2017-06-01 | 2017-06-10 | | | | 500 | 10 | 50 | 550 | AAAAD | 1234 | Confirmed | BedAndBreakfast | "PoolKit"=>"1" | b3453b07-5ec7-4c15-be72-998e451998c6 |
5 | 5 | 5 | 2016-01-25 14:43:00 | 3 | 2017-06-08 | 2017-06-18 | | | | 500 | 10 | 50 | 550 | AAAAE | 1234 | Confirmed | BedAndBreakfast | "PoolKit"=>"1" | 02a5c8f8-1d4c-45d6-9698-50bfa6d47b42 |
我使用的是SQLAlchemy和PostgreSQL的最新版本,因此没有任何限制。在
你在这里看到的一切并不一定都是有意义的,因为这只是一个原型来测试一系列技术组合的特性。在
提前谢谢。在
因此,在Ilja找出变量命名冲突后,我继续进行查询,这是最终的工作结果:
请注意,现在输入参数位于}变量中。在
check_in
、check_out
、guests
和{相关问题 更多 >
编程相关推荐