521,22 → 521,16 |
|
$chaineIdImages = implode(',', $idsImages); |
$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 { |
$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) |
* ORDER BY FIELD(id_observation, 783590,922353,922351,922349,921316,921315,921313,921312,783388,921310,921309,921308) |
* */ |
$liaisons = $this->bdd->recupererTous($requeteImages); |
|
// Ce n'est pas la peine de continuer s'il n'y a pas eu de résultats dans la table del_obs_images |
582,12 → 576,17 |
|
// 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 count(doi.id_image) as nb FROM del_obs_image doi INNER JOIN del_image di ON di.id_image = doi.id_image'; |
$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 '. |
'ON di.id_image = doi.id_image '. |
'GROUP BY doi.id_image, doi.id_observation) t '; |
$resultatNbImages = $this->bdd->recupererTous($requeteNbImages); |
|
$total = (int) $resultatNbImages[0]['nb']; |
$this->navigation->setTotal($total); |
} |
|
|
return $liaisons; |
} |
|
648,7 → 647,7 |
' 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)) { |
678,12 → 677,10 |
$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(); |
|
|
$resultatsVotes = $this->bdd->recupererTous($requeteVotes); |
$tabVotes = array(); |
foreach ($resultatsVotes as $vote) { |
703,26 → 700,23 |
$chaineTri .= ' doi.id_image IN ('.$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 '. |
' FROM del_obs_image doi LEFT JOIN del_image_tag dit ON dit.ce_image = doi.id_image '; |
|
$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 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 "; |
} else { |
$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); |
$tabtags = array(); |
foreach ($resultatstags as $tag) { |
742,14 → 736,13 |
$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); |