On simule la pagination pour optimiser les requêtes en BDD (évite de tout charger puis d'en jeter les 3/4)

4 files changed, 77 insertions, 32 deletions

diff --git a/app/configuration/routes.php b/app/configuration/routes.php
index e0937731c..fa8c29b3f 100644
--- a/app/configuration/routes.php
+++ b/app/configuration/routes.php
@@ -3,7 +3,7 @@
 return array (
 	// Index
 	array (
-		'route'      => '/\?q=([\w\d\-_]+)&p=([\d+])',
+		'route'      => '/\?q=([\w\d\-_]+)&p=([\d]+)',
 		'controller' => 'index',
 		'action'     => 'index',
 		'params'     => array ('get', 'page')
diff --git a/app/controllers/indexController.php b/app/controllers/indexController.php
index 4002a0032..b06b6c711 100755
--- a/app/controllers/indexController.php
+++ b/app/controllers/indexController.php
@@ -10,6 +10,11 @@ class indexController extends ActionController {
 		$entryDAO = new EntryDAO ();
 		$catDAO = new CategoryDAO ();
 		
+		// pour optimiser
+		$page = Request::param ('page', 1);
+		$entryDAO->_nbItemsPerPage ($this->view->conf->postsPerPage ());
+		$entryDAO->_currentPage ($page);
+		
 		$default_view = $this->view->conf->defaultView ();
 		$mode = Session::param ('mode');
 		if ($mode == false) {
@@ -51,18 +56,7 @@ class indexController extends ActionController {
 			$entries = $entryDAO->listEntries ($mode, $order);
 		}
 		
-		// Gestion pagination
-		try {
-			$page = Request::param ('page', 1);
-			$this->view->entryPaginator = new Paginator ($entries);
-			$this->view->entryPaginator->_nbItemsPerPage ($this->view->conf->postsPerPage ());
-			$this->view->entryPaginator->_currentPage ($page);
-		} catch (CurrentPagePaginationException $e) {
-			Error::error (
-				404,
-				array ('error' => array ('La page que vous cherchez n\'existe pas'))
-			);
-		}
+		$this->view->entryPaginator = $entryDAO->getPaginator ($entries);
 		
 		$this->view->cat_aside = $catDAO->listCategories ();
 		$this->view->nb_favorites = $entryDAO->countFavorites ();
diff --git a/app/models/Entry.php b/app/models/Entry.php
index fb7399fec..d434e99f6 100755
--- a/app/models/Entry.php
+++ b/app/models/Entry.php
@@ -214,7 +214,18 @@ class EntryDAO extends Model_pdo {
 			$order = '';
 		}
 		
-		$sql = 'SELECT * FROM entry' . $where . ' ORDER BY date' . $order;
+		$sql = 'SELECT COUNT(*) AS count FROM entry' . $where;
+		$stm = $this->bd->prepare ($sql);
+		$stm->execute ();
+		$res = $stm->fetchAll (PDO::FETCH_ASSOC);
+		$this->nbItems = $res[0]['count'];
+		
+		$deb = ($this->currentPage - 1) * $this->nbItemsPerPage;
+		$fin = $this->nbItemsPerPage;
+		
+		$sql = 'SELECT * FROM entry' . $where
+		     . ' ORDER BY date' . $order
+		     . ' LIMIT ' . $deb . ', ' . $fin;
 		$stm = $this->bd->prepare ($sql);
 		$stm->execute ();
 
@@ -233,7 +244,18 @@ class EntryDAO extends Model_pdo {
 			$order = '';
 		}
 		
-		$sql = 'SELECT * FROM entry' . $where . ' ORDER BY date' . $order;
+		$sql = 'SELECT COUNT(*) AS count FROM entry' . $where;
+		$stm = $this->bd->prepare ($sql);
+		$stm->execute ();
+		$res = $stm->fetchAll (PDO::FETCH_ASSOC);
+		$this->nbItems = $res[0]['count'];
+		
+		$deb = ($this->currentPage - 1) * $this->nbItemsPerPage;
+		$fin = $this->nbItemsPerPage;
+		
+		$sql = 'SELECT * FROM entry' . $where
+		     . ' ORDER BY date' . $order
+		     . ' LIMIT ' . $deb . ', ' . $fin;
 		$stm = $this->bd->prepare ($sql);
 		
 		$stm->execute ();
@@ -253,7 +275,18 @@ class EntryDAO extends Model_pdo {
 			$order = '';
 		}
 		
-		$sql = 'SELECT * FROM entry e INNER JOIN feed f ON e.id_feed = f.id' . $where . ' ORDER BY date' . $order;
+		$sql = 'SELECT COUNT(*) AS count FROM entry e INNER JOIN feed f ON e.id_feed = f.id' . $where;
+		$stm = $this->bd->prepare ($sql);
+		$values = array ($cat);
+		$stm->execute ($values);
+		$res = $stm->fetchAll (PDO::FETCH_ASSOC);
+		$this->nbItems = $res[0]['count'];
+		
+		$deb = ($this->currentPage - 1) * $this->nbItemsPerPage;
+		$fin = $this->nbItemsPerPage;
+		$sql = 'SELECT * FROM entry e INNER JOIN feed f ON e.id_feed = f.id' . $where
+		     . ' ORDER BY date' . $order
+		     . ' LIMIT ' . $deb . ', ' . $fin;
 		
 		$stm = $this->bd->prepare ($sql);
 		
@@ -290,31 +323,48 @@ class EntryDAO extends Model_pdo {
 		
 		return $res[0]['count'];
 	}
+	
+	// gestion de la pagination directement via le DAO
+	private $nbItemsPerPage = 1;
+	private $currentPage = 1;
+	private $nbItems = 0;
+	public function _nbItemsPerPage ($value) {
+		$this->nbItemsPerPage = $value;
+	}
+	public function _currentPage ($value) {
+		$this->currentPage = $value;
+	}
+	
+	public function getPaginator ($entries) {
+		$paginator = new Paginator ($entries);
+		$paginator->_nbItems ($this->nbItems);
+		$paginator->_nbItemsPerPage ($this->nbItemsPerPage);
+		$paginator->_currentPage ($this->currentPage);
+		
+		return $paginator;
+	}
 }
 
 class HelperEntry {
 	public static function daoToEntry ($listDAO, $mode = 'all', $favorite = false) {
 		$list = array ();
-
+		
 		if (!is_array ($listDAO)) {
 			$listDAO = array ($listDAO);
 		}
 
 		foreach ($listDAO as $key => $dao) {
-			if (($mode != 'not_read' || !$dao['is_read'])
-			 && ($favorite == false || $dao['is_favorite'])) {
-				$list[$key] = new Entry (
-					$dao['id_feed'],
-					$dao['guid'],
-					$dao['title'],
-					$dao['author'],
-					unserialize (gzinflate (base64_decode ($dao['content']))),
-					$dao['link'],
-					$dao['date'],
-					$dao['is_read'],
-					$dao['is_favorite']
-				);
-			}
+			$list[$key] = new Entry (
+				$dao['id_feed'],
+				$dao['guid'],
+				$dao['title'],
+				$dao['author'],
+				unserialize (gzinflate (base64_decode ($dao['content']))),
+				$dao['link'],
+				$dao['date'],
+				$dao['is_read'],
+				$dao['is_favorite']
+			);
 		}
 
 		return $list;
diff --git a/app/views/index/index.phtml b/app/views/index/index.phtml
index 0ddf4efdd..cf3e07752 100644
--- a/app/views/index/index.phtml
+++ b/app/views/index/index.phtml
@@ -1,4 +1,5 @@
-<?php $items = $this->entryPaginator->items (); ?>
+<?php $items = $this->entryPaginator->items (true); ?>
+
 <?php if (!empty ($items)) { ?>
 <div id="top">
 	<?php if (!login_is_conf ($this->conf) || is_logged ()) { ?>