public static function getInstance()
{
if (empty(self::$instance)) {
self::$instance = new self();
}
return self::$instance;
}
/**
* ye olde getTaggedWith method - hopefully superceeded by the tagged_with method
* @param [type] $tags [description]
* @param [type] $filterSql [description]
* @param integer $start [description]
* @param integer $limit [description]
* @param string $lookupMode if AND then you get content tagged with all ptovided tags
* if OR then you get content tagged with at least one of the provided tags
*/
public static function getTaggedWith($tags, $filterSql = null, $start = 0, $limit = 40, $lookupMode = 'OR')
{
// generate a cache key
$key = preg_replace('/[^A-Za-z0-9]/', '_', __FUNCTION__) . implode('_', array_map(array(get_called_class(), 'safe_args'), func_get_args()));
// chache hit?
if (empty(static::$cache[$key])) {
// clean up input
if (!is_array($tags)) {
$tags = static::explode_tags($tags);
}
if ($lookupMode != 'AND' && $lookupMode != 'OR') {
throw new Exception('Invalid lookupMode supplied');
}
// Set some vars
$classes = static::extended_classes();
$set = new ArrayList();
$db = AbcDB::getInstance();
$sql = '';
$tables = $joins = $filter = array();
// Build Query Data
foreach ($classes as $className) {
// Fetch Class Data
$table = static::table_for_class($className);
$extTable = static::extension_table_for_class_with_property($className, 'Tags');
// $tables we are working with
if ($table) {
$tables[$table] = $table;
}
// join
if ($table && $extTable && $table != $extTable) {
$joins[$table][] = $extTable;
} elseif ($extTable) {
$tables[$extTable] = $extTable;
}
// Where
if ($table) {
$where[$table][] = "LOWER(" . $table . ".ClassName) = '" . strtolower($className) . "'";
}
// Tag filter
// Should be REGEX so we don't get partial matches
if ($extTable) {
foreach ($tags as $tag) {
$filter[$table][] = $extTable . ".Tags REGEXP '(^|,| )+" . Convert::raw2sql($tag) . "(\$|,| )+'";
}
}
}
// Build Query
foreach ($tables as $table) {
if (array_key_exists($table, $joins)) {
// Prepare Where Statement
$uWhere = array_unique($where[$table]);
$uFilter = array_unique($filter[$table]);
// this lookupMode injection will prob break something in AND mode
$wSql = "(" . implode(' OR ', $uWhere) . ") AND (" . implode(' ' . $lookupMode . ' ', $uFilter) . ")";
// Make the rest of the SQL
if ($sql) {
$sql .= "UNION ALL" . "\n\n";
}
$rowCountSQL = !$sql ? "SQL_CALC_FOUND_ROWS " : "";
$sql .= "SELECT " . $rowCountSQL . $table . ".ClassName, " . $table . ".ID" . "\n";
$sql .= "FROM " . $table . "\n";
// join
$join = array_unique($joins[$table]);
foreach ($join as $j) {
$sql .= " LEFT JOIN " . $j . " ON " . $table . ".ID = " . $j . ".ID" . "\n";
}
// Add the WHERE statement
$sql .= "WHERE " . $wSql . "\n\n";
}
}
// Add Global Filter to Query
if ($filterSql) {
$sql .= (count($tables) == 1 ? "AND " : "WHERE ") . $filterSql;
}
// Add Limits to Query
$sql .= " LIMIT " . $start . "," . $limit;
// Get Data
$result = $db->query($sql);
$result = $result ? $result->fetchAll(PDO::FETCH_OBJ) : array();
// Convert to DOs
foreach ($result as $entry) {
// Make the data easier to work with
$entry = (object) $entry;
$className = $entry->ClassName;
// this is faster but might not pull in relations
//$dO = new $className;
//$dO = DataObjectHelper::populate($dO, $entry);
// this is slower, but will be more reliable
$dO = DataObject::get_by_id($className, $entry->ID);
$set->push($dO);
}
$set->unlimitedRowCount = $db->query('SELECT FOUND_ROWS() AS total')->fetch(PDO::FETCH_OBJ)->total;
static::$cache[$key] = $set;
}
return static::$cache[$key];
}
public static function getUnlimitedRowCountForSQL($sql)
{
$r = AbcDB::getInstance()->query($sql);
if ($r) {
return $r->fetch(PDO::FETCH_OBJ)->total;
}
return false;
}
/**
* kind of a wierd thing to add in here
* @param [type] $class [description]
* @param [type] $q [description]
* @param array $fields [description]
* @return [type] [description]
*/
public static function weighted_search($className, $q, array $fields = array('Content' => 1, 'Title' => 3), $start = 0, $limit = 10, $filterSql = null)
{
// sort out the cache
$implodedArgs = $className . '_' . $q . '_' . implode('_', $fields) . '_' . $start . '_' . $limit . '_' . $filterSql;
$cachekey = preg_replace("/[^A-Za-z0-9]/", '_', __FUNCTION__ . "_" . $implodedArgs);
$cache = SS_Cache::Factory('DataObjectSearch', 'Output', array('lifetime' => static::get_cache_time(), 'automatic_serialization' => true));
$set = $cache->load($cachekey);
// dont hit the db if we don't need to
if (!$set) {
// parse terms
// need to analyse various fragments like first 3 words, last 3 words
$terms = array_merge(static::str_to_terms($q), static::str_to_fragments($q));
$terms[] = $q;
$terms = array_unique($terms);
// Set some vars
$set = new ArrayList();
$db = AbcDB::getInstance();
$sql = '';
// expand search to subclasses
$classes = array_merge(ClassInfo::subclassesFor($className), array($className));
// iterate through list
foreach ($classes as $className) {
// Fetch Class Data
$table = DataObjectHelper::getTableForClass($className);
foreach ($fields as $field => $weight) {
foreach ($terms as $term) {
// init the recivers
$tables = $joins = $filter = array();
// $tables we are working with
if ($table) {
$tables[$table] = $table;
}
// Where
if ($table) {
$where[$table][] = $table . ".ClassName = '" . $className . "'";
}
// find the table the property is on
$extTable = DataObjectHelper::getExtensionTableForClassWithProperty($className, $field);
// join
if ($table && $extTable && $table != $extTable) {
$joins[$table][] = $extTable;
} elseif ($extTable) {
$tables[$extTable] = $extTable;
}
// ext table
if ($extTable) {
$filter[$table][] = $extTable . "." . $field . " LIKE '%" . Convert::raw2sql($term) . "%'";
} else {
$filter[$table][] = $table . "." . $field . " LIKE '%" . Convert::raw2sql($term) . "%'";
}
// Build Query
foreach ($tables as $table) {
// Prepare Where Statement
$uWhere = array_unique($where[$table]);
$uFilter = array_unique($filter[$table]);
// Where SQL
$wSql = "(" . implode(' OR ', $uWhere) . ") AND (" . implode(' OR ', $uFilter) . ")";
// Make the rest of the SQL
if ($sql) {
$sql .= " ) UNION ALL (" . "\n\n";
}
$sql .= "SELECT " . $table . ".ClassName, " . $table . ".ID, " . $weight . " AS weight" . "\n";
$sql .= "FROM " . $table . "\n";
// join
if (array_key_exists($table, $joins)) {
$join = array_unique($joins[$table]);
foreach ($join as $j) {
$sql .= " LEFT JOIN " . $j . " ON " . $table . ".ID = " . $j . ".ID" . "\n";
}
}
// Add the WHERE statement
$sql .= "WHERE " . $wSql . "\n\n";
}
// Add Global Filter to Query
if ($filterSql) {
$sql .= (count($tables) == 1 ? "AND " : "WHERE ") . $filterSql;
}
}
}
}
// Add Limits and order to Query
$sql = "\n SELECT SQL_CALC_FOUND_ROWS ClassName, ID, SUM(weight) AS total_weight\n FROM ((" . $sql . ")) AS t1\n GROUP BY ID\n ORDER BY total_weight DESC\n LIMIT " . $start . "," . $limit . "\n ";
// Get Data
// die('<br>' . $sql . '<br>');
$result = $db->query($sql);
$result = $result ? $result->fetchAll(PDO::FETCH_OBJ) : array();
// Convert to DOs
foreach ($result as $entry) {
// Make the data easier to work with
$entry = (object) $entry;
$className = $entry->ClassName;
// this is slower, but will be more reliable
$dO = DataObject::get_by_id($className, $entry->ID);
$set->push($dO);
}
// stash total length
$res = $db->query('SELECT FOUND_ROWS() AS total');
$set->unlimitedRowCount = 0;
if (!empty($res)) {
$resObj = $res->fetch(PDO::FETCH_OBJ);
if (!empty($resObj)) {
$set->unlimitedRowCount = $resObj->total;
}
}
// cache
$cache->save($set, $cachekey);
}
return $set;
}
