SELECT SQL_CALC_FOUND_ROWS wp_posts.*, (
SELECT SUM(rooms_available) rooms_available FROM (SELECT IFNULL(SUM(room_count), 0) rooms_available, availabilities.accommodation_id FROM wp_byt_accommodation_vacancies availabilities INNER JOIN (
select d.the_date from
(
select adddate('2025-07-02',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) the_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
having the_date between '2025-07-02' and '2027-07-02'
ORDER BY the_date
) d
where DATE(d.the_date) >= DATE(CURDATE())
group by d.the_date
order by d.the_date
) possible_dates ON (possible_dates.the_date >= DATE(availabilities.start_date) AND possible_dates.the_date < DATE(availabilities.end_date)) WHERE 1=1 AND ('2025-07-02' BETWEEN start_date AND end_date OR start_date <= '2027-07-02') AND end_date >= '2025-07-02' GROUP BY possible_dates.the_date, availabilities.accommodation_id ) as ra WHERE accommodation_id = wp_posts.ID ) rooms_available , (
SELECT IFNULL(SUM(rooms_booked), 0) rooms_booked FROM ((SELECT IFNULL(SUM(bookings.room_count), 0) rooms_booked, bookings.accommodation_id, possible_dates.the_date FROM wp_byt_accommodation_bookings bookings INNER JOIN (
select d.the_date from
(
select adddate('2025-07-02',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) the_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
having the_date between '2025-07-02' and '2027-07-02'
ORDER BY the_date
) d
where DATE(d.the_date) >= DATE(CURDATE())
group by d.the_date
order by d.the_date
) possible_dates ON (possible_dates.the_date >= DATE(bookings.date_from) AND possible_dates.the_date < DATE(bookings.date_to)) WHERE 1=1 GROUP BY possible_dates.the_date, accommodation_id ) as rb, (SELECT MIN(start_date) min_start_date, MAX(end_date) max_end_date, accommodation_id FROM wp_byt_accommodation_vacancies GROUP BY accommodation_id) min_max_available_dates ) WHERE rb.accommodation_id = wp_posts.ID AND min_max_available_dates.accommodation_id = wp_posts.ID AND rb.the_date >= min_max_available_dates.min_start_date AND rb.the_date < min_max_available_dates.max_end_date ) rooms_booked , IFNULL((SELECT price_meta2.meta_value + 0 FROM wp_postmeta price_meta2 WHERE price_meta2.post_id=wp_posts.ID AND price_meta2.meta_key='_accommodation_min_price:2025-07-02:2027-07-02' LIMIT 1), 0) accommodation_price , IFNULL((SELECT price_meta3.meta_value + 0 FROM wp_postmeta price_meta3 WHERE price_meta3.post_id=wp_posts.ID AND price_meta3.meta_key='accommodation_static_from_price' LIMIT 1), 0) accommodation_static_price
FROM wp_posts
WHERE 1=1 AND wp_posts.post_type = 'accommodation' AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID HAVING 1=1 AND rooms_available > rooms_booked AND rooms_available >= 1
ORDER BY accommodation_price ASC
LIMIT 0, 24
Your Luxurious Escape Near Marrakech A stunning 4-bedroom villa with a heated pool, private football field, gym, and breathtaking Atlas Mountain views, just 20 minutes from the city center.
A contemporary 5-bedroom villa near Marrakech, offering a heated pool, jacuzzi, and stunning mountain views. With a cook, concierge service, and luxurious amenities, it’s perfect for a relaxing getaway.
Située au cœur de la Palmeraie, Dar Marja est une villa traditionnelle offrant luxe et confort. Elle dispose de 5 chambres élégantes, chacune avec salle de bain privée et équipements modernes
Séjournez dans une villa d’exception alliant luxe et confort, avec services exclusifs dans une résidence sécurisée. Profitez d’une piscine, d’un cuisinier sur demande et d’un service de conciergerie.
Le Nid Secret is a luxurious 7-bedroom villa in Marrakech’s prestigious Amelkis Golf, offering a private spa, heated pool, and breathtaking Atlas Mountain views. Enjoy world-class comfort with personalized service in an exclusive retreat.
Château des Rêves is a luxurious villa in Marrakech's Palmeraie, offering 10 opulent bedrooms, private spa, heated pool, and exceptional service. A serene retreat combining Moroccan charm and modern elegance, just 15 minutes from the city center.
A Luxury Escape in MarrakechA stunning villa with a private heated pool, breathtaking Atlas Mountain views, and five-star service, blending Moroccan tradition with modern elegance.
A Luxurious Escape on Amelkis GolfEnjoy a refined stay in this stunning 5-bedroom villa with a heated pool, lush gardens, and dedicated staff, just minutes from Marrakech’s historic center.
Louez une villa de luxe à Marrakech avec piscine chauffée, chef privé et service 5 étoiles. Profitez d’un séjour exclusif alliant confort, sérénité et divertissement.
Séjournez à Villa Tica, une villa de luxe à Marrakech avec piscine, spa privé et vue imprenable sur l’Atlas. Profitez d’un service haut de gamme pour un séjour inoubliable.
Villa Chamou est une villa de luxe située dans la Palmeraie de Marrakech, offrant 6 chambres spacieuses, une piscine chauffée, un hammam et un jardin bio. Elle combine confort moderne, service personnalisé et démarches écologiques pour un séjour inoubliable
A Luxurious Oasis Near MarrakechExperience the ultimate Moroccan retreat at Terre Sauvage, a 6-hectare estate with 21 lavish suites, private pools, and breathtaking Atlas Mountain views, just 30 minutes from Marrakech.
Villa Émeraude Prestige, villa d’exception à Marrakech, allie luxe, art et sérénité au cœur de la Palmeraie. Profitez d’un séjour exclusif dans un cadre enchanteur
Séjournez à Villa Philonia, une villa de luxe à Marrakech avec piscine chauffée, jardin luxuriant et service haut de gamme. Profitez d’un cadre élégant pour des vacances inoubliables.
Experience luxury and tranquility in a villa in Marrakech (Annakhil), close to top attractions. Enjoy an outdoor pool, garden, free Wi-Fi, and concierge services for a perfect getaway.
Villa Rêve du Désert offers an unforgettable luxury escape in the heart of Marrakech’s Palmeraie, blending elegance, tranquility, and top-tier amenities.
Une villa contemporaine de luxe avec 4 suites, une piscine et une vue imprenable sur les montagnes de l'Atlas, à seulement 20 minutes de Marrakech. Idéale pour la détente, le golf et les séjours en famille.
Villa Sable Doré is a luxurious 5-bedroom retreat in Amelkis, featuring a private pool, jacuzzi, hammam, and stunning Atlas Mountain views. Enjoy premium services for an unforgettable stay.
Villa Le Sanctuaire is an eco-friendly luxury retreat near Marrakech, offering exclusive accommodations, modern amenities, and exceptional service amidst stunning natural beauty. Escape to tranquility and elegance in the Atlas Mountains.
Escape to Domaine Impérial, a luxurious private villa in Marrakech with exquisite suites, a saltwater pool, and world-class amenities. Experience ultimate relaxation and comfort in a serene, landscaped oasis just 20 minutes from the city center.
