WebSVN – eFlore/Applications.del – Diff – /trunk/services/bibliotheque/Sql.php

 		'where' => array(),
 		'groupby' => array(),
 		'orderby' => array());
 	private $champsPrenom = array('du.prenom', 'prenom_utilisateur');
-	private $champsNom = array('du.nom',  'nom_utilisateur');
+	private $champsNom = array('du.nom', 'nom_utilisateur');
 	private $champsSousRequeteObs = array('masque.genre', 'masque.famille', 'masque.ns', 'masque.commune', 'masque.milieu');
 	public function __construct(Conteneur $conteneur) {
 		$this->conteneur = $conteneur;
 		return $this->requete;
+	}
 	private function addJoin($join) {
 		if (!isset($this->requete['join'][$join])) {
-			$this->requete['join'][] = $join;
+			$this->requete['join'][$join] = $join;
+		}
+	}
+	}
 	private function addWhere($idParam, $where) {
 		if (isset($this->parametres['_parametres_condition_or_'])
+				&& in_array($idParam, $this->parametres['_parametres_condition_or_'])) {
+			if ($this->etreAppliImg() && in_array($idParam, $this->champsSousRequeteObs)) {
+				$this->requete['where']['OR_SOUS_REQUETE'][] = $where;
-				&& in_array($idParam, $this->parametres['_parametres_condition_or_'])) {
+			} else {
+				$this->requete['where']['OR'][] = $where;
 			$this->requete['where']['OR'][] = $where;
 		} else {
 			$this->requete['where']['AND'][] = $where;
+		}
+	}
+	public function getWhere() {
+		// Sous-requete spéciale pour éviter de rechercher dans la table obs jointe à img depuis Pictoflora...
+		if (isset($this->requete['where']['OR_SOUS_REQUETE']) && count($this->requete['where']['OR_SOUS_REQUETE']) > 0) {
+			$clauseWhereSousRequete = implode(' OR ', $this->requete['where']['OR_SOUS_REQUETE']);
+			$sousRequete = 'di.ce_observation IN '.
+				"(SELECT DISTINCT id_observation FROM del_observation AS do WHERE $clauseWhereSousRequete ) ";
+			$this->requete['where']['OR'][] = "( $sousRequete )";
+			unset($this->requete['join']['LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ']);
+		}
 	public function getWhere() {
 		if (isset($this->requete['where']['OR']) && count($this->requete['where']['OR']) > 0) {
 			$this->requete['where']['AND'][] = '('.implode(' OR ', $this->requete['where']['OR']).')';
+		}
 			if (isset($this->parametres['masque.tag_cel']['AND'])) {
 				$tags = $this->parametres['masque.tag_cel']['AND'];
 				$clausesWhere = array();
 				foreach ($tags as $tag) {
 					$tagMotif = $this->bdd->proteger("%$tag%");
+					if ($this->etreAppliImg()) {
+						$sousRequete = 'SELECT DISTINCT id_observation '.
+							'FROM del_observation '.
+							"WHERE mots_cles_texte LIKE $tagMotif ";
-					$sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) LIKE %s";
+						$sql = " (di.mots_cles_texte LIKE $tagMotif OR di.id_image IN ($sousRequete) ) ";
+					} else {
+						// WARNING : la sous-requête est la meilleure solution trouvée pour contrer le fonctionnement
+						// étrange de l'optimiseur de MYSQL 5.6 (à retester avec Mysql 5.7 et suivant).
+						$sousRequete = 'SELECT DISTINCT ce_observation '.
+							'FROM del_image '.
+							"WHERE mots_cles_texte LIKE $tagMotif ".
+							'AND ce_observation IS NOT NULL';
+						$sql = " (do.mots_cles_texte LIKE $tagMotif OR do.id_observation IN ($sousRequete)) ";
+					}
-					$clausesWhere[] = sprintf($sqlTpl, $tagMotif);
+					$clausesWhere[] = $sql;
+				}
 				$whereTags = implode(' AND ', $clausesWhere);
 				$this->addWhere('masque.tag_cel', "($whereTags)");
 			} else if (isset($this->parametres['masque.tag_cel']['OR'])) {
 				$tags = $this->parametres['masque.tag_cel']['OR'];
-				$sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) REGEXP %s";
 				$tagMotif = $this->bdd->proteger(implode('|', $tags));
+				$sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) REGEXP %s";
 				$tagSql = sprintf($sqlTpl, $tagMotif);
 				$this->addWhere('masque.tag_cel', $tagSql);
+				if ($this->etreAppliObs()) {
+					$this->addJoin('LEFT JOIN del_image AS di ON (di.ce_observation = do.id_observation) ');
+				}
+			}
 			if ($this->etreAppliImg()) {
 				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
+			}
-			if ($this->etreAppliObs()) {
-				$this->addJoin('LEFT JOIN del_image AS di ON (do.id_observation = di.ce_observation) ');
-			}
+		}
+	}
 	/**
 	 * Plusieurs solutions sont disponibles dans les anciennes versions (voir DelTk et l'historique SVN de ce fichier).
 	 */
 	private function ajouterContrainteTagDel() {
-		if (isset($this->parametres['masque.tag_del'])) {
-			if (isset($this->parametres['masque.tag_del']['AND'])) {
+		if (isset($this->parametres['masque.tag_del'])) {
-				$tags = $this->parametres['masque.tag_del']['AND'];
-				// optimisation: en cas de "AND" on sort() l'input et le GROUP_CONCAT()
-				// donc nous utilisons des ".*" plutôt que de multiples conditions et "|"
-				sort($tags);
+			$tagsMotif = $this->construireTagsMotif();
-				$tagsMotif = $this->bdd->proteger(implode('.*', $tags));
+			if (is_null($tagsMotif) === false) {
-				$requete = 'SELECT ce_image '.
+				$sousRequete = 'SELECT ce_image '.
 					'FROM del_image_tag '.
 					'WHERE actif = 1 '.
 					'GROUP BY ce_image '.
-					"HAVING GROUP_CONCAT(tag_normalise ORDER BY tag_normalise) REGEXP $tagsMotif ".
-					' -- '.__FILE__.' : '.__LINE__;
-				$sql = $this->recupererSqlContrainteTag($requete);
-				$this->addWhere('masque.tag_del', $sql);
-			} else if (isset($this->parametres['masque.tag_del']['OR'])) {
-				$tags = $this->parametres['masque.tag_del']['OR'];
-				$tagsMotif = $this->bdd->proteger(implode('|', $tags));
-				$requete = 'SELECT ce_image '.
-					'FROM del_image_tag '.
-					'WHERE actif = 1 '.
-					'GROUP BY ce_image '.
-					"HAVING GROUP_CONCAT(tag_normalise) REGEXP $tagsMotif ".
-					' -- '.__FILE__.' : '.__LINE__;
+					"HAVING GROUP_CONCAT(DISTINCT tag_normalise ORDER BY tag_normalise) REGEXP $tagsMotif ";
 				$sql = $this->recupererSqlContrainteTag($requete);
-				$this->addWhere('masque.tag_del', $sql);
+				$this->addWhere('masque.tag_del', "di.id_image IN ($sousRequete)");
+			}
 		}
-	}
+	}
-	private function recupererSqlContrainteTag($requete) {
-		$resultats = $this->bdd->recupererTous($requete);
+	private function construireTagsMotif() {
-		$ids = array();
-		foreach ($resultats as $resultat) {
+		$tagsMotif = null;
+		if (isset($this->parametres['masque.tag_del']['AND'])) {
-			$ids[] = $resultat['ce_image'];
+			$tags = $this->parametres['masque.tag_del']['AND'];
-		}
+			// ATTENTION -> optimisation: en cas de "AND" on sort() l'input et le GROUP_CONCAT()
+			// donc nous utilisons des ".*" plutôt que de multiples conditions et "|"
-		if (!empty($ids)) {
+			sort($tags);
+			$tagsMotif = $this->bdd->proteger(implode('.*', $tags));
-			$clauseIn = implode(',', $ids);
+		} else if (isset($this->parametres['masque.tag_del']['OR'])) {
-		} else {
+			$tags = $this->parametres['masque.tag_del']['OR'];
-			$clauseIn = 'SELECT ce_image FROM del_image_tag WHERE false';
+			$tagsMotif = $this->bdd->proteger(implode('|', $tags));
+		}
-		return "di.id_image IN ($clauseIn)";
+		return $tagsMotif;
+	}

Subversion Repositories eFlore/Applications.del

(root)/trunk/services/bibliotheque/Sql.php – Rev 1889 → 1922