From 1e077160fca3306a273ecae5a366fd756c32baee Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 30 Nov 2013 22:47:48 +0100 Subject: Optimisation recherche et pagination MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit * Optimisation recherche SQL avec utilisation de HAVING plutôt que WHERE * Simplification et amélioration des performances en supprimant de RSSPaginator qui n'aidait plus vraiment et nécessitait plus de code et des copies de données. * Correction d'un bug dans le titre de la page introduit récemment, et simplification --- app/App_FrontController.php | 1 - app/controllers/indexController.php | 33 +++++++++++++--------------- app/layout/layout.phtml | 5 ++--- app/layout/nav_menu.phtml | 4 ++-- app/models/Entry.php | 11 +++++++--- app/models/RSSPaginator.php | 37 -------------------------------- app/views/helpers/pagination.phtml | 4 ++-- app/views/helpers/view/normal_view.phtml | 8 +++---- app/views/helpers/view/reader_view.phtml | 7 +++--- app/views/helpers/view/rss_view.phtml | 3 +-- 10 files changed, 36 insertions(+), 77 deletions(-) delete mode 100644 app/models/RSSPaginator.php diff --git a/app/App_FrontController.php b/app/App_FrontController.php index d701e2944..176677781 100644 --- a/app/App_FrontController.php +++ b/app/App_FrontController.php @@ -33,7 +33,6 @@ class App_FrontController extends FrontController { include (APP_PATH . '/models/Category.php'); include (APP_PATH . '/models/Feed.php'); include (APP_PATH . '/models/Entry.php'); - include (APP_PATH . '/models/RSSPaginator.php'); include (APP_PATH . '/models/Log_Model.php'); } diff --git a/app/controllers/indexController.php b/app/controllers/indexController.php index cf0e5024a..b3ae40847 100755 --- a/app/controllers/indexController.php +++ b/app/controllers/indexController.php @@ -46,8 +46,6 @@ class indexController extends ActionController { 'params' => $params ); - $this->view->rss_title = View::title(); - if ($output === 'rss') { // no layout for RSS output $this->view->_useLayout (false); @@ -67,19 +65,6 @@ class indexController extends ActionController { $this->view->get_c = ''; $this->view->get_f = ''; - // mise à jour des titres - $this->view->nb_not_read = HelperCategory::CountUnreads($this->view->cat_aside, 1); - if ($this->view->nb_not_read > 0) { - View::appendTitle (' (' . $this->view->nb_not_read . ')'); - } - View::prependTitle (' - '); - - $this->view->rss_title = $this->view->currentName . ' - ' . $this->view->rss_title; - View::prependTitle ( - $this->view->currentName . - ($this->nb_not_read_cat > 0 ? ' (' . $this->nb_not_read_cat . ')' : '') - ); - $get = Request::param ('get', 'a'); $getType = $get[0]; $getId = substr ($get, 2); @@ -92,6 +77,18 @@ class indexController extends ActionController { return; } + $this->view->nb_not_read = HelperCategory::CountUnreads($this->view->cat_aside, 1); + + // mise à jour des titres + if ($this->view->nb_not_read > 0) { + View::appendTitle (' (' . $this->view->nb_not_read . ')'); + } + View::prependTitle ( + $this->view->currentName . + ($this->nb_not_read_cat > 0 ? ' (' . $this->nb_not_read_cat . ')' : '') . + ' - ' + ); + // On récupère les différents éléments de filtrage $this->view->state = $state = Request::param ('state', $this->view->conf->defaultView ()); $filter = Request::param ('search', ''); @@ -138,13 +135,13 @@ class indexController extends ActionController { } if (count($entries) <= $nb) { - $next = ''; + $this->view->nextId = ''; } else { //We have more elements for pagination $lastEntry = array_pop($entries); - $next = $lastEntry->id(); + $this->view->nextId = $lastEntry->id(); } - $this->view->entryPaginator = new RSSPaginator ($entries, $next); + $this->view->entries = $entries; } catch (EntriesGetterException $e) { Minz_Log::record ($e->getMessage (), Minz_Log::NOTICE); Error::error ( diff --git a/app/layout/layout.phtml b/app/layout/layout.phtml index 9b502275c..6fc5c1cf0 100644 --- a/app/layout/layout.phtml +++ b/app/layout/layout.phtml @@ -10,10 +10,9 @@ renderHelper ('javascript_vars'); ?> //]]> entryPaginator) ? $this->entryPaginator->next() : ''; - if (!empty($next)) { + if (!empty($this->nextId)) { $params = Request::params (); - $params['next'] = $next; + $params['next'] = $this->nextId; ?> diff --git a/app/layout/nav_menu.phtml b/app/layout/nav_menu.phtml index 289fe6542..4b4945108 100644 --- a/app/layout/nav_menu.phtml +++ b/app/layout/nav_menu.phtml @@ -17,8 +17,6 @@ $string_mark = Translate::t ('mark_cat_read'); } $nextGet = $get; - $p = $this->entryPaginator->peek(); - $idMax = $p === null ? '0' : $p->id(); if (($this->conf->onread_jump_next () === 'yes') && (strlen ($get) > 2)) { $anotherUnreadId = ''; $foundCurrent = false; @@ -54,6 +52,8 @@ break; } } + $p = isset($this->entries[0]) ? $this->entries[0] : null; + $idMax = $p === null ? '0' : $p->id(); $markReadUrl = _url ('entry', 'read', 'is_read', 1, 'get', $get, 'nextGet', $nextGet, 'idMax', $idMax); Session::_param ('markReadUrl', $markReadUrl); ?> diff --git a/app/models/Entry.php b/app/models/Entry.php index 915fbccc8..ae8facf68 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -504,14 +504,18 @@ class EntryDAO extends Model_pdo { if ($firstId > 0) { $where .= 'AND e.id ' . ($order === 'DESC' ? '<=' : '>=') . $firstId . ' '; } - $terms = explode(' ', trim($filter)); + $terms = array_unique(explode(' ', trim($filter))); sort($terms); //Put #tags first + $having = ''; foreach ($terms as $word) { if (!empty($word)) { if ($word[0] === '#' && isset($word[1])) { - $where .= 'AND tags LIKE "%' . $word . '%" '; + $having .= 'AND tags LIKE ? '; + $values[] = '%' . $word .'%'; } elseif (!empty($word)) { - $where .= 'AND (e.title LIKE "%' . $word . '%" OR UNCOMPRESS(e.content_bin) LIKE "%' . $word . '%") '; + $having .= 'AND (e.title LIKE ? OR content LIKE ?) '; + $values[] = '%' . $word .'%'; + $values[] = '%' . $word .'%'; } } } @@ -519,6 +523,7 @@ class EntryDAO extends Model_pdo { $sql = 'SELECT e.id, e.guid, e.title, e.author, UNCOMPRESS(e.content_bin) AS content, e.link, e.date, e.is_read, e.is_favorite, e.id_feed, e.tags ' . 'FROM `' . $this->prefix . 'entry` e ' . 'INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id WHERE ' . $where + . (empty($having) ? '' : 'HAVING' . substr($having, 3)) . 'ORDER BY e.id ' . $order; if ($limit > 0) { diff --git a/app/models/RSSPaginator.php b/app/models/RSSPaginator.php deleted file mode 100644 index 39146f1ba..000000000 --- a/app/models/RSSPaginator.php +++ /dev/null @@ -1,37 +0,0 @@ -items = $items; - $this->next = $next; - } - - public function isEmpty () { - return empty ($this->items); - } - - public function items () { - return $this->items; - } - - public function next () { - return $this->next; - } - - public function peek () { - return isset($this->items[0]) ? $this->items[0] : null; - } - - public function render ($view, $getteur) { - $view = APP_PATH . '/views/helpers/'.$view; - - if (file_exists ($view)) { - include ($view); - } - } -} diff --git a/app/views/helpers/pagination.phtml b/app/views/helpers/pagination.phtml index 0b0d2f5ba..408cfca1b 100755 --- a/app/views/helpers/pagination.phtml +++ b/app/views/helpers/pagination.phtml @@ -8,8 +8,8 @@