[pLog-svn] r4677 - plog/branches/lifetype-1.2/class/dao
oscar at devel.lifetype.net
oscar at devel.lifetype.net
Fri Feb 2 17:57:32 EST 2007
Author: oscar
Date: 2007-02-02 17:57:32 -0500 (Fri, 02 Feb 2007)
New Revision: 4677
Modified:
plog/branches/lifetype-1.2/class/dao/searchengine.class.php
Log:
Implemented Jordi's patch for the SearchEngine class to use the FULLTEXT indexes. Since we cannot fully rely on these indexes for some character sets, I've left the old code for those cases when PDbMySQLDriver::isFullTextSupported() returns false and implemented new faster queries for those cases when we can take the fast route.
I've also had to improve Jordi's query to search not only the lt_articles_text table but also the lt_custom_fields_values one as custom fields should also be searchable.
Modified: plog/branches/lifetype-1.2/class/dao/searchengine.class.php
===================================================================
--- plog/branches/lifetype-1.2/class/dao/searchengine.class.php 2007-02-02 22:54:59 UTC (rev 4676)
+++ plog/branches/lifetype-1.2/class/dao/searchengine.class.php 2007-02-02 22:57:32 UTC (rev 4677)
@@ -80,34 +80,56 @@
*/
function search( $blogId, $searchTerms, $status = POST_STATUS_PUBLISHED, $includeFuture = true, $page = -1, $itemsPerPage = -1 )
{
- $prefix = $this->getPrefix();
- $query = "SELECT DISTINCT a.id AS id FROM {$prefix}articles a ".
- "WHERE ".$this->getArticleSearchConditions( $searchTerms );
-
+ // calculate the conditions right away, they will be used by both sides of the union
+ $conds = "";
if( $blogId != -1 )
- $query .= " AND a.blog_id = ".Db::qstr( $blogId );
-
+ $conds .= " AND a.blog_id = ".Db::qstr( $blogId );
if( $status != -1 )
- $query .= " AND a.status = ".$status;
-
+ $conds .= " AND a.status = ".$status;
if( !$includeFuture )
- $query .= " AND a.date < NOW()";
-
- $query .= " ORDER BY a.date DESC";
+ $conds .= " AND a.date < NOW()";
+
+ // first change
+ $prefix = $this->getPrefix();
+ // check if we can use fulltext indexes
+ $db =& Db::getDb();
+ if( $db->isFullTextSupported()) {
+ $query = "(SELECT a.* FROM {$prefix}articles a
+ INNER JOIN {$prefix}articles_text at ON a.id = at.article_id
+ WHERE MATCH(at.normalized_text, at.normalized_topic) AGAINST ('{$searchTerms}' IN BOOLEAN MODE)
+ {$conds})
+ UNION
+ (SELECT a.* FROM {$prefix}articles a
+ INNER JOIN {$prefix}custom_fields_values cfv ON a.id = cfv.article_id
+ WHERE MATCH(cfv.normalized_value) AGAINST ('{$searchTerms}' IN BOOLEAN MODE)
+ {$conds})
+ ORDER BY date DESC";
+ }
+ else {
+ $query = "SELECT DISTINCT a.id AS id FROM {$prefix}articles a ".
+ "WHERE ".$this->getArticleSearchConditions( $searchTerms )." {$conds} ORDER BY a.date DESC";
+ }
+
$result = $this->Execute( $query, $page, $itemsPerPage );
-
- lt_include( PLOG_CLASS_PATH."class/dao/articles.class.php" );
+
+ include_once( PLOG_CLASS_PATH."class/dao/articles.class.php" );
$articles = new Articles();
-
+
if( !$result )
return( Array());
-
+
$results = Array();
while( $row = $result->FetchRow()) {
- $results[] = new SearchResult( $articles->getArticle( $row["id"] ), SEARCH_RESULT_ARTICLE, $searchTerms );
+ // depending on whether fulltext is available, we will have either fetched all we need so that
+ // we can map the row directly into an object, or then we have to use the cache to get all
+ // articles one by one
+ if( $db->isFullTextSupported())
+ $results[] = new SearchResult( $articles->mapRow( $row ), SEARCH_RESULT_ARTICLE, $searchTerms );
+ else
+ $results[] = new SearchResult( $articles->getArticle( $row["id"] ), SEARCH_RESULT_ARTICLE, $searchTerms );
}
-
+
return( $results );
}
@@ -123,20 +145,49 @@
*/
function getNumSearchResults( $blogId, $searchTerms, $status, $includeFuture = true )
{
- $prefix = $this->getPrefix();
- $query = $this->getArticleSearchConditions( $searchTerms );
-
+ $prefix = $this->getPrefix();
+
+ // calculate the additional conditions beforehad
+ $conds = "";
if( $blogId != -1 )
- $query .= " AND a.blog_id = ".Db::qstr( $blogId );
-
+ $conds .= " AND a.blog_id = ".Db::qstr( $blogId );
if( $status != -1 )
- $query .= " AND a.status = ".$status;
-
+ $conds .= " AND a.status = ".$status;
if( !$includeFuture )
- $query .= " AND a.date < NOW()";
-
- $total = $this->getNumItems( "{$prefix}articles a", $query, "a.id" );
- return( $total );
+ $conds .= " AND a.date < NOW()";
+
+ // check if the db supports fulltext searches and if so act accordingly
+ $db =& Db::getDb();
+ if( $db->isFullTextSupported()) {
+ // faster path via the fulltext indexes
+ $query = "(SELECT COUNT(a.id) AS total FROM {$prefix}articles a
+ INNER JOIN {$prefix}articles_text at ON a.id = at.article_id
+ WHERE MATCH(at.normalized_text, at.normalized_topic) AGAINST ('{$searchTerms}' IN BOOLEAN MODE)
+ {$conds})
+ UNION
+ (SELECT COUNT(a.id) AS total FROM {$prefix}articles a
+ INNER JOIN {$prefix}custom_fields_values cfv ON a.id = cfv.article_id
+ WHERE MATCH(cfv.normalized_value) AGAINST ('{$searchTerms}' IN BOOLEAN MODE)
+ {$conds})";
+ // execute the query, and it should give us exactly two rows: one per each one of the queries of the union, so
+ // the total amount of posts that match the search condition should be the sum of those two rows
+ $result = $this->Execute( $query );
+ if( !$result )
+ return 0;
+
+ $total = 0;
+ while( $row = $result->FetchRow()) {
+ $total += $row["total"];
+ }
+ }
+ else {
+ // alternative, slower path
+ $query = $this->getArticleSearchConditions( $searchTerms );
+
+ $total = $this->getNumItems( "{$prefix}articles a", $query, "a.id" );
+ }
+
+ return( $total );
}
/**
@@ -163,7 +214,7 @@
$tmpCond = $comments->getSearchConditions( $searchTerms );
$query = "SELECT c.article_id AS article_id FROM {$prefix}articles_comments c
WHERE $tmpCond AND c.status = ".COMMENT_STATUS_NONSPAM;
- $result = $this->Execute( $query );
+ $result = $this->Execute( $query );
$ids = Array();
while( $row = $result->FetchRow()) {
@@ -179,7 +230,7 @@
$fields = new CustomFieldsValues();
$tmpCond = $fields->getSearchConditions( $searchTerms );
$query = "SELECT v.article_id AS article_id FROM {$prefix}custom_fields_values v WHERE $tmpCond";
- $result = $this->Execute( $query );
+ $result = $this->Execute( $query );
$ids = Array();
while( $row = $result->FetchRow()) {
More information about the pLog-svn
mailing list