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('2026-08-23',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 '2026-08-23' and '2028-08-23'
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 ('2026-08-23' BETWEEN start_date AND end_date OR start_date <= '2028-08-23') AND end_date >= '2026-08-23' 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('2026-08-23',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 '2026-08-23' and '2028-08-23'
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:2026-08-23:2028-08-23' 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
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.
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.
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.
Experience the elegance of Villa Andalousi, a stunning retreat on Amelkis Golf Course. Designed in Arabo-Andalusian style, this exclusive villa offers 4 luxurious suites, a heated pool, a private spa with a hammam, and breathtaking golf views.
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.
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.
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.
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.
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.
Experience ultimate luxury in the heart of the Palmeraie of Marrakech. This stunning 6-bedroom villa offers a private infinity pool, heated indoor pool, spa, cinema room, gym, and lush gardens.
Cette villa de 1000 m² offre une vue sur les montagnes et le jardin, avec une piscine privée, un sauna, et une cuisine entièrement équipée. Elle comprend une chambre king-size, une salle de bains avec douche à l’italienne, ainsi qu’une terrasse avec mobilier extérieur.
Experience ultimate luxury in Les Jardins de Fez, a stunning villa in Marrakech with a private pool, lush gardens, and premium services for an unforgettable stay.
Experience ultimate luxury at Villa Sultana, a stunning retreat near Majorelle Gardens, featuring a private pool, panoramic terrace, and personalized service.
Villa Eden Rose is a luxurious 10-bedroom villa in Marrakech, offering a heated pool, private spa, and personalized services. Perfect for a serene, exclusive retreat just minutes from the city's attractions.
Villa Océane is a luxurious 6-bedroom villa in Marrakech, offering stunning Atlas Mountain views, an infinity pool, and spacious terraces. Perfect for relaxation, with top-notch services and eco-friendly amenities.
Située dans un domaine de golf prestigieux, la Villa Emily offre 6 chambres spacieuses, une piscine chauffée, un jacuzzi, une salle de cinéma, et une cuisine gourmet.
Oasis des Dunes is a luxurious 8-bedroom villa located on the Amelkis Golf Course in Marrakech, offering world-class amenities including a spa, infinity pool, and dedicated staff. Perfect for a serene and unforgettable getaway.
Paradis Caché – Luxury Villa in Marrakech A breathtaking escape with 21 stylish rooms, a heated pool, a Jacuzzi, and stunning event spaces, perfect for weddings and private retreats.
Nichée dans un parc de 12 000 m², la Villa Rose-Anna allie tradition orientale et style moderne. Avec 6 chambres, une piscine chauffée, un tennis privé, un hammam et une salle de sport, elle offre un cadre idyllique pour des séjours en famille ou entre amis.
