Force PostgreSQL

Meeting with a challenging non-trivial task of finding and processing information, sometimes I want to solve it in the forehead. And although you understand that the solution may be slow or even non-viable, and knowledge and experience is not enough to solve it for real, no need to rush. It is important to understand that the database was specially created for this, and solve the problems intended for them, in other ways not worth it.

the

Task

Search hotels with available rooms on a specific date, a certain group of people.

the

the Project

When the project fell into our hands, the search has already been implemented. He worked slowly, very slowly. And all because the calculations and sampling was conducted on the database side, and on the side of a web application: I got a ton of records from different tables, and the cycles were selected and calculated numbers, filtered, sorted and displayed page by page. It is very inefficient. And app, by the way, is written in Ruby on Rails.
Don't do that.

the

Source data

Source Datasheet (in the examples artificially simplified to fit into the limitations sqlfiddle)

Places areas of the resorts. Of the fields — only the name.

Districts areas. Each direction can have multiple areas. Fields: name and id of areas.

Properties hotels can be bound to a direction or to a particular area. Fields:

the

name — name
dest_type — type polymorphic context with the direction or region ("Place" or "District")
dest_id is the id of the connection with the direction or area
stars — the star rating (0 to 5)
currency — currency code

Property_arrival_rules — rules of entry in each hotel. Fields:

the

arrival_date — date-in
property_id — id of the hotel
rule — rule type (0 or 1), depending on the type of is calculated according to different departure date, read more the decision below
min_stay — minimum number of nights for your stay

The lack of records in a table on a specific date means that the entry in this day impossible. Why is stored? It's all in the types of rules of entry. Read more about these types the decision below.

Rooms — hotel rooms, more room types, as for example, 2 bedroom of the same rooms may be several in the same hotel. Fields: name and id property.

Room_availabilities — the availability of rooms on each night. Fields:

the

room_id — id of room
date — date
initial_count — the number of rooms available
sales_count — the number of rooms booked

The lack of a record on any night means the unavailability of the room.

Room_price_policies policies offer. The same room may have different prices depending on the number of guests, type of food and other conditions. Fields:

the

room_id — id of room
max_guests — maximum number of guests
meal_type — feed type, a number from 0 to 8, where 0 — no meals, 1 Breakfast, 2 half Board etc.
has_special_requirements — special conditions, Boolean value
before_type — type special conditions (0 or 1), 0 — the policy is valid only if the booking occurs before a specific date, 1 policy applies if booking is made for N days before date of arrival
before_date — date for before_type 0
days_before_arrival — the number of days for before_type 1

Room_prices for politicians of rooms per night in the hotel's currency. Fields:

the

room_price_policy_id — id policy numbers
price_date — date
price

The lack of records for any night means the inability to acquire a room in the night.
Currency_rates — currency exchange rates. Fields:

the

sale_currency code of the sold currency
buy_currency code of the purchased currency
price — the course, the number of units sold currency divided by the rate will give the number of units of the currency being purchased.

the

Input parameters

The user in the search form can select:

the

Direction or district — something out of places or districts. And if this is the direction the search should look for not only hotels directions and hotels all parts
Desired arrival date
date of departure

the group of people, for example, 3 adults + 2 children (7 and 9 years) the

Optionally, filter by price per night, the quality of the hotel, type of food

the

search Results

The search result should be a list of hotels in the area of the district. And for each property:

the

Suitable dates of arrival and departure
Suitable for the capacity of the cheapest room OR 3 for the cheapest rooms if no rooms accommodating the whole group

the Cost for the period of arrival and departure in the base currency for each room, included in the result

The list of hotels should be sorted: first there are hotels with adequate room, then hotels with 3 the cheapest, then hotels with no available rooms. Additionally, you can sort by star hotel or a value for the period.

It should be noted that come in the application from the database has a limited number of records for a specific page (pagination).

Is this possible? Yes, in 2 (two!) sql query (after a small modification of the schema of the data)

the

Solution

Let's say user is looking for the following:

the

the direction of the “Val Thorens”, which includes two more district “Tignes Le Lac and Tignes Val claret”
preferred date of arrival: January 2, 2018
preferred date of departure: January 8, 2018 (respectively, the desired number of nights — 6)
part of a group of people: 3 adults + 2 children (7 and 9 years)
Today: August 17, 2017

the

Step 1. The closest date to the desired

In fact, you have to find one rule entry for each hotel, direction, or area with the closest date to desired date of entry. And here we can assume that searching for nearest date no further N days from the desired, for example, 7 days. Here is the query.

Request the nearest check-in date

SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2018-01-02'::date - 7 <= & arrival_date arrival_date < = '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff

Step 2. Appropriate check-out date

We need to calculate the date of departure at each hotel based on the selected rule entry (from step 1) and the number of nights calculated as the difference between the desired dates of check-in.

And he opened the first problem, since the rules of entry was very tricky. There are two types of rules:

Type 1. You can call in a particular day for any number of days, but not less than N days

Type 2. You can call on a certain day strictly on N days

And when in the desired period fall foul of type 2, then to calculate the entire period you want to view the next rule, going to the end date rule — date rule + N days.

A real example of rules of type 2. The hotel can only be accessed on Saturdays for exactly a week. If I want to enter for a period of from 1 to 6 days — I still have to take the whole week. If I want to take more than 7 days, e.g. 9 days, then I will have to take or 14 days, limit yourself to a period of less than 7 days. And so on...

And it turns out that the algorithm for calculating the date of departure as follows:

1. take the rule of entry and expected date of exit (date of rule + the desired number of nights)
2.1. if inside, i.e., the period of the rule covers the desired date — check which end of the period closer
2.1.1. if closer to the beginning and this is not the first viewed by a rule, the departure date is the date of check-in rules
2.1.2. otherwise, the departure date is “date + N days”
2.2. if outside, i.e. the period the rules may not be sufficient — check what type of rule we are looking
2.2.1. if you type 1, the intended date of departure and will be calculated by the date of departure
2.2.2. if type 2, take the following rule for the date: “date + N days”
2.2.2.1. if the next rule exists, recursively repeat p. 2 for this rule, given the fact that this is not the first viewed the rule
2.2.2.2. if the following rule does not exist, the departure date will be “date + N days”

And how to put in sql?

On the application side is to calculate in advance according to the rules of entry all possible times of arrival and departure for each day and put in a separate table with fields:

the

arrival_date
(date of arrival)

wanted_departure_date
(desired departure date)

departure_date
(the actual
calculated
check-out date)

property_id
(hotel id)

Or even more closely, in order to reduce the number of entries, because for rules of type 2 will often be the same date of arrival and intended date of departure for some of the adjacent
the

arrival_date
(date of arrival)

wanted_departure_range
(dates, departure,
type daterange)

departure_date
(the actual
calculated
check-out date)

property_id
(hotel id)

Let's call it property_arrival_periods — the calculated periods of entry.

In order to limit the number of records in this table and do the calculation on is not infinite, you need to add a limit on the maximum booking period, e.g. 30 days. With this limitation in each hotel for one year, in the worst case, will be ~11000 records that looks quite good.

Thus when you add / change / delete rules check, we have background in the application:

the

delete the calculated periods for the dates from “dates rules minus 30 days” to “date rules”
count periods on each day from the “date of the rule minus 30 days” to “date rules” for each booking period: 1 day, 2 days, 3 days, ... for 30 days

And then when searching we don't need anything to count, but only select from this new table.

Request dates of arrival and departure

WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2018-01-02'::date - 7 <= & arrival_date arrival_date < = '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
)

SELECT 
property_arrival_periods.arrival_date, 
property_arrival_periods.departure_date, 
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)

the

Step 3. Available rooms

Take all available rooms, i.e. those that have a record for a calculated period of travel (from step 2) and available at the same time every night period.

rooms

WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2018-01-02'::date - 7 <= & arrival_date arrival_date < = '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT 
property_arrival_periods.arrival_date, 
property_arrival_periods.departure_date, 
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date

)

SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)

the

Step 4. The price of the room and “fit the group?”

Take policy numbers (from step 3) for which there are prices for each day of the calculated period, and calculated cost for the period and the average price per night, counting the amount of currency of the hotel in a certain base currency (in our case EUR). In addition, it is necessary to consider special policy conditions “booking to date” and “reservation over the N days prior to entry”.

Also, we need the sign of “does the whole group in the room” for every policy received.
The policy objective should contain the maximum ages with the amount.
For example, a room can go up to 3 adults + 2 children up to 5 years.
In such a room able to fit a group:

the

3 adults
3 adults + child 4 years
2 adults + child 10 years of age (in place of the adult)

But will not fit:

the

4 adults
3 adults + child 7 years
2 adults + 2 children 9 years old

And that's the problem.

Moreover, initially the maximum number of people represented by the field type * PostgreSQL hstore (to which the conditions of the problem, you will write) to an odd view: Map where the key is the maximum age, and values — number, and for adults — key at all “adults”.

So also it is unclear how to provide this information so that you can test fit the group or not.

And let's imagine the maximum number of guests in the form of array (sorted in ascending order), each place the maximum age (18 to adult). And then occupancy “3 adults + 2 children up to 5 years” will look like

[5, 5, 18, 18, 18]

A group of people we represent as an array of their ages, and then the “2 adults + 2 children (5 and 9 years old)” will look like

[5, 9, 18, 18]

In the end, policy table (room_price_policies) was added to the column capacity (capacity) to store it in this form.

But still the question remains. How to write sql condition (or query): fit [5, 9, 18, 18] [5, 5, 18, 18, 18]? It turns out we have to each and every guest from the group to look for a place in the room and the age of the place must be greater than or equal to the age of the guest, and note that for one seat only one person. Sort of a recursive exception of the guests in the room.

And here we will help stored procedures. For our problem, the procedure is as follows.

Procedure 'placed the group in the room?'

CREATE OR REPLACE FUNCTION is_room_fit_guests(guests INTEGER[], capacity INTEGER[])
RETURNS BOOLEAN
AS
$$ 
DECLARE
int guest;
int seat;
seat_index int;
max_array_index CONSTANT int := 2147483647;
BEGIN
guest = guests[1];

IF guest IS NULL
THEN 
RETURN TRUE;
END IF;

seat_index := 1;
FOREACH seat IN ARRAY capacity
LOOP
IF guest <= seat
THEN
RETURN is_room_fit_guests(guests[2:max_array_index], capacity[1:seat_index-1] || capacity[seat_index+1:max_array_index]);
END IF;
seat_index := seat_index + 1;
END LOOP;

RETURN FALSE;
END;
$$ 
LANGUAGE plpgsql;

And example use.

And now our request looks like.

Request with price calculation and capacity

WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2018-01-02'::date - 7 <= & arrival_date arrival_date < = '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT 
property_arrival_periods.arrival_date, 
property_arrival_periods.departure_date, 
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date

)

SELECT
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
(
CASE
WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS total,
(
CASE
WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS average_night_price,
rooms.id AS room_id,
is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties ON room_properties room_properties.id = rooms.property_id
LEFT JOIN currency_rates 
ON currency_rates.sale_currency = room_properties.currency 
AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = price_date AND price_date < fit_arrival_dates.departure_date
AND (room_price_policies.has_special_requirements = FALSE
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
AND room_price_policies.days_before_arrival IS NOT NULL 
AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
)
AND room_price_policies.capacity IS NOT NULL
GROUP BY
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
rooms.id
room_properties.currency,
currency_rates.price,
room_price_policies.capacity
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)

the

Step 5. Perfect hotel

Choose hotels with the data (from step 4) one of the cheapest policy numbers with a positive value “does the whole group in the room.”

a Query matching hotels

WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2018-01-02'::date - 7 <= & arrival_date arrival_date < = '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT 
property_arrival_periods.arrival_date, 
property_arrival_periods.departure_date, 
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_rooms AS (
SELECT
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
(
CASE
WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS total,
(
CASE
WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS average_night_price,
rooms.id AS room_id,
is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties ON room_properties room_properties.id = rooms.property_id
LEFT JOIN currency_rates 
ON currency_rates.sale_currency = room_properties.currency 
AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = price_date AND price_date < fit_arrival_dates.departure_date
AND (room_price_policies.has_special_requirements = FALSE
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
AND room_price_policies.days_before_arrival IS NOT NULL 
AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
)
AND room_price_policies.capacity IS NOT NULL
GROUP BY
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,

room_price_policies.meal_type,
rooms.id
room_properties.currency,
currency_rates.price,
room_price_policies.capacity
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
)

SELECT DISTINCT ON(property_id) *, 
1 as all_guests_placed
FROM properties_with_rooms
WHERE fit_people = TRUE
ORDER BY property_id, total

the

Step 6. Unsuitable hotels with rooms available

Such hotels, in which there is no room under the entire group of guests, as options for booking multiple rooms. Choose hotels from step 4 with a negative value “does the whole group in the room”, but not included in the result of step 5

Request inappropriate hotels

WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2018-01-02'::date - 7 <= & arrival_date arrival_date < = '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT 
property_arrival_periods.arrival_date, 
property_arrival_periods.departure_date, 
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_rooms AS (
SELECT
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
(
CASE
WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS total,
(
CASE
WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS average_night_price,
rooms.id AS room_id,
is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties ON room_properties room_properties.id = rooms.property_id
LEFT JOIN currency_rates 
ON currency_rates.sale_currency = room_properties.currency 
AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = price_date AND price_date < fit_arrival_dates.departure_date
AND (room_price_policies.has_special_requirements = FALSE
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
AND room_price_policies.days_before_arrival IS NOT NULL 
AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
)
AND room_price_policies.capacity IS NOT NULL
GROUP BY
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
rooms.id
room_properties.currency,
currency_rates.price,
room_price_policies.capacity
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
),
properties_with_recommended_room AS (
SELECT DISTINCT ON(property_id) *, 
1 as all_guests_placed
FROM properties_with_rooms
WHERE fit_people = TRUE
ORDER BY property_id, total
)

SELECT DISTINCT ON(property_id) *, 
0 as all_guests_placed 
FROM properties_with_rooms
WHERE property_id NOT IN (SELECT property_id FROM properties_with_recommended_room)
ORDER BY property_id, total

the

Step 7. All hotels destinations

Finally, combine results sorting first the perfect hotel (from step 5), then the wrong hotels with available rooms (from step 6), then all other hotels, additional sorting cost for the period or the quality of the hotel if necessary, and adding pagination (20 hotels per page)

the resulting query search hotels

WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2018-01-02'::date - 7 <= & arrival_date arrival_date < = '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT 
property_arrival_periods.arrival_date, 

property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_rooms AS (
SELECT
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
(
CASE
WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS total,
(
CASE
WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS average_night_price,
rooms.id AS room_id,
is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties ON room_properties room_properties.id = rooms.property_id
LEFT JOIN currency_rates 
ON currency_rates.sale_currency = room_properties.currency 
AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = price_date AND price_date < fit_arrival_dates.departure_date
AND (room_price_policies.has_special_requirements = FALSE
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
AND room_price_policies.days_before_arrival IS NOT NULL 
AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
)
AND room_price_policies.capacity IS NOT NULL
GROUP BY
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
rooms.id
room_properties.currency,
currency_rates.price,
room_price_policies.capacity
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
),
properties_with_recommended_room AS (
SELECT DISTINCT ON(property_id) *, 
1 as all_guests_placed
FROM properties_with_rooms
WHERE fit_people = TRUE
ORDER BY property_id, total
),
properties_without_recommended_room AS (
SELECT DISTINCT ON(property_id) *, 
0 as all_guests_placed 
FROM properties_with_rooms
WHERE property_id NOT IN (SELECT property_id FROM properties_with_recommended_room)
ORDER BY property_id, total
),
properties_with_cheapest_room AS (
SELECT * FROM properties_with_recommended_room
UNION ALL
SELECT * FROM properties_without_recommended_room
)

SELECT properties.*,
(
CASE 
WHEN a room_id IS NOT NULL THEN 1
ELSE 0
END
) AS room_available,
properties_with_cheapest_room.arrival_date,
properties_with_cheapest_room.departure_date,
properties_with_cheapest_room.room_id,
properties_with_cheapest_room.room_price_policy_id,
properties_with_cheapest_room.total,
properties_with_cheapest_room.average_night_price,
properties_with_cheapest_room.all_guests_placed
FROM properties
LEFT JOIN properties_with_cheapest_room ON properties_with_cheapest_room.property_id = properties.id
WHERE
(
(properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
All_guests_placed ORDER BY DESC NULLS LAST, room_available DESC, total ASC
LIMIT 20 OFFSET 0

the

Step 8. 3 the cheapest rooms

Before sending the result to the user, to the wrong hotels with available rooms separate sql query to select the 3 most expensive rooms. The query is very similar to the search of hotels. Except that selected unique numbers and only on specific hotels (from step 6). Suppose that on the current page two of the hotel, and id — 1 and 4. Request will be.

3 rooms

WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2018-01-02'::date - 7 <= & arrival_date arrival_date < = '2018-01-02'::date + 7
AND property_id IN (1, 4)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT 
property_arrival_periods.arrival_date, 
property_arrival_periods.departure_date, 
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_available_rooms AS (
SELECT DISTINCT ON (rooms.id)
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
(
CASE
WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS total,
(
CASE
WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)

END
) AS average_night_price,
rooms.id AS room_id
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties ON room_properties room_properties.id = rooms.property_id
LEFT JOIN currency_rates 
ON currency_rates.sale_currency = room_properties.currency 
AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date < = price_date AND price_date < fit_arrival_dates.departure_date
AND (room_price_policies.has_special_requirements = FALSE
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
AND room_price_policies.days_before_arrival IS NOT NULL 
AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
)
GROUP BY
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
rooms.id
room_properties.currency,
currency_rates.price
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
)

SELECT 
distinct_available_rooms.property_id,
distinct_available_rooms.room_id,
distinct_available_rooms.room_price_policy_id,
distinct_available_rooms.total
FROM properties
JOIN LATERAL (
SELECT * FROM properties_with_available_rooms
WHERE properties.id = properties_with_available_rooms.property_id
ORDER BY total
LIMIT 3
) distinct_available_rooms ON distinct_available_rooms.property_id = properties.id 
WHERE properties.id IN (1, 4)
ORDER BY distinct_available_rooms.total

the

Result

Speed up your search for dozens of times and this despite the relatively small amount of data, and over time the difference will be felt more and more.

And of course a ton of useful experience obtained in the course of the solution.

Article based on information from habrahabr.ru

Поиск по этому блогу

computer express