/**
* Create a new PolymorphicHasManyList relation list.
*
* @param string $dataClass The class of the DataObjects that this will list.
* @param string $foreignField The name of the composite foreign relation field. Used
* to generate the ID and Class foreign keys.
* @param string $foreignClass Name of the class filter this relation is filtered against
*/
function __construct($dataClass, $foreignField, $foreignClass)
{
// Set both id foreign key (as in HasManyList) and the class foreign key
parent::__construct($dataClass, "{$foreignField}ID");
$this->classForeignKey = "{$foreignField}Class";
// Ensure underlying DataQuery globally references the class filter
$this->dataQuery->setQueryParam('Foreign.Class', $foreignClass);
// For queries with multiple foreign IDs (such as that generated by
// DataList::relation) the filter must be generalised to filter by subclasses
$classNames = Convert::raw2sql(ClassInfo::subclassesFor($foreignClass));
$this->dataQuery->where(sprintf("\"{$this->classForeignKey}\" IN ('%s')", implode("', '", $classNames)));
}
/**
* Return a set type-formatted string
*
* @param array $values Contains a tokenised list of info about this data type
* @return string
*/
public function set($values)
{
//For reference, this is what typically gets passed to this function:
//$parts=Array('datatype'=>'enum', 'enums'=>$this->enum, 'character set'=>'utf8', 'collate'=>
// 'utf8_general_ci', 'default'=>$this->default);
//DB::requireField($this->tableName, $this->name, "enum('" . implode("','", $this->enum) . "') character set
//utf8 collate utf8_general_ci default '{$this->default}'");
$valuesString = implode(",", Convert::raw2sql($values['enums'], true));
$charset = Config::inst()->get('SilverStripe\\ORM\\Connect\\MySQLDatabase', 'charset');
$collation = Config::inst()->get('SilverStripe\\ORM\\Connect\\MySQLDatabase', 'collation');
return "set({$valuesString}) character set {$charset} collate {$collation}" . $this->defaultClause($values);
}
/**
* Return a SQL CONCAT() fragment suitable for a SELECT statement.
* Useful for custom queries which assume a certain member title format.
*
* @return String SQL
*/
public static function get_title_sql()
{
// This should be abstracted to SSDatabase concatOperator or similar.
$op = DB::get_conn() instanceof MSSQLDatabase ? " + " : " || ";
// Get title_format with fallback to default
$format = static::config()->title_format;
if (!$format) {
$format = ['columns' => ['Surname', 'FirstName'], 'sep' => ' '];
}
$columnsWithTablename = array();
foreach ($format['columns'] as $column) {
$columnsWithTablename[] = static::getSchema()->sqlColumnForField(__CLASS__, $column);
}
$sepSQL = Convert::raw2sql($format['sep'], true);
return "(" . join(" {$op} {$sepSQL} {$op} ", $columnsWithTablename) . ")";
}
/**
* The core search engine, used by this class and its subclasses to do fun stuff.
* Searches both SiteTree and File.
*
* @param array $classesToSearch
* @param string $keywords Keywords as a string.
* @param int $start
* @param int $pageLength
* @param string $sortBy
* @param string $extraFilter
* @param bool $booleanSearch
* @param string $alternativeFileFilter
* @param bool $invertedMatch
* @return \SilverStripe\ORM\PaginatedList
* @throws Exception
*/
public function searchEngine($classesToSearch, $keywords, $start, $pageLength, $sortBy = "Relevance DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false)
{
$pageClass = 'SilverStripe\\CMS\\Model\\SiteTree';
$fileClass = 'SilverStripe\\Assets\\File';
$pageTable = DataObject::getSchema()->tableName($pageClass);
$fileTable = DataObject::getSchema()->tableName($fileClass);
if (!class_exists($pageClass)) {
throw new Exception('MySQLDatabase->searchEngine() requires "SiteTree" class');
}
if (!class_exists($fileClass)) {
throw new Exception('MySQLDatabase->searchEngine() requires "File" class');
}
$keywords = $this->escapeString($keywords);
$htmlEntityKeywords = htmlentities($keywords, ENT_NOQUOTES, 'UTF-8');
$extraFilters = array($pageClass => '', $fileClass => '');
$boolean = '';
if ($booleanSearch) {
$boolean = "IN BOOLEAN MODE";
}
if ($extraFilter) {
$extraFilters[$pageClass] = " AND {$extraFilter}";
if ($alternativeFileFilter) {
$extraFilters[$fileClass] = " AND {$alternativeFileFilter}";
} else {
$extraFilters[$fileClass] = $extraFilters[$pageClass];
}
}
// Always ensure that only pages with ShowInSearch = 1 can be searched
$extraFilters[$pageClass] .= " AND ShowInSearch <> 0";
// File.ShowInSearch was added later, keep the database driver backwards compatible
// by checking for its existence first
$fields = $this->getSchemaManager()->fieldList($fileTable);
if (array_key_exists('ShowInSearch', $fields)) {
$extraFilters[$fileClass] .= " AND ShowInSearch <> 0";
}
$limit = $start . ", " . (int) $pageLength;
$notMatch = $invertedMatch ? "NOT " : "";
if ($keywords) {
$match[$pageClass] = "\n\t\t\t\tMATCH (Title, MenuTitle, Content, MetaDescription) AGAINST ('{$keywords}' {$boolean})\n\t\t\t\t+ MATCH (Title, MenuTitle, Content, MetaDescription) AGAINST ('{$htmlEntityKeywords}' {$boolean})\n\t\t\t";
$fileClassSQL = Convert::raw2sql($fileClass);
$match[$fileClass] = "MATCH (Name, Title) AGAINST ('{$keywords}' {$boolean}) AND ClassName = '{$fileClassSQL}'";
// We make the relevance search by converting a boolean mode search into a normal one
$relevanceKeywords = str_replace(array('*', '+', '-'), '', $keywords);
$htmlEntityRelevanceKeywords = str_replace(array('*', '+', '-'), '', $htmlEntityKeywords);
$relevance[$pageClass] = "MATCH (Title, MenuTitle, Content, MetaDescription) " . "AGAINST ('{$relevanceKeywords}') " . "+ MATCH (Title, MenuTitle, Content, MetaDescription) AGAINST ('{$htmlEntityRelevanceKeywords}')";
$relevance[$fileClass] = "MATCH (Name, Title) AGAINST ('{$relevanceKeywords}')";
} else {
$relevance[$pageClass] = $relevance[$fileClass] = 1;
$match[$pageClass] = $match[$fileClass] = "1 = 1";
}
// Generate initial DataLists and base table names
$lists = array();
$baseClasses = array($pageClass => '', $fileClass => '');
foreach ($classesToSearch as $class) {
$lists[$class] = DataList::create($class)->where($notMatch . $match[$class] . $extraFilters[$class]);
$baseClasses[$class] = '"' . $class . '"';
}
$charset = static::config()->get('charset');
// Make column selection lists
$select = array($pageClass => array("ClassName", "{$pageTable}.\"ID\"", "ParentID", "Title", "MenuTitle", "URLSegment", "Content", "LastEdited", "Created", "Name" => "_{$charset}''", "Relevance" => $relevance[$pageClass], "CanViewType"), $fileClass => array("ClassName", "{$fileTable}.\"ID\"", "ParentID", "Title", "MenuTitle" => "_{$charset}''", "URLSegment" => "_{$charset}''", "Content" => "_{$charset}''", "LastEdited", "Created", "Name", "Relevance" => $relevance[$fileClass], "CanViewType" => "NULL"));
// Process and combine queries
$querySQLs = array();
$queryParameters = array();
$totalCount = 0;
foreach ($lists as $class => $list) {
$table = DataObject::getSchema()->tableName($class);
/** @var SQLSelect $query */
$query = $list->dataQuery()->query();
// There's no need to do all that joining
$query->setFrom($table);
$query->setSelect($select[$class]);
$query->setOrderBy(array());
$querySQLs[] = $query->sql($parameters);
$queryParameters = array_merge($queryParameters, $parameters);
$totalCount += $query->unlimitedRowCount();
}
$fullQuery = implode(" UNION ", $querySQLs) . " ORDER BY {$sortBy} LIMIT {$limit}";
// Get records
$records = $this->preparedQuery($fullQuery, $queryParameters);
$objects = array();
foreach ($records as $record) {
$objects[] = new $record['ClassName']($record);
}
$list = new PaginatedList(new ArrayList($objects));
$list->setPageStart($start);
$list->setPageLength($pageLength);
$list->setTotalItems($totalCount);
// The list has already been limited by the query above
$list->setLimitItems(false);
return $list;
}
public function testAppendExtraFieldsToQuery()
{
$list = new ManyManyList('ManyManyListTest_ExtraFields', 'ManyManyListTest_ExtraFields_Clients', 'ManyManyListTest_ExtraFieldsID', 'ChildID', array('Worth' => 'Money', 'Reference' => 'Varchar'));
// ensure that ManyManyListTest_ExtraFields_Clients.ValueCurrency is
// selected.
$db = DB::get_conn();
$expected = 'SELECT DISTINCT "ManyManyListTest_ExtraFields_Clients"."WorthCurrency",' . ' "ManyManyListTest_ExtraFields_Clients"."WorthAmount", "ManyManyListTest_ExtraFields_Clients"."Reference",' . ' "ManyManyListTest_ExtraFields"."ClassName", "ManyManyListTest_ExtraFields"."LastEdited",' . ' "ManyManyListTest_ExtraFields"."Created", "ManyManyListTest_ExtraFields"."ID",' . ' CASE WHEN "ManyManyListTest_ExtraFields"."ClassName" IS NOT NULL THEN' . ' "ManyManyListTest_ExtraFields"."ClassName" ELSE ' . Convert::raw2sql('ManyManyListTest_ExtraFields', true) . ' END AS "RecordClassName" FROM "ManyManyListTest_ExtraFields" INNER JOIN' . ' "ManyManyListTest_ExtraFields_Clients" ON' . ' "ManyManyListTest_ExtraFields_Clients"."ManyManyListTest_ExtraFieldsID" =' . ' "ManyManyListTest_ExtraFields"."ID"';
$this->assertSQLEquals($expected, $list->sql($parameters));
}
/**
* Test that publishing processes respects lazy loaded fields
*/
public function testLazyLoadFields()
{
$originalMode = Versioned::get_reading_mode();
// Generate staging record and retrieve it from stage in live mode
Versioned::set_stage(Versioned::DRAFT);
$obj = new VersionedTest_Subclass();
$obj->Name = 'bob';
$obj->ExtraField = 'Field Value';
$obj->write();
$objID = $obj->ID;
$filter = sprintf('"VersionedTest_DataObject"."ID" = \'%d\'', Convert::raw2sql($objID));
Versioned::set_stage(Versioned::LIVE);
// Check fields are unloaded prior to access
$objLazy = Versioned::get_one_by_stage('VersionedTest_DataObject', 'Stage', $filter, false);
$lazyFields = $objLazy->getQueriedDatabaseFields();
$this->assertTrue(isset($lazyFields['ExtraField_Lazy']));
$this->assertEquals('VersionedTest_Subclass', $lazyFields['ExtraField_Lazy']);
// Check lazy loading works when viewing a Stage object in Live mode
$this->assertEquals('Field Value', $objLazy->ExtraField);
// Test that writeToStage respects lazy loaded fields
$objLazy = Versioned::get_one_by_stage('VersionedTest_DataObject', 'Stage', $filter, false);
$objLazy->writeToStage('Live');
$objLive = Versioned::get_one_by_stage('VersionedTest_DataObject', 'Live', $filter, false);
$liveLazyFields = $objLive->getQueriedDatabaseFields();
// Check fields are unloaded prior to access
$this->assertTrue(isset($liveLazyFields['ExtraField_Lazy']));
$this->assertEquals('VersionedTest_Subclass', $liveLazyFields['ExtraField_Lazy']);
// Check that live record has original value
$this->assertEquals('Field Value', $objLive->ExtraField);
Versioned::set_reading_mode($originalMode);
}
/**
* Ensure that the query is ready to execute.
*
* @param array|null $queriedColumns Any columns to filter the query by
* @return SQLSelect The finalised sql query
*/
public function getFinalisedQuery($queriedColumns = null)
{
if (!$queriedColumns) {
$queriedColumns = $this->queriedColumns;
}
if ($queriedColumns) {
$queriedColumns = array_merge($queriedColumns, array('Created', 'LastEdited', 'ClassName'));
}
$query = clone $this->query;
// Apply manipulators before finalising query
foreach ($this->getDataQueryManipulators() as $manipulator) {
$manipulator->beforeGetFinalisedQuery($this, $queriedColumns, $query);
}
$schema = DataObject::getSchema();
$baseDataClass = $schema->baseDataClass($this->dataClass());
$baseIDColumn = $schema->sqlColumnForField($baseDataClass, 'ID');
$ancestorClasses = ClassInfo::ancestry($this->dataClass(), true);
// Generate the list of tables to iterate over and the list of columns required
// by any existing where clauses. This second step is skipped if we're fetching
// the whole dataobject as any required columns will get selected regardless.
if ($queriedColumns) {
// Specifying certain columns allows joining of child tables
$tableClasses = ClassInfo::dataClassesFor($this->dataClass);
// Ensure that any filtered columns are included in the selected columns
foreach ($query->getWhereParameterised($parameters) as $where) {
// Check for any columns in the form '"Column" = ?' or '"Table"."Column"' = ?
if (preg_match_all('/(?:"(?<table>[^"]+)"\\.)?"(?<column>[^"]+)"(?:[^\\.]|$)/', $where, $matches, PREG_SET_ORDER)) {
foreach ($matches as $match) {
$column = $match['column'];
if (!in_array($column, $queriedColumns)) {
$queriedColumns[] = $column;
}
}
}
}
} else {
$tableClasses = $ancestorClasses;
}
// Iterate over the tables and check what we need to select from them. If any selects are made (or the table is
// required for a select)
foreach ($tableClasses as $tableClass) {
// Determine explicit columns to select
$selectColumns = null;
if ($queriedColumns) {
// Restrict queried columns to that on the selected table
$tableFields = $schema->databaseFields($tableClass, false);
unset($tableFields['ID']);
$selectColumns = array_intersect($queriedColumns, array_keys($tableFields));
}
// If this is a subclass without any explicitly requested columns, omit this from the query
if (!in_array($tableClass, $ancestorClasses) && empty($selectColumns)) {
continue;
}
// Select necessary columns (unless an explicitly empty array)
if ($selectColumns !== array()) {
$this->selectColumnsFromTable($query, $tableClass, $selectColumns);
}
// Join if not the base table
if ($tableClass !== $baseDataClass) {
$tableName = $schema->tableName($tableClass);
$query->addLeftJoin($tableName, "\"{$tableName}\".\"ID\" = {$baseIDColumn}", $tableName, 10);
}
}
// Resolve colliding fields
if ($this->collidingFields) {
foreach ($this->collidingFields as $collisionField => $collisions) {
$caseClauses = array();
foreach ($collisions as $collision) {
if (preg_match('/^"(?<table>[^"]+)"\\./', $collision, $matches)) {
$collisionTable = $matches['table'];
$collisionClass = $schema->tableClass($collisionTable);
if ($collisionClass) {
$collisionClassColumn = $schema->sqlColumnForField($collisionClass, 'ClassName');
$collisionClasses = ClassInfo::subclassesFor($collisionClass);
$collisionClassesSQL = implode(', ', Convert::raw2sql($collisionClasses, true));
$caseClauses[] = "WHEN {$collisionClassColumn} IN ({$collisionClassesSQL}) THEN {$collision}";
}
} else {
user_error("Bad collision item '{$collision}'", E_USER_WARNING);
}
}
$query->selectField("CASE " . implode(" ", $caseClauses) . " ELSE NULL END", $collisionField);
}
}
if ($this->filterByClassName) {
// If querying the base class, don't bother filtering on class name
if ($this->dataClass != $baseDataClass) {
// Get the ClassName values to filter to
$classNames = ClassInfo::subclassesFor($this->dataClass);
$classNamesPlaceholders = DB::placeholders($classNames);
$baseClassColumn = $schema->sqlColumnForField($baseDataClass, 'ClassName');
$query->addWhere(array("{$baseClassColumn} IN ({$classNamesPlaceholders})" => $classNames));
}
}
// Select ID
$query->selectField($baseIDColumn, "ID");
// Select RecordClassName
$baseClassColumn = $schema->sqlColumnForField($baseDataClass, 'ClassName');
$query->selectField("\n\t\t\tCASE WHEN {$baseClassColumn} IS NOT NULL THEN {$baseClassColumn}\n\t\t\tELSE " . Convert::raw2sql($baseDataClass, true) . " END", "RecordClassName");
// TODO: Versioned, Translatable, SiteTreeSubsites, etc, could probably be better implemented as subclasses
// of DataQuery
$obj = Injector::inst()->get($this->dataClass);
$obj->extend('augmentSQL', $query, $this);
$this->ensureSelectContainsOrderbyColumns($query);
// Apply post-finalisation manipulations
foreach ($this->getDataQueryManipulators() as $manipulator) {
$manipulator->afterGetFinalisedQuery($this, $queriedColumns, $query);
}
return $query;
}
