From 8f54e55d99a6799dd0dc9811e5260e749d20d1dd Mon Sep 17 00:00:00 2001 From: x2000bedo Date: Wed, 4 Aug 2004 18:05:54 +0000 Subject: [PATCH] =?utf8?q?Grande=20am=E9lioration=20de=20la=20requ=EAte=20?= =?utf8?q?et=20meilleure=20lisibilit=E9=20du=20code=20Il=20n'y=20a=20plus?= =?utf8?q?=20qu'une=20seule=20requ=EAte=20qui=20fait=20toute=20la=20recher?= =?utf8?q?che=20et=20elle=20est=20optimis=E9e=20!?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Par contre, pour optimiser, j'utilise SQL_CALC_ROWS et je dois donc exécuter SELECT FOUND_ROWS() juste après ma requête. En mode avec trace on ce n'est pas possible avec la lib diogene (à cause du SELECT EXPLAIN) donc j'ai bidouillé xorg.pages pour gérer ça. A mon humble avis, il faudrait légèrement modifier la lib diogenes pour rendre le truc plus correct (genre créer une pile des requêtes de la page et exécuter les requêtes en pile puis faire l'EXPLAIN sur cette pile une fois le page->run() lancé). A voir, si on a du temps pour ça. Moi je n'en ai pas. Ce serait plus propre (surtout vu que diogenes est un package debian) mais ça marche très bien comme c'est maintenant. --- htdocs/search.php | 48 +++++++++++++----------------------------- include/search.classes.inc.php | 32 +++++++++++----------------- include/xorg.page.inc.php | 23 ++++++++++++++++++-- 3 files changed, 48 insertions(+), 55 deletions(-) diff --git a/htdocs/search.php b/htdocs/search.php index eedca67..60b0f0e 100644 --- a/htdocs/search.php +++ b/htdocs/search.php @@ -11,46 +11,28 @@ else $page->assign('public_directory',$public_directory); if (array_key_exists('rechercher', $_REQUEST)) { $page->assign('formulaire',0); - $nameField = new StringSField('name',array('nom','epouse')); - $firstnameField = new StringSField('firstname',array('prenom')); - $promoField = new PromoSField('promo','egal','promo'); + $nameField = new StringSField('name',array('u.nom','u.epouse','i.nom'),'i.nom'); + $firstnameField = new StringSField('firstname',array('u.prenom','i.prenom'),'i.prenom'); + $promoField = new PromoSField('promo','egal',array('u.promo','i.promo'),'i.promo'); $fields = new SFieldGroup(true,array($nameField,$firstnameField,$promoField)); - $nameField = new StringSField('name',array('i.nom')); - $firstnameField = new StringSField('firstname',array('i.prenom')); - $promoField = new PromoSField('promo','egal','i.promo'); - $fields2 = new SFieldGroup(true,array($nameField,$firstnameField,$promoField)); $offset = new NumericSField('offset'); - $sqli = '(SELECT matricule - FROM auth_user_md5 - WHERE '.$fields->get_where_statement().')'; - $result = mysql_query($sqli); - while ($row = mysql_fetch_row($result)) - list($matricules[]) = $row; - $sqln = '(SELECT matricule - FROM auth_user_md5 - WHERE matricule IN ('.implode(',',$matricules).')) - UNION - (SELECT i.matricule + $sql = 'SELECT SQL_CALC_FOUND_ROWS + u.nom!="" AS inscrit, + IF(u.nom!="",u.nom,i.nom) AS nom, + u.epouse, + IF(u.prenom!="",u.prenom,i.prenom) AS prenom, + IF(u.promo!="",u.promo,i.promo) AS promo, + i.deces!=0 AS decede, + u.username, + c.uid AS contact FROM identification AS i - WHERE i.matricule NOT IN ('.implode(',',$matricules).') - AND '.$fields2->get_where_statement().')'; - $result = mysql_query($sqln); - $page->assign('nb_resultats_total',mysql_num_rows($result)); - $sql = '(SELECT 1 AS inscrit,u.nom,u.epouse,u.prenom,u.promo,i.deces!=0 AS decede,u.username, - c.uid AS contact - FROM auth_user_md5 AS u - INNER JOIN identification AS i ON (i.matricule=u.matricule) + LEFT JOIN auth_user_md5 AS u ON (i.matricule=u.matricule) LEFT JOIN contacts AS c ON (c.uid = '.$_SESSION['uid'].' AND c.contact=u.user_id) - WHERE u.matricule IN ('.implode(',',$matricules).')) - UNION - (SELECT 0 AS inscrit,i.nom,"",i.prenom,i.promo,i.deces!=0 AS decede,"","" AS contact - FROM identification AS i - WHERE i.matricule NOT IN ('.implode(',',$matricules).') - AND '.$fields2->get_where_statement().') + WHERE '.$fields->get_where_statement().' ORDER BY '.implode(',',array_filter(array($fields->get_order_statement(), 'promo DESC,nom,prenom'))).' LIMIT '.$offset->value.','.$perpage; - $page->mysql_assign($sql, 'resultats', 'nb_resultats'); + $page->mysql_assign($sql, 'resultats', 'nb_resultats','nb_resultats_total'); $page->assign('url_args',$fields->get_url()); $page->assign('offset',$offset->value); $page->assign('perpage',$perpage); diff --git a/include/search.classes.inc.php b/include/search.classes.inc.php index 1b2da99..05165b2 100644 --- a/include/search.classes.inc.php +++ b/include/search.classes.inc.php @@ -2,11 +2,13 @@ class SField { var $fieldFormName; var $fieldDbName; + var $fieldResultName; var $value; - function SField($_fieldFormName,$_fieldDbName='') { + function SField($_fieldFormName,$_fieldDbName='',$_fieldResultName='') { $this->fieldFormName = $_fieldFormName; $this->fieldDbName = $_fieldDbName; + $this->fieldResultName = $_fieldResultName; $this->get_request(); } @@ -22,7 +24,9 @@ class SField { } function get_where_statement() { - return ($this->value!=''); + return ($this->value!='')? + '('.implode(' OR ',array_map(array($this,'get_single_where_statement'),$this->fieldDbName)).')' + :false; } function get_order_statement() { @@ -65,7 +69,7 @@ class StringSField extends SField { length($this->value)-length(ereg_replace('[a-z]'.$CARACTERES_ACCENTUES,'',strtolower($this->value))); } - function get_like($field) { + function get_single_where_statement($field) { //on rend les traits d'union et les espaces équivalents $regexp = preg_replace('/[ -]/','[ \-]',$this->value); //on remplace le pseudo language des * par une regexp @@ -73,19 +77,9 @@ class StringSField extends SField { return $field." RLIKE '^(.*[ -])?".replace_accent_regexp($regexp).".*'"; } - function get_where_statement() { - if (!parent::get_where_statement()) - return false; - return '('.implode(' OR ',array_map(array($this,'get_like'),$this->fieldDbName)).')'; - } - - function get_different($field) { - return $field.'!="'.$this->value.'"'; - } - function get_order_statement() { if ($this->value!='') - return implode(',',array_map(array($this,'get_different'),$this->fieldDbName)); + return $this->fieldResultName.'!="'.$this->value.'"'; else return false; } @@ -94,8 +88,8 @@ class StringSField extends SField { class PromoSField extends SField { var $compareField; - function PromoSField($_fieldFormName,$_compareFieldFormName,$_fieldDbName) { - parent::SField($_fieldFormName,$_fieldDbName); + function PromoSField($_fieldFormName,$_compareFieldFormName,$_fieldDbName,$_fieldResultName) { + parent::SField($_fieldFormName,$_fieldDbName,$_fieldResultName); $this->compareField = new SField($_compareFieldFormName); } @@ -109,10 +103,8 @@ class PromoSField extends SField { return ($this->compareField->value=='=' && $this->value!=''); } - function get_where_statement() { - if (!parent::get_where_statement()) - return false; - return $this->fieldDbName.$this->compareField->value.$this->value; + function get_single_where_statement($field) { + return $field.$this->compareField->value.$this->value; } function get_url() { diff --git a/include/xorg.page.inc.php b/include/xorg.page.inc.php index 3a10b75..017fce6 100644 --- a/include/xorg.page.inc.php +++ b/include/xorg.page.inc.php @@ -120,8 +120,15 @@ class XorgPage extends DiogenesCorePage { function doAuth() { } - function mysql_assign($sql_query,$var_name,$var_nb_name='') { + function mysql_assign($sql_query,$var_name,$var_nb_name='',$var_found_rows='') { global $globals; + //lorsqu'on désire obtenir found_rows il faut désactiver la trace du résultat + $switch_trace = false; + if(!empty($var_found_rows) && $globals->db->_trace==1) { + $switch_trace = true; + $globals->db->trace_off(); + } + $sql = $globals->db->query($sql_query); if(mysql_errno()) return(mysql_error($sql)); @@ -130,10 +137,22 @@ class XorgPage extends DiogenesCorePage { while($array[] = mysql_fetch_assoc($sql)); array_pop($array); mysql_free_result($sql); - $this->assign_by_ref($var_name,$array); if(!empty($var_nb_name)) $this->assign($var_nb_name, count($array)); + + if(!empty($var_found_rows)) { + $n_res = $globals->db->query('SELECT FOUND_ROWS()'); + $r = mysql_fetch_row($n_res); + $this->assign($var_found_rows, $r[0]); + mysql_free_result($n_res); + //si la trace était activée on affiche la trace sur la requête initiale + if ($switch_trace) { + $globals->db->trace_on(); + $sql = $globals->db->query($sql_query); + mysql_free_result($sql); + } + } return 0; } -- 2.1.4