Below query takes a lot of time (37s). Can you solve this issue???
STHotel->get_unavailability_hotel()
::::::::::::::::
SELECT ID, hotel_id, count(hotel_id) as total_room
FROM (
SELECT ID, wp_postmeta.meta_value as adult_number, st_meta1.meta_value as children_number, st_meta3.meta_value as number_room, st_meta2.meta_value as hotel_id
FROM wp_posts JOIN wp_postmeta on wp_postmeta.post_id=wp_posts.ID and wp_postmeta.meta_key=’adult_number’ JOIN wp_postmeta as st_meta1 on st_meta1.post_id=wp_posts.ID and st_meta1.meta_key=’children_number’ JOIN wp_postmeta as st_meta3 on st_meta3.post_id=wp_posts.ID and st_meta3.meta_key=’number_room’ JOIN wp_postmeta as st_meta2 on st_meta2.post_id=wp_posts.ID and st_meta2.meta_key=’room_parent’ where 1=1
AND post_type=’hotel_room’
AND wp_posts.ID IN (
SELECT room_id from(
SELECT room_id, sum(room_num_search) AS total_booked, room.number_room
FROM wp_st_order_item_meta
INNER JOIN wp_hotel_room AS room
ON room.post_id = wp_st_order_item_meta.room_id
WHERE 1 = 1
AND ( ( check_in_timestamp <= 1536710400
AND check_out_timestamp >= 1536710400 )
OR ( check_in_timestamp >= 1536710400
AND check_in_timestamp <= 1536796800 ) )
AND st_booking_post_type = ‘st_hotel’
AND STATUS NOT IN ( ‘trash’, ‘canceled’, ‘wc-cancelled’ )
GROUP BY room_id Having room.number_room – total_booked < 1 ) as booked_table )
OR wp_posts.ID IN (
SELECT post_id
FROM wp_st_room_availability
WHERE 1 = 1
AND ( check_in >= 1536710400
AND check_out <= 1536796800
AND status = ‘unavailable’ ) )
OR wp_postmeta.meta_value < 1
OR st_meta3.meta_value < 1
GROUP BY wp_posts.ID ) as room_data
GROUP BY hotel_id
HAVING total_room >= (
SELECT count(post_id)
FROM wp_postmeta
WHERE meta_key = ‘room_parent’
AND meta_value = hotel_id )
Bryan 4 years, 6 months ago.
