[pLog-svn] r2779 - in plog/trunk/class: dao view/admin
oscar at devel.lifetype.net
oscar at devel.lifetype.net
Thu Jan 12 22:45:38 GMT 2006
Author: oscar
Date: 2006-01-12 22:45:38 +0000 (Thu, 12 Jan 2006)
New Revision: 2779
Modified:
plog/trunk/class/dao/articles.class.php
plog/trunk/class/dao/model.class.php
plog/trunk/class/view/admin/admindashboardview.class.php
Log:
I had to reconsider some ideas regarding caching of long lists of articles... It is overkill to load all articles at once, cache them all and then return only what we need because the system will slow down considerably and we'll hit the 8mb limit. I reverted Articles::getBlogArticles and Articles::getNumBlogArticles to what they basically were in 1.0.x (plus just a bit of caching) and things seem to work much better now (both in performance and memory) Regarding memory, the db from lifetype.net fits perfectly now in between 3.5 and 5.0mb memory depending on the situation, which is quite an improvement!
Modified: plog/trunk/class/dao/articles.class.php
===================================================================
--- plog/trunk/class/dao/articles.class.php 2006-01-12 22:35:22 UTC (rev 2778)
+++ plog/trunk/class/dao/articles.class.php 2006-01-12 22:45:38 UTC (rev 2779)
@@ -51,7 +51,7 @@
{
$blogArticles = $this->getMany( "blog_id",
$blogId,
- CACHE_ARTICLES_BYBLOG,
+ null,
Array( CACHE_ARTICLES => "getId" ),
Array( "date" => "DESC" ),
$searchTerms,
@@ -274,18 +274,20 @@
$status = POST_STATUS_PUBLISHED,
$userId = 0,
$maxDate = 0,
- $searchTerms = Array())
- {
- $articles = $this->getBlogArticles( $blogId,
- $date,
- -1,
- $categoryId,
- $status,
- $userId,
- $maxDate,
- $searchTerms );
+ $searchTerms = "")
+ {
+ $postStatus = $status;
+ $prefix = $this->getPrefix();
+ $where = $this->buildWhere( $blogId, $date, $amount, $categoryId, $status, $userId, $maxDate, $searchTerms );
+ $query = "SELECT COUNT(*) AS total FROM {$prefix}articles a, {$prefix}articles_categories c, {$prefix}article_categories_link l WHERE $where ";
- return count($articles);
+ $result = $this->_db->Execute( $query );
+
+ if( !$result )
+ return 0;
+
+ $number = $result->RowCount();
+ return( $number );
}
/**
@@ -372,7 +374,66 @@
return( $searchCondition );
}
+
+ /**
+ * builds a WHERE clause for a query
+ *
+ * @private
+ */
+ function buildWhere( $blogId, $date = -1, $amount = -1, $categoryId = 0, $status = 0, $userId = 0, $maxDate = 0, $searchTerms = "" )
+ {
+ $postStatus = $status;
+ $prefix = $this->getPrefix();
+ if($blogId == -1){
+ $query = "a.blog_id = a.blog_id";
+ }
+ else{
+ $query = "a.blog_id = ".Db::qstr($blogId);
+ }
+ if( $date != -1 ) {
+ // consider the time difference
+ include_once( PLOG_CLASS_PATH."class/dao/blogs.class.php" );
+ $blogs = new Blogs();
+ $blogInfo = $blogs->getBlogInfo( $blogId );
+ $blogSettings = $blogInfo->getSettings();
+ $timeDifference = $blogSettings->getValue( "time_offset" );
+ $SecondsDiff = $timeDifference * 3600;
+ $query .= " AND FROM_UNIXTIME(UNIX_TIMESTAMP(a.date)+$SecondsDiff)+0 LIKE '$date%'";
+ }
+ // the common part "c.id = a.category_id" is needed so that
+ // we don't get one article row as many times as the amount of categories
+ // we have... due to the sql 'join' operation we're carrying out
+ if( $categoryId == -1 )
+ $query .= " AND c.id = l.category_id AND a.id = l.article_id ";
+ else {
+ if( $categoryId > 0 )
+ $query .= " AND a.id = l.article_id AND l.category_id = $categoryId AND c.id = l.category_id";
+ else {
+ $query .= " AND c.id = l.category_id AND a.id = l.article_id AND c.in_main_page = 1";
+ }
+ }
+
+ if( $status > 0 )
+ $query .= " AND a.status = '$postStatus'";
+ if( $userId > 0 )
+ $query .= " AND a.user_id = ".Db::qstr($userId);
+ if( $maxDate > 0 )
+ $query .= " AND a.date <= '$maxDate'";
+
+ // in case there were some search terms specified as parameters...
+ if( $searchTerms != "" ) {
+ $whereString = $this->getSearchConditions( $searchTerms );
+ // and add it to the current search
+ $query .=" AND {$whereString} ";
+ }
+
+ if( $categoryId <= 0 )
+ $query .= " GROUP BY a.id ";
+
+ return $query;
+ }
+
/**
* Returns all the articles for a given blog, according to the conditions specified in
* the call. If this function is too cumbersome to use (I reckon it might be,
@@ -402,25 +463,65 @@
$searchTerms = "",
$page = -1 )
{
- $articles = $this->getArticles( $blogId, $searchTerms );
-
- if( !$articles )
- $articles = Array();
+ // build the query
+ // the query gets quite complicated to build because we have to take care of plenty
+ // of conditions, such as the maximum date, the amount, the category,
+ // wether the category has to be shown in the main page or not, etc...
+ $postStatus = $status;
+ $prefix = $this->getPrefix();
+ $where = $this->buildWhere( $blogId, $date, $amount, $categoryId, $status, $userId, $maxDate, $searchTerms );
+ $query = "SELECT a.id as id, a.id, a.date,
+ a.user_id,a.blog_id,a.status,a.properties,
+ a.num_reads, a.slug, 1 AS relevance FROM {$prefix}articles a, {$prefix}articles_categories c,
+ {$prefix}article_categories_link l";
+ if( $searchTerms != "" )
+ $query .= ", {$prefix}articles_text t ";
+ $query .= " WHERE ";
+ if( $searchTerms != "" )
+ $query .= " t.article_id = a.id AND ";
+ $query .= " $where";
+
+
+ // if we're doing a search, we should sort by relevance
+ if( $searchTerms != "" ) {
+ $query .= " ORDER BY relevance";
+ }
+ else {
+ $query .= " ORDER BY a.date DESC";
+ }
- $result = Array();
- $total = 0;
- foreach( $articles as $article ) {
- if( $this->check( $article, $date, $categoryId, $status, $userId, $maxDate )) {
- $result[] = $article;
- $total++;
- }
- }
-
- // do the slicing only if necessary
- if( $page > -1 && $amount > -1 )
- $result = array_slice( $result, ($page-1) * $amount, $amount );
-
- return( $result );
+ // we don't need limits if we're getting the posts for a given day
+ if( ($amount > 0) && ($date == -1) && ($page == -1 ))
+ $query .= " LIMIT $amount;";
+
+ // in case we're using a paged display
+ if( $page > 0 ) {
+ $start = (($page - 1) * $amount);
+ $query .= " LIMIT $start, $amount";
+ }
+
+ // execute the query
+ $result = $this->Execute( $query );
+
+ if( !$result )
+ return Array();
+
+ if( $result->RowCount() == 0 )
+ return Array();
+
+ $articles = Array();
+ while( $row = $result->FetchRow()) {
+ // map the row to an object
+ $article = $this->mapRow( $row );
+ $articles[] = $article;
+ // and cache it for later use, we might need it
+ $this->_cache->setData( $article->getId(), CACHE_ARTICLES, $article );
+ $this->_cache->setData( $article->getPostSlug(), CACHE_ARTICLES_BYNAME, $article );
+ }
+
+ $result->Close();
+
+ return $articles;
}
/**
@@ -1066,9 +1167,9 @@
$article->setCategories( $articleCategories );
// get information about the categories of the article
$article->setBlogInfo( $blogInfo );
- if ( $this->users === null )
+ /*if ( $this->users === null )
$this->users = new Users();
- $article->setUserInfo( $this->users->getUserInfoFromId( $query_result['user_id'] ));
+ $article->setUserInfo( $this->users->getUserInfoFromId( $query_result['user_id'] ));*/
// counters
$article->setTotalComments( $query_result['num_comments'] );
Modified: plog/trunk/class/dao/model.class.php
===================================================================
--- plog/trunk/class/dao/model.class.php 2006-01-12 22:35:22 UTC (rev 2778)
+++ plog/trunk/class/dao/model.class.php 2006-01-12 22:45:38 UTC (rev 2779)
@@ -310,11 +310,10 @@
$orderBy .= "$field $dir";
}
}
+
// build the query including all parts
$query = $query.$where.$orderBy;
- $this->log->debug("query = $query");
-
$result = $this->Execute( $query );
if( !$result )
Modified: plog/trunk/class/view/admin/admindashboardview.class.php
===================================================================
--- plog/trunk/class/view/admin/admindashboardview.class.php 2006-01-12 22:35:22 UTC (rev 2778)
+++ plog/trunk/class/view/admin/admindashboardview.class.php 2006-01-12 22:45:38 UTC (rev 2779)
@@ -72,13 +72,22 @@
$numOwnedBlogs = 0;
foreach( $this->_userBlogs as $userBlog ) {
$recentPosts[$userBlog->getId()] = $articles->getBlogArticles( $userBlog->getId(),
- -1,
- DASHBOARD_MAX_RECENT_ITEMS,
- 0,
+ -1, // no date,
+ DASHBOARD_MAX_RECENT_ITEMS,
+ 0,
POST_STATUS_PUBLISHED );
- $recentComments[$userBlog->getId()] = $comments->getBlogComments ( $userBlog->getId(), DASHBOARD_MAX_RECENT_ITEMS );
- $recentTrackbacks[$userBlog->getId()] = $trackbacks->getBlogTrackbacks( $userBlog->getId(), DASHBOARD_MAX_RECENT_ITEMS );
-
+ $recentComments[$userBlog->getId()] = $comments->getBlogComments ( $userBlog->getId(),
+ COMMENT_ORDER_NEWEST_FIRST,
+ COMMENT_STATUS_ALL,
+ "", // no search terms
+ 1, // first page
+ DASHBOARD_MAX_RECENT_ITEMS );
+ $recentTrackbacks[$userBlog->getId()] = $trackbacks->getBlogTrackbacks( $userBlog->getId(),
+ COMMENT_ORDER_NEWEST_FIRST,
+ COMMENT_STATUS_ALL,
+ "",
+ 1,
+ DASHBOARD_MAX_RECENT_ITEMS );
if( $userBlog->getOwner() == $this->_userInfo->getId())
$numOwnedBlogs++;
}
More information about the pLog-svn
mailing list