391,6 → 391,11 |
', IF(dvote.ce_protocole = '.$this->parametres['protocole'].', SUM(dvote.valeur), 0) as total_votes ' : |
'' |
). |
($this->doitJoindreTableTags() ? |
// attention le DISTINCT est indispensable ! |
', (COUNT(DISTINCT dtag.id_tag) + '.$this->assemblercomptageOccurencesMotsClesCel().') as total_tags ' : |
'' |
). |
'FROM '.$this->gestionBdd->formaterTable('del_obs_image', 'doi'). |
'INNER JOIN del_image di '. |
'ON doi.id_image = di.id_image '. |
398,6 → 403,11 |
'ON doi.id_observation = dob.id_observation '. |
'INNER JOIN del_utilisateur du '. |
'ON du.id_utilisateur = di.ce_utilisateur '. |
($this->doitJoindreTableTags() ? |
'LEFT JOIN del_image_tag dtag '. |
'ON doi.id_image = dtag.ce_image AND dtag.actif = 1 ' : |
'' |
). |
($this->doitJoindreTableVotes() ? |
'LEFT JOIN del_image_vote dvote '. |
'ON doi.id_image = dvote.ce_image AND dvote.ce_protocole = '.$this->parametres['protocole'] : |
406,18 → 416,40 |
$requeteLiaisons .= $this->chargerClauseWhere(); |
$requeteLiaisons .= $this->getTri(); |
$requeteLiaisons .= $this->gestionBdd->getLimitSql(); |
|
//echo $requeteLiaisons;exit; |
return $this->bdd->recupererTous($requeteLiaisons); |
} |
|
private function assemblercomptageOccurencesMotsClesCel() { |
$chaineMotsClesAffiches = $this->conteneur->getParametre('mots_cles_cel_affiches'); |
$tabMotsClesAffiches = explode(',',$chaineMotsClesAffiches); |
$chaineSql = ''; |
|
// Comptage du nombre de mots clés officiels présents dans la chaine mots clés texte |
foreach ($tabMotsClesAffiches as $motCle) { |
if($chaineSql != '') { |
$chaineSql .= ' + '; |
} |
$chaineSql .= 'IF(FIND_IN_SET('.$this->proteger($motCle).',di.mots_cles_texte) != 0, 1, 0)'; |
} |
|
return '('.$chaineSql.')'; |
} |
|
private function doitJoindreTableVotes() { |
return ($this->tri == 'votes'); |
} |
|
private function doitJoindreTableTags() { |
return ($this->tri == 'tags'); |
} |
|
private function getTri() { |
$order = ''; |
if($this->doitJoindreTableVotes()) { |
$order = ' GROUP BY dvote.ce_image, dob.id_observation ORDER BY total_votes '.$this->directionTri.', date_transmission desc '; |
} else if($this->doitJoindreTableTags()) { |
$order = ' GROUP BY doi.id_image ORDER BY total_tags '.$this->directionTri.', date_transmission desc '; |
} else { |
$order = ' ORDER BY '.$this->tri.' '.$this->directionTri.' '; |
} |