• Accueil
• Nos programmes immobiliers neufs à Bordeaux

    string(4792) "

    SELECT
		p.id_page,
		p.reference,
		a.id_annonce,
		a.latitude,
		a.longitude,
		lpo.id_type_objet_lie AS id_type_objet,
		CONCAT( '/' , pp.slug , '/' , p.slug ) AS url,
		a.nom AS programme,
		a.lien_video,
		a.lien_plan_3d,
		p.introduction,
		a.id_quartier,
		a.id_commune,
		a.pinel,
		a.pinel_plus,
		l.annee_livraison,
		o.titre AS titre_offre,

		IF( a.id_quartier IS NULL, c.nom_min , CONCAT(c.nom_min, ' : ' ,q.nom_min) ) AS lieu,
        IF( a.id_quartier IS NULL, 'extramuros','intramuros' ) AS intra_extra,
		c.code_postal,

        substring_index(
			GROUP_CONCAT( DISTINCT
				CONCAT( l.trimestre_livraison, if( l.trimestre_livraison > 1, 'ème' ,'er' ), ' trimestre ', l.annee_livraison )
				ORDER BY t.nombre_pieces ASC SEPARATOR ','
            ) , ',', 1
		) AS 'livraison',
  
		GROUP_CONCAT( DISTINCT CONCAT( t.nombre_pieces ) ORDER BY t.nombre_pieces ASC SEPARATOR ',' ) AS 'typologies',
		a.nombre_lots,

		IFNULL( o.id_offre, 0 ) AS offres,

		MIN( t.prix_min ) AS prix_min,
		MIN(a.prix_min_rp) AS prix_min_RP,
		MAX( t.prix_max ) AS prix_max,
		a.tva_reduite,
		(
			SELECT
				COUNT(t.id_typologie)
			FROM typologies t
			WHERE
				t.id_annonce = a.id_annonce
				AND t.id_type_bien = 1
		) maisons,

		(
			SELECT
			COUNT(t.id_typologie)
			FROM typologies t
			WHERE
			t.id_annonce = a.id_annonce
			AND t.id_type_bien = 3
		) appartements,

        (
			SELECT
			COUNT(t.id_typologie)
			FROM typologies t
			WHERE
			t.id_annonce = a.id_annonce
			AND t.id_type_bien = 5
		) residence_etudiantes,

        (
			SELECT
			COUNT(t.id_typologie)
			FROM typologies t
			WHERE
			t.id_annonce = a.id_annonce
			AND t.id_type_bien IN (4,6)
		) residence_seniors,
        
        a.lmnp_gere AS residence_LMNP,
        a.denormandie,
        a.lli,
		a.prix_maitrise,
  
		a.prix_maitrise,

		if( a.date_fin_lancement > CURRENT_TIMESTAMP, TRUE , FALSE ) AS nouveaute,

		IF(
			(
				l.annee_livraison = YEAR( CURRENT_DATE() ) AND l.trimestre_livraison = QUARTER( CURRENT_DATE() )
			) OR (
				l.annee_livraison = YEAR( DATE_ADD(CURRENT_DATE(), INTERVAL +3 MONTH) ) AND
				l.trimestre_livraison = QUARTER( DATE_ADD(CURRENT_DATE(), INTERVAL +3 MONTH))
			)
		,1,0) AS livraison_rapide,

      	IF(
      		(
      			l.annee_livraison < YEAR( CURRENT_DATE() )
  			) OR (
          		l.annee_livraison = YEAR( CURRENT_DATE() ) AND
          		l.trimestre_livraison < QUARTER( CURRENT_DATE())
          	)
        ,1,0) AS deja_livre,

		CONCAT( '/medias/' , d_fav.nom_fichier_encode) AS image,
		GROUP_CONCAT( DISTINCT CONCAT('"/medias/' , doc.nom_fichier_encode , '-400.jpg"') SEPARATOR ',' ) AS images,
		COUNT( DISTINCT doc.id_document ) AS nb_images,
        a.phrase_accroche

    FROM annonces a

    LEFT JOIN typologies t
    ON 	t.id_annonce = a.id_annonce

	RIGHT JOIN lots l
	ON 	t.id_lot_representatif = l.id_lot AND
		l.actif = 1

    LEFT JOIN liens_pages_objets lpo
    ON 	lpo.id_objet_lie = a.id_annonce AND
    	lpo.id_type_objet_lie = 33

    RIGHT JOIN pages p
    ON 	lpo.id_page = p.id_page
    	 AND p.id_domaine = 95

    LEFT JOIN pages pp
    ON 	p.id_page_parent = pp.id_page

    LEFT JOIN liens_documents_objets ldo_fav
    ON  ldo_fav.id_document_role = 7 AND
        ldo_fav.id_type_objet_lie = 33 AND
        ldo_fav.id_objet_lie = a.id_annonce

    LEFT JOIN documents d_fav
    ON d_fav.id_document = ldo_fav.id_document AND d_fav.actif = 1

    LEFT JOIN liens_documents_objets ldo
    ON  ldo.id_document_role IN (5,6,7) AND
        ldo.id_type_objet_lie = 33 AND
        ldo.id_objet_lie = a.id_annonce

    LEFT JOIN documents doc
    ON doc.id_document = ldo.id_document AND doc.actif = 1

    LEFT JOIN communes c
    ON c.id_commune = a.id_commune

    LEFT JOIN quartiers q
    ON q.id_quartier = a.id_quartier

    LEFT JOIN offres o
    ON  o.id_objet_lie = a.id_annonce AND
        o.actif = 1 AND
        o.valide = 1 AND
      	o.id_type_objet_lie = 33 AND
        o.date_debut < NOW() AND
        /*( o.date_fin IS NULL OR o.date_fin > NOW() )*/
        ( o.date_fin IS NULL OR DATE_ADD( o.date_fin, INTERVAL 1 DAY) > NOW() )
    
    LEFT JOIN annonces_details ad
        ON ad.id_annonce = a.id_annonce

    WHERE
        ad.listing_mode != 2 AND
		p.id_gabarit IN ( 142 , 168 ) AND
		p.id_page_statut = 4 AND
		p.actif = 1 AND
		a.date_archivage IS NULL AND
        prix_min > 0
         AND p.id_domaine = 95
        
        
        
        

    GROUP BY a.id_annonce

    HAVING
        1 = 1
        
        
        
        

    ORDER BY  prix_min ASC AND intra_extra = 'intramuros'

    /*LIMIT 50*/

"
    

Toutes les réponses
à vos Questions sur L'Immobilier Neuf

Votre guide du neuf Offert !

Recevoir le guide