SilverStripe\PostgreSQL\PostgreSQLDatabase::searchEngine()

Complexity

Conditions	10
Paths	114

Size

Total Lines	134
Code Lines	90

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
    public function searchEngine($classesToSearch, $keywords, $start, $pageLength, $sortBy = "ts_rank DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false)
    {
        $start = (int)$start;
        $pageLength = (int)$pageLength;

        //Fix the keywords to be ts_query compatitble:
        //Spaces must have pipes
        //@TODO: properly handle boolean operators here.
        $keywords= trim($keywords);
        $keywords= str_replace(' ', ' | ', $keywords);
        $keywords= str_replace('"', "'", $keywords);


        $keywords = $this->quoteString(trim($keywords));

        // Get tables
        $tablesToSearch = [];
        foreach ($classesToSearch as $class) {
            $tablesToSearch[$class] = DataObject::getSchema()->baseDataTable($class);
        }

        //We can get a list of all the tsvector columns though this query:
        //We know what tables to search in based on the $classesToSearch variable:
        $classesPlaceholders = DB::placeholders($classesToSearch);
        $searchableColumns = $this->preparedQuery(
            "
            SELECT table_name, column_name, data_type
            FROM information_schema.columns
            WHERE data_type='tsvector' AND table_name in ($classesPlaceholders);",
            array_values($tablesToSearch)
        );
        if (!$searchableColumns->numRecords()) {
            throw new Exception('there are no full text columns to search');
        }

        $tables = array();
        $tableParameters = array();

        // Make column selection lists
        $pageClass = 'SilverStripe\\CMS\\Model\\SiteTree';
        $fileClass = 'SilverStripe\\Assets\\File';
        $select = array(
            $pageClass => array(
                '"ClassName"',
                '"' . $tablesToSearch[$pageClass] . '"."ID"',
                '"ParentID"',
                '"Title"',
                '"URLSegment"',
                '"Content"',
                '"LastEdited"',
                '"Created"',
                'NULL AS "Name"',
                '"CanViewType"'
            ),
            $fileClass => array(
                '"ClassName"',
                '"' . $tablesToSearch[$fileClass] . '"."ID"',
                '0 AS "ParentID"',
                '"Title"',
                'NULL AS "URLSegment"',
                'NULL AS "Content"',
                '"LastEdited"',
                '"Created"',
                '"Name"',
                'NULL AS "CanViewType"'
            )
        );

        foreach ($searchableColumns as $searchableColumn) {
            $conditions = array();
            $tableName = $searchableColumn['table_name'];
            $columnName = $searchableColumn['column_name'];
            $className = DataObject::getSchema()->tableClass($tableName);
            if (DataObject::getSchema()->fieldSpec($className, 'ShowInSearch')) {
                $conditions[] = array('"ShowInSearch"' => 1);
            }

            $method = self::default_fts_search_method();
            $conditions[] = "\"{$tableName}\".\"{$columnName}\" $method q ";
            $query = DataObject::get($className, $conditions)->dataQuery()->query();

            // Could parameterise this, but convention is only to to so for where conditions
            $query->addFrom(array(
                'tsearch' => ", to_tsquery('" . self::search_language() . "', $keywords) AS q"
            ));
            $query->setSelect(array());

            foreach ($select[$className] as $clause) {
                if (preg_match('/^(.*) +AS +"?([^"]*)"?/i', $clause, $matches)) {
                    $query->selectField($matches[1], $matches[2]);
                } else {
                    $query->selectField($clause);
                }
            }

            $query->selectField("ts_rank(\"{$tableName}\".\"{$columnName}\", q)", 'Relevance');
            $query->setOrderBy(array());

            //Add this query to the collection
            $tables[] = $query->sql($parameters);
            $tableParameters = array_merge($tableParameters, $parameters);
        }

        $limit = $pageLength;
        $offset = $start;

        if ($keywords) {
            $orderBy = " ORDER BY $sortBy";
        } else {
            $orderBy='';
        }

        $fullQuery = "SELECT *, count(*) OVER() as _fullcount FROM (" . implode(" UNION ", $tables) . ") AS q1 $orderBy LIMIT $limit OFFSET $offset";

        // Get records
        $records = $this->preparedQuery($fullQuery, $tableParameters);
        $totalCount = 0;
        $objects = [];
        foreach ($records as $record) {
            $objects[] = Injector::inst()->createWithArgs($record['ClassName'], [$record]);
            $totalCount = $record['_fullcount'];
        }

        if ($objects) {
            $results = new ArrayList($objects);
        } else {
            $results = new ArrayList();
        }
        $list = new PaginatedList($results);
        $list->setLimitItems(false);
        $list->setPageStart($start);
        $list->setPageLength($pageLength);
        $list->setTotalItems($totalCount);
        return $list;
    }