mitzuko

THIS IS THE PROBLEM:

SELECT posts.*, rules.*, (t_rate.price_from*(100-COALESCE(pmd.discount, 0))*(100+COALESCE(tm2.meta_value, 0)*COALESCE(tm3.meta_value, 0)))/(100*100) AS discount_price_from, pm.guests AS guests, ctt.slug AS category_slug, ctt.term_id AS category_id, CAST(tm2.meta_value AS UNSIGNED) AS categories_add_taxes, CAST(tm3.meta_value AS UNSIGNED) AS categories_tax, CAST(pmr.meta_value AS DECIMAL(3,2)) AS rating, items_number, pmd.discount, pmd.discount_date_from, pmd.discount_date_to, t_rate.rate_id, t_rate.rate_title, t_rate.date_from AS rate_date_from, t_rate.date_to AS rate_date_to, t_rate.apply_days, t_rate.start_days, t_rate.min_booking_period, t_rate.max_booking_period, t_rate.price_from, t_rate.price_general, t_rate.prices_conditional, t_rate.rate_order FROM vmc_posts posts #every our post assigned to terms from booking categories and other taxonomies INNER JOIN vmc_term_relationships tr ON posts.ID = tr.object_id #we need only our post type with categories INNER JOIN vmc_term_taxonomy ct ON ct.term_taxonomy_id = tr.term_taxonomy_id AND ct.taxonomy = ‘categories’ AND ct.term_taxonomy_id IN (72) #get category slug INNER JOIN vmc_terms ctt ON ctt.term_id = ct.term_id # get rates INNER JOIN vmc_babe_rates t_rate ON posts.ID = t_rate.booking_obj_id AND ( t_rate.date_from <= ‘2024-10-31 23:59:59’ OR t_rate.date_from IS NULL ) AND ( t_rate.date_to >= ‘2024-05-27 22:43:12’ OR t_rate.date_to IS NULL ) #get max guests INNER JOIN ( SELECT CAST(meta_value AS DECIMAL) AS guests, post_id AS pm_post_id FROM vmc_postmeta WHERE meta_key = ‘guests’ ) pm ON ( posts.ID = pm.pm_post_id AND pm.guests >= 1 ) #add categories_booking_rule meta LEFT JOIN vmc_termmeta tm ON tr.term_taxonomy_id = tm.term_id AND tm.meta_key = ‘categories_booking_rule’ # get rule LEFT JOIN vmc_babe_booking_rules rules ON rules.rule_id = tm.meta_value #get items number LEFT JOIN ( SELECT GREATEST(CAST(COALESCE(meta_value, 1) AS DECIMAL), 1) AS items_number, post_id AS pmt_post_id, meta_key AS pmt_meta_key FROM vmc_postmeta ) pmt ON ( posts.ID = pmt.pmt_post_id AND pmt.pmt_meta_key = CONCAT(‘items_number_’, ctt.slug) ) # get av cal INNER JOIN ( SELECT booking_obj_id AS obj_id, DATE_FORMAT(date_from, ‘%Y-%m-%d’) AS cal_date, MIN(date_from) AS av_date_from, 0 as group_results_by_date FROM vmc_babe_av_cal GROUP BY booking_obj_id ORDER BY date_from ASC ) av_cal ON av_cal.obj_id = posts.ID LEFT JOIN ( SELECT category_id AS category_id_to_exclude FROM vmc_babe_category_deactivate_schedule WHERE deactivate_date_from <= ‘2024-05-27 22:43:12’ AND deactivate_date_to >= ‘2024-10-31 23:59:59’ GROUP BY category_id ) cds ON cds.category_id_to_exclude = ctt.term_id #add categories_add_taxes meta LEFT JOIN vmc_termmeta tm2 ON tr.term_taxonomy_id = tm2.term_id AND tm2.meta_key = ‘categories_add_taxes’ #add categories_tax meta LEFT JOIN vmc_termmeta tm3 ON tr.term_taxonomy_id = tm3.term_id AND tm3.meta_key = ‘categories_tax’ #get rating LEFT JOIN vmc_postmeta pmr ON posts.ID = pmr.post_id AND pmr.meta_key = ‘_rating’ #get discount LEFT JOIN ( SELECT discount, date_from AS discount_date_from, date_to AS discount_date_to, booking_obj_id AS discount_obj_id FROM vmc_babe_discount WHERE date_from <= ‘2024-05-27 22:43:12’ AND date_to >= ‘2024-05-27 22:43:12’ ) pmd ON posts.ID = pmd.discount_obj_id WHERE posts.post_type = ‘to_book’ AND posts.post_status = ‘publish’ AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 260 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 297 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 302 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 303 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 304 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 305 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 261 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 262 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 263 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 264 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 265 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 268 ) AND EXISTS ( SELECT object_id FROM vmc_term_relationships WHERE object_id = posts.ID AND term_taxonomy_id = 295 ) AND NOT EXISTS ( SELECT booking_obj_id FROM vmc_babe_av_cal WHERE booking_obj_id = posts.ID AND in_schedule=1 AND date_from > ‘2024-05-27 22:43:12’ AND date_from <= ‘2024-10-31 23:59:59’ AND av_guests < 1 AND rules.basic_booking_period != ‘recurrent_custom’ AND rules.basic_booking_period != ‘single_custom’ AND rules.basic_booking_period != ‘night’ LIMIT 1 ) AND NOT EXISTS ( SELECT booking_obj_id FROM vmc_babe_av_cal WHERE booking_obj_id = posts.ID AND in_schedule=1 AND date_from >= ‘2024-05-27 22:43:12’ AND date_from < ‘2024-10-31 23:59:59’ AND av_guests < 1 AND rules.basic_booking_period = ‘night’ LIMIT 1 ) AND ( ( rules.basic_booking_period != ‘recurrent_custom’ AND rules.basic_booking_period != ‘single_custom’ ) OR ( ( rules.basic_booking_period = ‘recurrent_custom’ OR rules.basic_booking_period = ‘single_custom’ ) AND EXISTS ( SELECT booking_obj_id FROM vmc_babe_av_cal WHERE booking_obj_id = posts.ID AND in_schedule=1 AND date_from >= ‘2024-05-27 22:43:12’ AND date_from <= ‘2024-10-31 23:59:59’ AND av_guests > 1 LIMIT 1 ) ) ) AND cds.category_id_to_exclude IS NULL GROUP BY posts.ID ORDER BY discount_price_from DESC, t_rate.booking_obj_id ASC, t_rate.rate_order ASC, t_rate.price_from ASC, t_rate.date_from DESC, t_rate.date_to DESC LIMIT 0, 24