SELECT wp_posts.*, 1 rooms_available, 0 rooms_booked , IFNULL((SELECT price_meta2.meta_value + 0 FROM wp_postmeta price_meta2 WHERE price_meta2.post_id=wpml_translations_default.element_id AND price_meta2.meta_key='_accommodation_min_price:2024-12-18:2026-12-18' LIMIT 1), 0) accommodation_price , IFNULL((SELECT price_meta3.meta_value + 0 FROM wp_postmeta price_meta3 WHERE price_meta3.post_id=wpml_translations_default.element_id AND price_meta3.meta_key='accommodation_static_from_price' LIMIT 1), 0) accommodation_static_price
FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) JOIN wp_icl_translations wpml_translations
ON wp_posts.ID = wpml_translations.element_id
AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type) LEFT JOIN wp_icl_translations wpml_translations_default ON wpml_translations_default.trid = wpml_translations.trid AND (wpml_translations_default.source_language_code IS NULL OR wpml_translations.source_language_code IS NULL)
WHERE 1=1 AND (
( wp_postmeta.meta_key = 'accommodation_location_post_id' AND wp_postmeta.meta_value IN ('17983','20223','14064','5516','891','527') )
) AND wp_posts.post_type = 'accommodation' AND ((wp_posts.post_status = 'publish')) AND ( ( ( wpml_translations.language_code = 'en' OR 0 ) AND wp_posts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','location','room_type','accommodation','wpcf7_contact_form','ufaq','mc4wp-form' ) ) OR wp_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','location','room_type','accommodation','wpcf7_contact_form','ufaq','mc4wp-form' ) )
GROUP BY wp_posts.ID HAVING 1=1
ORDER BY wp_posts.post_title ASC
Nestled near Italy's coastal heel, this resort seamlessly blends Puglia's allure with modern luxury and impeccable service. crèche and kids club ensure parents enjoy spa, golf or gym time.
This family-friendly destination is nestled amidst stunning Alpine scenery and offers modern accommodations, exceptional dining, and a wealth of activities for all ages. Highlights include a rooftop infinity pool, a natural lake for swimming, and an expansive play and adventure area for kids.
Set against a backdrop of lush Mediterranean greenery and crystal-clear turquoise waters, the Falkensteiner Resort Capo Boi is a luxurious resort that offers an idyllic family escape for relaxation and adventure.
Discover tranquility near the Puglia coast at this creatively transformed historic property with stylish apartments. Explore the serene surroundings including picturesque towns.
Discover tranquility in southern Italy at this captivating pair of villas—one authentically restored Trullo and one contemporary Villa—sharing a pool, barbecue and lush outdoor spaces.
Nestled in enchanting Tuscan landscapes, this country estate promises idyllic family vacations. Kids embark on adventures, discovering hidden playhouses and splashing in the pool.
Indulge in the legacy of this family-run gem since 1908. Kids thrive with daily activities, supervised dinners and hikes. Enjoy the pool, the spa and a gym offering free daily courses.
The South Tyrolean Family Hideaway is a brand new retreat exclusively for families, located in the Dolomites. The resort offers comfortable accommodations and a variety of activities for all ages.
This 5-star haven blending family fun, gourmet delights and stunning nature. A hay barn, bouldering wall, trampoline, ski adventures in winter and a spa water park keep the smiles coming.
This retreat promises a haven where families create cherished memories. It encourages parents to savor moments with their kids: woodworking, fancy dressing and a petting zoo awaits.
Discover enchanting Umbrian bliss at these idyllic rustic country cottages with breathtaking views. The vast grounds include a fenced pool, playground, and alpacas for added delight.
The Italians are well known for their child-friendly attitude. They are also experts on living 'the good life', and are only too happy to share their relaxed worldview with visitors. Italy is a lovely destination all year, but the best times to visit are probably in spring and autumn: the temperatures are mild, there are fewer tourists around and prices are not too high.
