CODEWITHSUNDEEP
sqlruby-on-railspostgresql
newBike
newBike

Reputation: 15012

Query consecutive days records effectively

Query consecutive days records effectively.

Let's say I have tables hotels, rooms, room_skus

Hotel HAS_MANY Room

Room HAS_MANY RoomSku (RoomSku has a date field)

inline

I can take the location and date_range as the params from the user.

eg. L.A. 2016-08-12~2016-08-18

The above params mean I need to fileter all the hotels in L.A. first. And then find the rooms which have available room_skus from 2016-08-12~2016-08-18

How could I write the query more effeciently.

The query in PostgreSQL looks like this 

      Room Load (0.7ms)  SELECT "rooms".* FROM "rooms" WHERE "rooms"."id" IN (492, 76, 1187)
      Hotel Load (0.6ms)  SELECT "hotels".* FROM "hotels" WHERE "hotels"."id" IN (13, 42,  357, 368, 378)
      RoomSku Load (3.6ms)  SELECT "room_skus".* FROM "room_skus" WHERE "room_skus"."room_id" IN (73, 74, 75, 2267)  ORDER BY date
       (0.2ms)  SELECT SUM("room_sku_stocks"."amount") FROM "room_sku_stocks" WHERE "room_sku_stocks"."room_sku_id" = $1  [["room_sku_id", 1047]]
      HotelImage Load (0.2ms)  SELECT  "hotel_images".* FROM "hotel_images" WHERE "hotel_images"."hotel_id" = $1  ORDER BY "hotel_images"."id" ASC LIMIT 1  [["hotel_id", 13]]
       (0.2ms)  SELECT SUM("room_sku_stocks"."amount") FROM "room_sku_stocks" WHERE "room_sku_stocks"."room_sku_id" = $1  [["room_sku_id", 1034]]
      CACHE (0.0ms)  SELECT  "hotel_images".* FROM "hotel_images" WHERE "hotel_images"."hotel_id" = $1  ORDER BY "hotel_images"."id" ASC LIMIT 1  [["hotel_id", 13]]
       (0.2ms)  SELECT SUM("room_sku_stocks"."amount") FROM "room_sku_stocks" WHERE "room_sku_stocks"."room_sku_id" = $1  [["room_sku_id", 1021]]
      .....
      CACHE (0.0ms)  SELECT  "hotel_images".* FROM "hotel_images" WHERE "hotel_images"."hotel_id" = $1  ORDER BY "hotel_images"."id" ASC LIMIT 1  [["hotel_id", 378]]
      Rendered api/v1/room_skus/search.json.jbuilder (468.4ms)

Here's my current query, its complexity looks so terrible.

      // suppose I get the hotel ids first, and try to filter available rooms from this function
      def get_available_rooms_in_a_row(start_date, end_date, hotel_ids, num_of_days_in_a_row)
        all_room_ids = RoomSku.get_room_ids(start_date, end_date, hotel_ids)
        available_rooms_ids = get_filtered_available_rooms_ids(all_room_ids, num_of_days_in_a_row)
        Room.includes(:hotel, :skus).where(id: available_rooms_ids)
      end

# fetch all room_sku_ids and check if the # of available room_sku_ids is above the num_of_days_in_a_row, # if so, then this room is qualified. (the filter is on Ruby level not SQL level)

          def get_filtered_available_rooms_ids(room_ids, num_of_days_in_a_row)
            room_frequence = {}
            room_ids.each do |i|
              if room_frequence.has_key? i
                room_frequence[i]+=1
              else
                room_frequence[i] = 1
              end
            end
            room_frequence.reject { |k, v| v < num_of_days_in_a_row }.keys
          end

And in the response JSON is also a headache question,

I will get Rooms from the above functions,

But the returned JSON is a aggregation of valia room_sku_ids and its Hotel and Room infomation.

Let's say. The return Rooms are #1 and #2

However, the Room #1 has room_sku_ids from 2016-01-01 ~ 2018-12-31

But what I need is the info of room_sku_ids from 2016-08-12~2016-08-18

Therefore you can see I wrote a isOutOfDataRange to filter those room_sku_ids which are out of date range.

        def isOutOfDataRange(room_sku_date)
          (room_sku_date< @checkin_date or room_sku_date > @checkout_date )
        end

        json.array!(@rooms) do |item|
          json.hotel item.hotel
          json.room_skus do
            json.array! item.skus do |sku|
              next if isOutOfDataRange(sku.date)
              json.merge! sku.attributes.merge({stock:sku.stock})
            end
          end
        end

Overall, I think these query is terrible and not-effective.

Any idea or direction to prove my code?

Sample output: Object of Array, each object is a aggregation of RoomSkuid and its Room, Hotel information

inline

SAMPLE JSON

  [  
     {  
        "id":73,
        "hotel_id":13,
        "name":"單人房",
        "guests":1,
        "created_at":"2016-08-10T17:03:40.302Z",
        "updated_at":"2016-08-10T17:03:40.302Z",
        "english_name":"Single Room",
        "hotel":{  
           "id":13,
           "name":"東京郎伍德飯店",
           "introduction":null,
           "city_id":1,
           "created_at":"2016-08-10T17:03:40.300Z",
           "updated_at":"2016-08-10T17:03:40.311Z",
           "checkin_time":null,
           "checkout_time":null,
           "region":"上野",
           "english_name":"Hotel Lungwood Tokyo",
        },
        "room_skus":[  
           {  
              "id":1047,
              "room_id":73,
              "price":4000,
              "date":"2016-08-17",
              "created_at":"2016-08-10T17:04:05.161Z",
              "updated_at":"2016-08-10T17:04:05.170Z",
              "saleable":true,
              "annotation":null,
              "state":"active",
              "cost":3000.0,
              "stock":6
           }
        ],
        "img_src_url":"/img/hotel.jpg"
     },
     .....
     {  
        "id":2267,
        "hotel_id":378,
        "name":"三人房",
        "guests":3,
        "created_at":"2016-08-10T17:03:45.364Z",
        "updated_at":"2016-08-10T17:03:45.364Z",
        "english_name":"Triple Room",
        "hotel":{  
           "id":378,
           "name":"名古屋駅前名鐵飯店",
           "introduction":null,
           "city_id":3,
           "created_at":"2016-08-10T17:03:45.357Z",
           "updated_at":"2016-08-10T17:03:45.367Z",
           "checkin_time":null,
           "checkout_time":null,
           "region":"名古屋",
           "english_name":"Meitetsu Inn Nagoya Ekimae",
        },
        "room_skus":[  
           {  
              "id":30690,
              "room_id":2267,
              "price":3000,
              "date":"2016-08-17",
              "saleable":true,
              "annotation":null,
              "state":"active",
              "cost":2500.0,
              "stock":26
           }
        ],
        "img_src_url":"/img/hotel.jpg"
     }
  ]

Response of Matt solution

I got ERROR: operator is not unique: unknown - unknown

LINE 16: ... t.RoomDateCount = DATE_PART('date', '2016-08-18' - '2016-08... ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts.

SELECT *
FROM
    (
    SELECT
       *
       ,COUNT(*) OVER (PARTITION BY r.Id) as RoomDateCount
    FROM
       hotels h
       INNER JOIN rooms r
       ON h.Id = r.hotel_id
       INNER JOIN room_skus s
       ON r.id = s.room_id
       AND s.date BETWEEN '2016-08-12' AND '2016-08-18'
    ) t
WHERE
    t.RoomDateCount = DATE_PART('date', '2016-08-18' - '2016-08-12');

inline

Upvotes: 1

Views: 209

Answers (1)

Matt
Matt

Reputation: 14361

SELECT *
FROM
    (
    SELECT
       *
       ,COUNT(*) OVER (PARTITION BY r.Id) as RoomDateCount
    FROM
       Hotel h
       INNER JOIN Room r
       ON h.Id = r.hotel_id
       INNER JOIN RommSku s
       ON r.id = s.room_id
       AND s.date BETWEEN '2016-08-12' AND '2016-08-18'
       AND s.saleable = 1
    ) t
WHERE
    t.RoomDateCount = DATE_PART('day', '2016-08-18' - '2016-08-12')

I think this will give you want you want, you might have to play with date filter or count adding 1 day depending on your exact desired results. All columns in hotels, room, and RoomSku would be available and I suggest specifically hard coding the ones you want for performance.

This does assume only 1 RoomSku per room per Date exists in your RoomSku table if multiple can exist then it would need slight tweak.

Upvotes: 1

Related Questions