From 91d8a28c664afa5378735bcd0efe068dd74d589f Mon Sep 17 00:00:00 2001 From: Thomas Lange Date: Sun, 1 Apr 2018 19:16:13 +0200 Subject: Use method "buildByAttribute" to create Item instances for item listing MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This commit significantly reduces the number of database queries required to display a list of pages, posts or users. This could be achieved by using "SELECT * FROM […]" in combination with the new implemented factory method "buildByAttribute". Previously, the first database query returned an array of unique item IDs that were then passed to the factory method "build" within the "foreach" loop which caused the application to make an additional database query like "SELECT * FROM […] WHERE id = {current_id}" for every single item ID to get it's payload data. Since this commit, this additional query for every item is not necessary anymore. --- admin/page/index.php | 10 +++++----- admin/post/index.php | 10 +++++----- admin/user/index.php | 10 +++++----- core/include/feed/main.php | 16 ++++++++-------- core/include/home.php | 8 +++----- core/include/page/list.php | 8 ++++---- core/include/post/list.php | 8 ++++---- core/include/user/list.php | 8 ++++---- 8 files changed, 38 insertions(+), 40 deletions(-) diff --git a/admin/page/index.php b/admin/page/index.php index c14bf13..caff257 100644 --- a/admin/page/index.php +++ b/admin/page/index.php @@ -26,18 +26,18 @@ if($currentSite < 1 OR ($currentSite > $lastSite AND $lastSite > 0)) { } #=============================================================================== -# Fetch page IDs from database +# Fetch items from database #=============================================================================== -$execSQL = "SELECT id FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; -$pageIDs = $Database->query(sprintf($execSQL, Page\Attribute::TABLE))->fetchAll($Database::FETCH_COLUMN); +$execSQL = "SELECT * FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; +$Statement = $Database->query(sprintf($execSQL, Page\Attribute::TABLE)); #=============================================================================== # TRY: Template\Exception #=============================================================================== try { - foreach($pageIDs as $pageID) { + while($Attribute = $Statement->fetchObject('Page\Attribute')) { try { - $Page = Page\Factory::build($pageID); + $Page = Page\Factory::buildByAttribute($Attribute); $User = User\Factory::build($Page->attr('user')); $ItemTemplate = generatePageItemTemplate($Page, $User); diff --git a/admin/post/index.php b/admin/post/index.php index 57b19e9..7e492ba 100644 --- a/admin/post/index.php +++ b/admin/post/index.php @@ -26,18 +26,18 @@ if($currentSite < 1 OR ($currentSite > $lastSite AND $lastSite > 0)) { } #=============================================================================== -# Fetch post IDs from database +# Fetch items from database #=============================================================================== -$execSQL = "SELECT id FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; -$postIDs = $Database->query(sprintf($execSQL, Post\Attribute::TABLE))->fetchAll($Database::FETCH_COLUMN); +$execSQL = "SELECT * FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; +$Statement = $Database->query(sprintf($execSQL, Post\Attribute::TABLE)); #=============================================================================== # TRY: Template\Exception #=============================================================================== try { - foreach($postIDs as $postID) { + while($Attribute = $Statement->fetchObject('Post\Attribute')) { try { - $Post = Post\Factory::build($postID); + $Post = Post\Factory::buildByAttribute($Attribute); $User = User\Factory::build($Post->attr('user')); $ItemTemplate = generatePostItemTemplate($Post, $User); diff --git a/admin/user/index.php b/admin/user/index.php index b474fc8..dc5a4e7 100644 --- a/admin/user/index.php +++ b/admin/user/index.php @@ -26,18 +26,18 @@ if($currentSite < 1 OR ($currentSite > $lastSite AND $lastSite > 0)) { } #=============================================================================== -# Fetch user IDs from database +# Fetch items from database #=============================================================================== -$execSQL = "SELECT id FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; -$userIDs = $Database->query(sprintf($execSQL, User\Attribute::TABLE))->fetchAll($Database::FETCH_COLUMN); +$execSQL = "SELECT * FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; +$Statement = $Database->query(sprintf($execSQL, User\Attribute::TABLE)); #=============================================================================== # TRY: Template\Exception #=============================================================================== try { - foreach($userIDs as $userID) { + while($Attribute = $Statement->fetchObject('User\Attribute')) { try { - $User = User\Factory::build($userID); + $User = User\Factory::buildByAttribute($Attribute); $ItemTemplate = generateUserItemTemplate($User); $users[] = $ItemTemplate; diff --git a/core/include/feed/main.php b/core/include/feed/main.php index b8e03dc..84f3edb 100644 --- a/core/include/feed/main.php +++ b/core/include/feed/main.php @@ -18,12 +18,12 @@ try { $POST['FEED_SORT'] = Application::get('POST.FEED_SORT'); $POST['FEED_SIZE'] = Application::get('POST.FEED_SIZE'); - $execSQL = "SELECT id FROM %s ORDER BY {$POST['FEED_SORT']} LIMIT {$POST['FEED_SIZE']}"; - $postIDs = $Database->query(sprintf($execSQL, Post\Attribute::TABLE))->fetchAll($Database::FETCH_COLUMN); + $execSQL = "SELECT * FROM %s ORDER BY {$POST['FEED_SORT']} LIMIT {$POST['FEED_SIZE']}"; + $Statement = $Database->query(sprintf($execSQL, Post\Attribute::TABLE)); - foreach($postIDs as $postID) { + while($Attribute = $Statement->fetchObject('Post\Attribute')) { try { - $Post = Post\Factory::build($postID); + $Post = Post\Factory::buildByAttribute($Attribute); $User = User\Factory::build($Post->attr('user')); $ItemTemplate = Template\Factory::build('feed/item_post'); @@ -42,12 +42,12 @@ try { $PAGE['FEED_SORT'] = Application::get('PAGE.FEED_SORT'); $PAGE['FEED_SIZE'] = Application::get('PAGE.FEED_SIZE'); - $execSQL = "SELECT id FROM %s ORDER BY {$PAGE['FEED_SORT']} LIMIT {$PAGE['FEED_SIZE']}"; - $pageIDs = $Database->query(sprintf($execSQL, Page\Attribute::TABLE))->fetchAll($Database::FETCH_COLUMN); + $execSQL = "SELECT * FROM %s ORDER BY {$PAGE['FEED_SORT']} LIMIT {$PAGE['FEED_SIZE']}"; + $Statement = $Database->query(sprintf($execSQL, Page\Attribute::TABLE)); - foreach($pageIDs as $pageID) { + while($Attribute = $Statement->fetchObject('Page\Attribute')) { try { - $Page = Page\Factory::build($pageID); + $Page = Page\Factory::buildByAttribute($Attribute); $User = User\Factory::build($Page->attr('user')); $ItemTemplate = Template\Factory::build('feed/item_page'); diff --git a/core/include/home.php b/core/include/home.php index ce91558..2fe81a2 100644 --- a/core/include/home.php +++ b/core/include/home.php @@ -9,14 +9,12 @@ $Language = Application::getLanguage(); # TRY: Template\Exception #=============================================================================== try { - $execSQL = 'SELECT id FROM %s ORDER BY '.Application::get('POST.LIST_SORT').' LIMIT '.Application::get('POST.LIST_SIZE'); + $execSQL = 'SELECT * FROM %s ORDER BY '.Application::get('POST.LIST_SORT').' LIMIT '.Application::get('POST.LIST_SIZE'); $Statement = $Database->query(sprintf($execSQL, Post\Attribute::TABLE)); - $postIDs = $Statement->fetchAll($Database::FETCH_COLUMN); - - foreach($postIDs as $postID) { + while($Attribute = $Statement->fetchObject('Post\Attribute')) { try { - $Post = Post\Factory::build($postID); + $Post = Post\Factory::buildByAttribute($Attribute); $User = User\Factory::build($Post->attr('user')); $ItemTemplate = generatePostItemTemplate($Post, $User); diff --git a/core/include/page/list.php b/core/include/page/list.php index 022478e..656d01a 100644 --- a/core/include/page/list.php +++ b/core/include/page/list.php @@ -34,12 +34,12 @@ if(Application::get('PAGE.SINGLE_REDIRECT') === TRUE AND $count === '1') { # TRY: Template\Exception #=============================================================================== try { - $execSQL = "SELECT id FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; - $pageIDs = $Database->query(sprintf($execSQL, Page\Attribute::TABLE))->fetchAll($Database::FETCH_COLUMN); + $execSQL = "SELECT * FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; + $Statement = $Database->query(sprintf($execSQL, Page\Attribute::TABLE)); - foreach($pageIDs as $pageID) { + while($Attribute = $Statement->fetchObject('Page\Attribute')) { try { - $Page = Page\Factory::build($pageID); + $Page = Page\Factory::buildByAttribute($Attribute); $User = User\Factory::build($Page->attr('user')); $ItemTemplate = generatePageItemTemplate($Page, $User); diff --git a/core/include/post/list.php b/core/include/post/list.php index 3ba2dba..6ade592 100644 --- a/core/include/post/list.php +++ b/core/include/post/list.php @@ -34,12 +34,12 @@ if(Application::get('POST.SINGLE_REDIRECT') === TRUE AND $count === '1') { # TRY: Template\Exception #=============================================================================== try { - $execSQL = "SELECT id FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; - $postIDs = $Database->query(sprintf($execSQL, Post\Attribute::TABLE))->fetchAll($Database::FETCH_COLUMN); + $execSQL = "SELECT * FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; + $Statement = $Database->query(sprintf($execSQL, Post\Attribute::TABLE)); - foreach($postIDs as $postID) { + while($Attribute = $Statement->fetchObject('Post\Attribute')) { try { - $Post = Post\Factory::build($postID); + $Post = Post\Factory::buildByAttribute($Attribute); $User = User\Factory::build($Post->attr('user')); $ItemTemplate = generatePostItemTemplate($Post, $User); diff --git a/core/include/user/list.php b/core/include/user/list.php index 4ce9fc8..adc28ec 100644 --- a/core/include/user/list.php +++ b/core/include/user/list.php @@ -34,12 +34,12 @@ if(Application::get('USER.SINGLE_REDIRECT') === TRUE AND $count === '1') { # TRY: Template\Exception #=============================================================================== try { - $execSQL = "SELECT id FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; - $userIDs = $Database->query(sprintf($execSQL, User\Attribute::TABLE))->fetchAll($Database::FETCH_COLUMN); + $execSQL = "SELECT * FROM %s ORDER BY {$site_sort} LIMIT ".(($currentSite-1) * $site_size).", {$site_size}"; + $Statement = $Database->query(sprintf($execSQL, User\Attribute::TABLE)); - foreach($userIDs as $userID) { + while($Attribute = $Statement->fetchObject('User\Attribute')) { try { - $User = User\Factory::build($userID); + $User = User\Factory::buildByAttribute($Attribute); $ItemTemplate = generateUserItemTemplate($User); $users[] = $ItemTemplate; -- cgit v1.2.3