SELECT annonces.id, annonces.id_categorie_web_new, annonces.date_publication, annonces.date_publication, annonces.titre, annonces.description, annonces.lat, annonces.lon, annonces.telephone, annonces.telephone2, annonces.hide_phone, communes_france.nom AS ville, annonces.signature_nominative, societes.nom as societe_nom, annonces.id_societe, annonces.etat, annonces.display_web,
				communes_france.nom_maj AS ville_maj, communes_france.cp, communes_france.ci AS insee, communes_france.id AS ville_id, clients.id AS client_id, 

				(SELECT COUNT(*) FROM annonces WHERE annonces.id_client = client_id AND annonces.etat = 'O' AND annonces.id_categorie_web_new NOT IN (68,69) AND date_publication <= NOW() AND annonces.display_web = 'O') AS nb_annonces_client, 

				clients.pseudo, clients.nom as client_nom, clients.prenom as client_prenom, clients.statut, clients.photo AS photoProfil, clients.etat_photo, `categories`.id AS idCat, `categories`.libelle, `categories`.slug, categories_parent.libelle AS parent_libelle, categories_parent.slug AS parent_slug,

				CONCAT('[', GROUP_CONCAT(DISTINCT IF(photos_annonce.id IS NOT NULL, JSON_OBJECT('id', photos_annonce.id, 'compteur', photos_annonce.compteur, 'photo', photos_annonce.photo, 'etat', photos_annonce.etat), null) ORDER BY photos_annonce.compteur ASC), ']') AS photos, 
				CONCAT('[', GROUP_CONCAT(DISTINCT IF(`criteres`.id IS NOT NULL, JSON_OBJECT(
				'id', `criteres`.id, 
				'valeur', IF(`annonces_criteres`.value != '', `annonces_criteres`.value, `criteres_categories_options`.labelOption), 
				'type', `criteres`.type, 
				'format_display', ccp_format.value, 
				'nom', COALESCE(ccp_nom.value, criteres.label),
				'unite', `criteres`.unit, 'ordre',
				 `criteres_categories`.ordre), null) ORDER BY `criteres_categories`.ordre), ']') AS criteres_data,  
        		CONCAT( '[', GROUP_CONCAT( DISTINCT IF( commandes_options_print.id IS NOT NULL, JSON_OBJECT('option_print_libelle', tarifs.libelle), NULL)),']') AS options_print

					FROM annonces
							LEFT JOIN communes_france 
								ON communes_france.id = annonces.ville 
							LEFT JOIN clients 
								ON clients.id = annonces.id_client 
							LEFT JOIN societes 
								ON societes.id = annonces.id_societe AND annonces.id_societe != 0
							LEFT JOIN photos_annonce 
								ON photos_annonce.annonce = annonces.id AND photos_annonce.etat = 'O' 
							JOIN `categories` 
								ON `categories`.id = annonces.id_categorie_web_new 
							LEFT JOIN `categories` as categories_parent
								ON categories_parent.id = categories.parent
							LEFT JOIN `annonces_criteres` 
								ON `annonces_criteres`.id_annonce = annonces.id 
							LEFT JOIN `criteres` 
								ON `criteres`.id = `annonces_criteres`.id_criteres 
							LEFT JOIN `criteres_categories` 
								ON `criteres_categories`.id_critere = `criteres`.id
								AND `criteres_categories`.id_categorie = `categories`.id
							LEFT JOIN `criteres_categories_options`
								ON  `criteres_categories_options`.id = `annonces_criteres`.id_criteres_categories_options

							LEFT JOIN criteres_categories_parametres ccp_nom 
								ON ccp_nom.id_critere = criteres.id 
								AND ccp_nom.type='label' 
								AND (ccp_nom.id_categorie = categories.id OR ccp_nom.id_categorie = 0)
							LEFT JOIN criteres_categories_parametres ccp_format 
								ON ccp_format.id_critere = criteres.id 
								AND ccp_format.type='format_display' 
								AND (ccp_format.id_categorie = categories.id OR ccp_format.id_categorie = 0)

							LEFT JOIN commandes_annonces 
								ON commandes_annonces.id_annonce = annonces.id
							LEFT JOIN commandes_options_print 
								ON commandes_options_print.id_commande_annonce = commandes_annonces.id 
							LEFT JOIN tarifs 
								ON tarifs.id = commandes_options_print.id_tarif
					WHERE annonces.etat = 'O' 
					AND annonces.date_publication <= NOW() 
					AND annonces.display_web = 'O'
					AND titre != '' 
					AND (criteres.etat = 'O' OR (criteres.etat IS NULL AND annonces.id_categorie_web_new = 48))
					AND annonces.id = :id
				GROUP BY annonces.id ---127092