/** * 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; }