46,8 → 46,8 |
|
private $champsPrenom = array('du.prenom', 'prenom_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; |
$this->bdd = $this->conteneur->getBdd(); |
83,7 → 83,7 |
|
private function addJoin($join) { |
if (!isset($this->requete['join'][$join])) { |
$this->requete['join'][] = $join; |
$this->requete['join'][$join] = $join; |
} |
} |
|
98,12 → 98,26 |
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; |
} else { |
$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) ']); |
} |
|
if (isset($this->requete['where']['OR']) && count($this->requete['where']['OR']) > 0) { |
$this->requete['where']['AND'][] = '('.implode(' OR ', $this->requete['where']['OR']).')'; |
} |
450,26 → 464,41 |
$clausesWhere = array(); |
foreach ($tags as $tag) { |
$tagMotif = $this->bdd->proteger("%$tag%"); |
$sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) LIKE %s"; |
$clausesWhere[] = sprintf($sqlTpl, $tagMotif); |
if ($this->etreAppliImg()) { |
$sousRequete = 'SELECT DISTINCT id_observation '. |
'FROM del_observation '. |
"WHERE mots_cles_texte LIKE $tagMotif "; |
$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[] = $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']; |
$tagMotif = $this->bdd->proteger(implode('|', $tags)); |
$sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) REGEXP %s"; |
$tagMotif = $this->bdd->proteger(implode('|', $tags)); |
$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). |
476,52 → 505,34 |
*/ |
private function ajouterContrainteTagDel() { |
if (isset($this->parametres['masque.tag_del'])) { |
$tagsMotif = $this->construireTagsMotif(); |
if (is_null($tagsMotif) === false) { |
$sousRequete = 'SELECT ce_image '. |
'FROM del_image_tag '. |
'WHERE actif = 1 '. |
'GROUP BY ce_image '. |
"HAVING GROUP_CONCAT(DISTINCT tag_normalise ORDER BY tag_normalise) REGEXP $tagsMotif "; |
|
$this->addWhere('masque.tag_del', "di.id_image IN ($sousRequete)"); |
} |
} |
} |
|
private function construireTagsMotif() { |
$tagsMotif = null; |
if (isset($this->parametres['masque.tag_del']['AND'])) { |
$tags = $this->parametres['masque.tag_del']['AND']; |
// optimisation: en cas de "AND" on sort() l'input et le GROUP_CONCAT() |
// 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 "|" |
sort($tags); |
$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 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__; |
|
$sql = $this->recupererSqlContrainteTag($requete); |
$this->addWhere('masque.tag_del', $sql); |
} |
return $tagsMotif; |
} |
} |
|
private function recupererSqlContrainteTag($requete) { |
$resultats = $this->bdd->recupererTous($requete); |
$ids = array(); |
foreach ($resultats as $resultat) { |
$ids[] = $resultat['ce_image']; |
} |
|
if (!empty($ids)) { |
$clauseIn = implode(',', $ids); |
} else { |
$clauseIn = 'SELECT ce_image FROM del_image_tag WHERE false'; |
} |
return "di.id_image IN ($clauseIn)"; |
} |
|
/** |
* Partie spécifique à PictoFlora: |
* Attention : si le critère de tri n'est pas suffisant, les résultats affichés peuvent varier à chaque appel |
Property changes: |
Added: svnkit:entry:sha1-checksum |
+d671d5d650c4560e1a9b099d98db1e0c78f00002 |
\ No newline at end of property |