Geekly Articles each Day
📜 ⬆️ ⬇️

PostgreSQL Power


Meeting with a complex non-trivial task of searching and processing data, sometimes you want to solve it head-on. And although you understand that a decision may be slow or not viable at all, and there is not enough knowledge and experience to really solve it, you don’t need to hurry. It is important to understand that the DBMS were specifically created for this, and you should not solve the tasks intended for them in other ways.

Task


Search for hotels with available rooms for specific dates by a group of people.

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 were not carried out on the side of the database, but on the side of the web application: a ton of records was selected from different tables, and the numbers were selected and calculated in cycles, filtered, sorted and output page by page. Very inefficient. And the application, by the way, is written in Ruby on Rails.
Do not do it this way.

Initial data


The original data schema (in the examples is artificially simplified to fit into the sqlfiddle constraints)

Places - destinations, resorts. Of the fields - only the name.

Districts - areas. Each direction can have several areas. Fields: name and direction id.

Properties - hotels, can be tied to the direction or to a specific area. Fields:
')

Property_arrival_rules - rules of entry to each hotel. Fields:


The absence of an entry in the table for a specific date means that entry on that date is not possible. Why is stored so? It's all about the types of rules of entry. More on these types in the solution below .

Rooms - hotel rooms, more precisely the types of rooms; for example, 2-room identical rooms may be several in one hotel. Fields: name and id of the hotel.

Room_availabilities — room availability for each night. Fields:


The absence of a record for any night means unavailability of the number.

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


Room_prices - prices for room policies for each night in hotel currency. Fields:


The absence of a record for any night means the inability to purchase a room that night.

Currency_rates - exchange rates. Fields:


Input parameters


The user in the search form can choose:


searching results


The result of the search should be a list of hotels in the direction, area. And for each hotel:


The list of hotels should be sorted: first go the hotels with the right number, then the hotels with the cheapest 3, then the hotels without available rooms. Additionally, you can sort by star hotel or cost for the period.

It should be borne in mind that the limited number of entries for a particular page (pagination) should come to the application from the database.

Is it possible Yes, in 2 (two!) Sql-requests (after a small modification of the data scheme)

Decision


Suppose a user searches by the following parameters:


Step 1. The nearest arrival date to the desired


In fact, it is necessary to find one entry rule for each hotel of the direction or area with the nearest date to the desired date of entry. And here we can assume that we are looking for the nearest date no further than N days from the desired, for example, 7 days. This is what this query looks like .

Request the nearest arrival 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 AND 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. Suitable departure date


We need to calculate the date of departure for each hotel based on the selected entry rules (from step 1) and the number of nights, calculated as the difference between the desired dates of departure and arrival.

And then the first problem opened, because rules of entry were very tricky. There are two types of rules:

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

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

And when in the required period type 2 rules fall, then in order to calculate the whole period, you should look through the following rule, which goes on the day the rule ends - the arrival date from the + N days rule.

A real example of type 2 rule. You can only enter the hotel on Saturdays for exactly one week. If I want to enter for a period of 1 to 6 days - I still have to take the whole week. If I want to take more than 7 days, for example, 9 days, then I will have to take either 14 days or limit myself for less than 7 days. And so on…

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

1. take the found entry rule and the estimated departure date (arrival date from the rule + the desired number of nights)
2. Check whether the departure date is within the minimum rule period: from the “arrival date” to the “arrival date + N days”
2.1. if inside, i.e. the rule period covers the desired dates - check to which end of the period is closer
2.1.1. if closer to the beginning and this is not the first rule to be viewed, then the departure date is the date of arrival from the rule
2.1.2. otherwise, the departure date is “arrival date + N days”
2.2. if outside, i.e. the period of the rule may not be enough - check what type of rule we look at
2.2.1. if type 1, then the estimated departure date will be the calculated departure date
2.2.2. if type 2, take the following rule on the date: “arrival date + N days”
2.2.2.1. if the following rule exists, then we recursively repeat item 2 already for this rule, taking into account that this is not the first rule to be viewed.
2.2.2.2. if the following rule does not exist, then the departure date will be “arrival date + N days”

And how to put this on sql?

On the application side, you can pre-calculate all possible check-in / check-out periods for each day by the entry rules and put them in a separate table with fields:

arrival_date
(arrival date)		wanted_departure_date
(desired departure date)		departure_date
(actual
calculated
date of departure)		property_id
(hotel id)

Or even more densely, in order to reduce the number of records, because Type 2 rules will often coincide with the arrival date and calculated departure date for some nearby days.
arrival_date
(arrival date)		wanted_departure_range
(desired departure period,
daterange type)		departure_date
(actual
calculated
date of departure)		property_id
(hotel id)

And let's call it property_arrival_periods - calculated entry periods.

In order to limit the number of entries in this table and make the calculation not infinite, you need to add a certain limit on the maximum booking period, for example, 30 days. With such a restriction on each hotel for one year, in the worst case, there will be ~ 11000 records, which looks quite good.

Thus, when adding / changing / deleting the entry rule, we have the background in the appendix:


And then when searching, we do not need to count anything, but only choose from this new table .

Request check-out dates
 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 AND 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)


Step 3. Available numbers


We take all available numbers , i.e. those that have entries for the calculated entry-exit period (from step 2) and are simultaneously available every night of the period.

Request available numbers
 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 AND 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) ) 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)


Step 4. Room rates and “Does the group fit?”


We take the room policies (from step 3), for which there are prices for each day of the calculated period, and we calculate the cost for the period and the average price per night, recalculating the amounts from the hotel currency to a certain base currency (in our case, EUR). In addition, you must take into account the special conditions of the policies “booking up to date” and “booking up to N days before entry”.

We will also need the sign “whether the whole group is placed in the number” for each received policy.
According to the task policy must contain the maximum allowable ages with the number.
For example, a room can have 3 adults + 2 children 5 years old.
In this room can fit groups:


But do not fit:


And this is a problem.

Not only that initially the maximum number of guests is represented by a hstore type field (to which the conditions will be problematic to write) in a strange way: Map, where the keys are the maximum age, and the values ​​are the number, and for adults the key is generally “adults”.

It is also not clear how to present such information at all in such a way that it is possible to check whether a group of people fits or not.

And let's imagine the maximum number of guests as an array of places (sorted in ascending order), where each place is the maximum age (18 for an adult). And then the capacity of the room “3 adults + 2 children 5 years old” will look like

[5, 5, 18, 18, 18]

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

[5, 9, 18, 18]

As a result, a capacity column (capacity) storing it in this form was added to the policy table (room_price_policies) .

But the question still remains. How to write a condition (or query) in sql: will it fit [5, 9, 18, 18] in [5, 5, 18, 18, 18]? It turns out we need for each 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 take into account that there is only one person for one place. Such recursive exclusion of guests and places in the room.

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

The procedure 'does the group fit in the number?'
 CREATE OR REPLACE FUNCTION is_room_fit_guests(guests INTEGER[], capacity INTEGER[]) RETURNS BOOLEAN AS $$ DECLARE guest int; seat int; 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 an example of use.

And now our query looks like this .

Request with calculation of cost 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 AND 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) ) 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 room_properties ON 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)


Step 5. Suitable hotels


We select hotels with data (from step 4) for the cheapest policy of a number with a positive value “whether the whole group is placed in a number”.

Request suitable 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 AND 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 room_properties ON 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) ) SELECT DISTINCT ON(property_id) *, 1 as all_guests_placed FROM properties_with_rooms WHERE fit_people = TRUE ORDER BY property_id, total


Step 6. Unsuitable hotels with rooms available


Such hotels, in which there is no room for the whole group of guests, as options for booking several rooms. Select hotels from step 4 with a negative value of “whether the whole group is placed in the room”, but not included in the result of step 5

Request unsuitable 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 AND 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 room_properties ON 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


Step 7. All hotels of the direction


Finally, we combine the results by sorting first suitable hotels (from step 5), then unsuitable hotels with available rooms (from step 6), then all other hotels, additionally sorting by the cost per period or star rating of the hotel if necessary, and also adding pagination ( 20 hotels per page)

Final hotel search request
 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 AND 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 room_properties ON 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 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)) ) ORDER BY all_guests_placed DESC NULLS LAST, room_available DESC, total ASC LIMIT 20 OFFSET 0


Step 8. 3 cheapest rooms


Before giving the result to the user, for unsuitable hotels with available rooms as a separate sql query, select the 3 cheapest rooms. The query is very similar to the search for hotels themselves.Unless unique numbers are selected and only on specific hotels (from step 6). Suppose that on the current page there are two such hotels, and their id is 1 and 4. The request will be like this .

3 cheap 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 AND 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) ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::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 room_properties ON 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


Result


Acceleration of the search operation is dozens of times and this is with a relatively small amount of data, and over time the difference will be felt more and more.

And of course a ton of useful experience gained during the decision.

Source: https://habr.com/ru/post/338406/

More articles:


All Articles