CREATE TEMPORARY TABLE IF NOT EXISTS Search_cb3882ebf201340c614320a407b07aba AS SELECT t1.*, meta_c1.post_id AS room_id, meta_c2.meta_value AS max_adults, meta_c3.meta_value AS max_kids, meta_c4.meta_value AS minimum_stay
FROM ( SELECT DISTINCT it2.element_id AS acc_id FROM (SELECT post_s1.ID AS acc_id FROM crv_2_posts AS post_s1
WHERE (post_s1.post_status = 'publish') AND (post_s1.post_type = 'accommodation')) AS t0
INNER JOIN crv_2_icl_translations it1 ON (it1.element_type = 'post_accommodation') AND it1.element_id = t0.acc_id
INNER JOIN crv_2_icl_translations it2 ON (it2.element_type = 'post_accommodation') AND it2.language_code='es' AND it2.trid = it1.trid ) AS t1
INNER JOIN crv_2_postmeta AS meta_c1 ON (meta_c1.meta_key = 'trav_room_accommodation') AND (t1.acc_id = meta_c1.meta_value)
INNER JOIN crv_2_postmeta AS meta_c2 ON (meta_c1.post_id = meta_c2.post_id) AND (meta_c2.meta_key='trav_room_max_adults')
LEFT JOIN crv_2_postmeta AS meta_c3 ON (meta_c1.post_id = meta_c3.post_id) AND (meta_c3.meta_key='trav_room_max_kids')
LEFT JOIN crv_2_postmeta AS meta_c4 ON (t1.acc_id = meta_c4.post_id) AND (meta_c4.meta_key='trav_accommodation_minimum_stay') WHERE (meta_c2.meta_value >= 1) AND (meta_c2.meta_value + IFNULL(meta_c3.meta_value,0) >= 1 + 0) GROUP BY acc_id
SELECT t1.*, post_l1.post_title as acc_title, meta_rating.meta_value as review, meta_price.meta_value as avg_price FROM Search_cb3882ebf201340c614320a407b07aba as t1
INNER JOIN crv_2_posts post_l1 ON (t1.acc_id = post_l1.ID) AND (post_l1.post_status = 'publish') AND (post_l1.post_type = 'accommodation')
LEFT JOIN crv_2_postmeta AS meta_rating ON (t1.acc_id = meta_rating.post_id) AND (meta_rating.meta_key = 'review')
LEFT JOIN crv_2_postmeta AS meta_price ON (t1.acc_id = meta_price.post_id) AND (meta_price.meta_key = 'trav_accommodation_avg_price') WHERE 1=1 GROUP BY acc_id ORDER BY cast(avg_price as unsigned) ASC LIMIT 30, 15;
