WebSVN – eFlore/Applications.del – Diff – /trunk/services/modules/0.1/images/ListeImages.php

 				$idsImages[] = $observation['id_image'];
+			}
 			$chaineIdImages = implode(',', $idsImages);
+			$requeteImages .= ' WHERE doi.id_image IN ('.$chaineIdImages.') '.
-			$requeteImages .= ' WHERE doi.id_image IN ('.$chaineIdImages.') '.
+							  ' GROUP BY doi.id_image, doi.id_observation '.
 							  ' ORDER BY FIELD(doi.id_image,  '.$chaineIdImages.')'.
 							  ' LIMIT '.$this->navigation->getLimite(); // On limite sur le nombre car les obs peuvent avoir plusieurs images
 		} else {
-		} else {
+			$requeteImages .= ' GROUP BY doi.id_image, doi.id_observation ';
 			$requeteImages .= ' ORDER BY id_observation DESC';
-			$requeteImages .= $this->gestionBdd->getLimitSql();
-		}
-		/**
-		 * 88307
-		 * SELECT *, di.mots_cles_texte as mots_cles_texte_image  FROM del_obs_image doi
-		 * INNER JOIN del_image di ON doi.id_image = di.id_image
-		 * WHERE id_observation IN (783590,922353,922351,922349,921316,921315,921313,921312,783388,921310,921309,921308)
+			$requeteImages .= $this->gestionBdd->getLimitSql();
 		 * ORDER BY FIELD(id_observation,  783590,922353,922351,922349,921316,921315,921313,921312,783388,921310,921309,921308)
 		 * */
+			}
 			// On compte à part les images issues de la jointure de del_obs_image et del_image car la fonction
+			// SQL_CALC_FOUND_ROWS dans la fonction requete image fait passer le temps d'éxécution de 0.0011 à 15s !
+			$requeteNbImages = 'SELECT SUM(t.nb) as nb FROM (SELECT count(DISTINCT doi.id_image) as nb '.
+							   'FROM del_obs_image doi '.
+							   'INNER JOIN del_image di '.
-			// SQL_CALC_FOUND_ROWS dans la fonction requete image fait passer le temps d'éxécution de 0.0011 à 15s !
+							   'ON di.id_image = doi.id_image '.
+							   'GROUP BY doi.id_image, doi.id_observation) t ';
-			$requeteNbImages = 'SELECT count(doi.id_image) as nb FROM del_obs_image doi INNER JOIN del_image di ON di.id_image = doi.id_image';
+			$resultatNbImages = $this->bdd->recupererTous($requeteNbImages);
 			$resultatNbImages = $this->bdd->recupererTous($requeteNbImages);
 			$total = (int) $resultatNbImages[0]['nb'];
 			$this->navigation->setTotal($total);
+		}
 		return $liaisons;
 						 ' FROM del_obs_image doi '.
 						 ' INNER JOIN del_image di ON doi.id_image = di.id_image '.
 						 ' INNER JOIN del_observation dob ON dob.id_observation = doi.id_observation '.
 						 ' LEFT JOIN del_image_tag dit ON dit.ce_image = di.id_image '.
 						 ' LEFT JOIN del_utilisateur du ON du.id_utilisateur = di.ce_utilisateur ';
 		$conditionsImg = $this->getConditionsImages();
 		if (!empty($conditionsImg)) {
 			$where = ' WHERE ('.implode(' OR ', $conditionsImg).') ';
 					$tag = $this->parametres['masque.tag'];
 					$requeteVotes .= ' LEFT JOIN del_image_tag dit ON dit.ce_image = di.id_image ';
 					$requeteVotes .= " WHERE (dit.tag_normalise LIKE '$tag%' OR  di.mots_cles_texte LIKE '%$tag%') AND dit.actif = 1 ";
+				}
 				$requeteVotes .= ' GROUP BY doi.id_image, doi.id_observation '.
 								 ' ORDER by total_votes '.$this->directionTri .', id_observation '.$this->directionTri.' '.
-						$this->gestionBdd->getLimitSql();
 						$this->gestionBdd->getLimitSql();
 				$resultatsVotes = $this->bdd->recupererTous($requeteVotes);
 				$tabVotes = array();
 				foreach ($resultatsVotes as $vote) {
 				$chaineTri .= '  doi.id_image IN ('.$strVotes.') ';
-				$chaineOrdre = ' ORDER BY FIELD(doi.id_image, '.$strVotes.')  ';
-			}
+				$chaineOrdre = ' ORDER BY FIELD(doi.id_image, '.$strVotes.')  ';
+			}
 			if ($this->parametres['tri'] == 'tags') {
-				$requetetags = ' SELECT SQL_CALC_FOUND_ROWS distinct doi.id_image, COUNT(id_tag) as total_tags '.
+				$requetetags = ' SELECT SQL_CALC_FOUND_ROWS doi.id_image, COUNT(id_tag) as total_tags '.
-						' FROM del_obs_image doi LEFT JOIN del_image_tag dit ON dit.ce_image = doi.id_image ';
+						' FROM del_obs_image doi LEFT JOIN del_image_tag dit ON dit.ce_image = doi.id_image AND dit.actif = 1 ';
 				if (isset($this->parametres['masque.tag'])) {
 					// Et si on a cherché par tag ?
-					$requetetags .= ' LEFT JOIN del_image di ON dit.ce_image = di.id_image ';
-					$tag = $this->parametres['masque.tag'];
-					$requetetags .= " WHERE (dit.tag_normalise LIKE '$tag%' OR  di.mots_cles_texte LIKE '%$tag%') AND dit.actif = 1 ";
+					$requetetags .= ' LEFT JOIN del_image di ON dit.ce_image = di.id_image ';
-				} else {
+					$tag = $this->parametres['masque.tag'];
-					$requetetags .= ' WHERE dit.actif = 1 ';
+					$requetetags .= " WHERE (dit.tag_normalise LIKE '$tag%' OR  di.mots_cles_texte LIKE '%$tag%') ";
+				}
 				$requetetags .= ' GROUP BY id_image, id_observation '.
 						' ORDER by total_tags '.$this->directionTri.', id_observation '.$this->directionTri.
 						$this->gestionBdd->getLimitSql();
 				$resultatstags = $this->bdd->recupererTous($requetetags);
+				}
 				$chaineTri .= ' doi.id_image IN ('.$strtags.') ';
 				$chaineOrdre = ' ORDER BY FIELD(doi.id_image, '.$strtags.') ';
-			}
+			}
 			$requeteImages .= $chaineTri.' GROUP BY doi.id_image, doi.id_observation '.$chaineOrdre;
 		} else {
 			$requeteImages .= ' GROUP BY doi.id_image, doi.id_observation'; // des fois, on a plusieurs observations pour la même image ...
 			$requeteImages .= $this->gestionBdd->getLimitSql();
 		}
 		$retour  = $this->bdd->recupererTous($requeteImages);
 		$total = $this->getFoundRows();
 		$this->navigation->setTotal($total);

Subversion Repositories eFlore/Applications.del

(root)/trunk/services/modules/0.1/images/ListeImages.php @ 1329 – Rev 1327 → 1328