Source of file SearchPageController.php
Size: 13,060 Bytes - Last Modified: 2021-12-24T05:19:43+00:00
/var/www/docs.ssmods.com/process/src/src/SearchPageController.php
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499 | <?php namespace Jaedb\Search; use PageController; use SilverStripe\ORM\ArrayList; use SilverStripe\ORM\PaginatedList; use SilverStripe\ORM\DB; use SilverStripe\Core\Config\Config; use SilverStripe\View\Requirements; use SilverStripe\Control\Director; class SearchPageController extends PageController { // these statics hold the search data private static $query; private static $types; private static $filters; private static $sort; private static $results; public function index($request){ if (Director::isLive()){ Requirements::css('/resources/jaedb/search/client/Search.min.css'); Requirements::javascript('/resources/jaedb/search/client/Search.min.js'); } else { Requirements::css('/resources/jaedb/search/client/Search.css'); Requirements::javascript('/resources/jaedb/search/client/Search.js'); } // get the parameters and variables of this request (ie the query and filters) $vars = $request->requestVars(); if (isset($vars['query']) && $vars['query'] != ''){ self::set_query($vars['query']); unset($vars['query']); } if (isset($vars['types']) && $vars['types'] != ''){ self::set_types(explode(',',$vars['types'])); unset($vars['types']); } if (isset($vars['sort']) && $vars['sort'] != ''){ self::set_sort($vars['sort']); unset($vars['sort']); } self::set_filters($vars); self::set_results($this->PerformSearch()); return []; } /** * Getters **/ public static function get_types_available(){ $types = Config::inst()->get('Jaedb\Search\SearchPageController', 'types'); $array = []; if ($types){ foreach ($types as $key => $value){ $value['Key'] = $key; $array[$key] = $value; } } return $array; } public static function get_filters_available(){ $filters = Config::inst()->get('Jaedb\Search\SearchPageController', 'filters'); $array = []; if ($filters){ foreach ($filters as $key => $value){ $value['Key'] = $key; $array[$key] = $value; } } return $array; } public static function get_sorts_available(){ $sorts = Config::inst()->get('Jaedb\Search\SearchPageController', 'sorts'); $array = []; if ($sorts){ foreach ($sorts as $key => $value){ $value['Key'] = $key; $array[$key] = $value; } } return $array; } public static function get_types(){ return self::$types; } public static function set_types($types){ self::$types = $types; } public static function get_mapped_types(){ $types_available = self::get_types_available(); $mapped_types = []; if ($types = self::get_types()){ foreach (self::get_types() as $key){ if (isset($types_available[$key])){ $mapped_types[] = $types_available[$key]; } } } else { $mapped_types = $types_available; } return $mapped_types; } public static function get_filters(){ return self::$filters; } public static function set_filters($filters){ self::$filters = $filters; } public static function get_mapped_filters(){ $filters_available = self::get_filters_available(); $mapped_filters = []; foreach (self::get_filters() as $key => $value){ if (isset($filters_available[$key])){ $filter = $filters_available[$key]; $filter['Value'] = $value; $mapped_filters[] = $filter; } } return $mapped_filters; } public static function get_query($mysqlSafe = false){ $query = self::$query; if( $mysqlSafe ){ $query = str_replace("'", "\'", $query); $query = str_replace('"', '\"', $query); $query = str_replace('`', '\`', $query); } return $query; } public static function set_query($query = null){ self::$query = $query; } public static function get_sort(){ return self::$sort; } public static function get_mapped_sort(){ $sorts_available = self::get_sorts_available(); $sort = self::get_sort(); // If no sort, assume the first item if (!$sort){ return reset($sorts_available); } else { return $sorts_available[$sort]; } } public static function set_sort($sort){ self::$sort = $sort; } public static function get_results(){ return self::$results; } public static function set_results($results){ self::$results = $results; } /** * Get the search query * This is just an alias to get my static variable * @return ArrayList **/ public function Query(){ return self::get_query(); } /** * Get the results * This is just an alias to get my static variable * @return ArrayList **/ public function Results(){ return self::get_results(); } /** * Get the search query * This is just an alias to get my static variable * @return ArrayList **/ public function Sort(){ return self::get_sort(); } /** * Get the types searched * This is just an alias to get my static variable. We then construct them as ArrayList for template use * @return ArrayList **/ public function Types(){ $types = self::get_types(); $types_available = self::get_types_available(); if (!$types){ return false; } $completeTypes = ArrayList::create(); foreach ($types as $type){ if (isset($types_available[$type])){ $completeTypes->push($types_available[$type]); } } return $completeTypes; } /** * Have a squiz through our site and find all matches * @return PaginatedList **/ public function PerformSearch(){ // get all our search requirements $query = self::get_query($mysqlSafe = true); $types = self::get_mapped_types(); $filters = self::get_mapped_filters(); // prepare our final result object $allResults = ArrayList::create(); // loop all the records we need to lookup foreach ($types as $type){ $sql = ''; $joins = ''; $where = ''; $sort = ''; /** * Result selection * We only need ClassName and ID to fetch the full object (using the SilverStripe ORM) * once we've got our results **/ $sql.= "SELECT \"".$type['Table']."\".\"ID\" AS \"ResultObject_ID\" FROM \"".$type['Table']."\" "; // Join this type with any dependent tables (if applicable) if (isset($type['JoinTables'])){ foreach ($type['JoinTables'] as $joinTable){ $joins.= "LEFT JOIN \"".$joinTable."\" ON \"".$joinTable."\".\"ID\" = \"".$type['Table']."\".\"ID\" "; } } /** * Query term * We search each column for this type for the provided query string */ $where .= ' WHERE ('; foreach ($type['Columns'] as $i => $column){ $column = explode('.',$column); if ($i > 0){ $where .= ' OR '; } $where .= "\"".$column[0]."\".\"".$column[1]."\" LIKE CONCAT('%','".$query."','%')"; } $where.= ')'; /** * Apply our type-level filters (if applicable) **/ if (isset($type['Filters'])){ foreach ($type['Filters'] as $key => $value){ $where.= ' AND ('.$key.' = '.$value.')'; } } /** * Apply filtering **/ $relations_sql = ''; if ($filters){ foreach ($filters as $filter){ // Apply filters, based on filter structure switch ($filter['Structure']){ /** * Simple column value filter **/ case 'db': // Identify which table has the column which we're trying to filter by $table_with_column = null; if (isset($type['JoinTables'])){ $tables_to_check = $type['JoinTables']; } else { $tables_to_check = []; } $tables_to_check[] = $type['Table']; foreach ($tables_to_check as $table_to_check){ $column_exists_query = DB::query( "SHOW COLUMNS FROM \"".$table_to_check."\" LIKE '".$filter['Column']."'" ); foreach ($column_exists_query as $column){ $table_with_column = $table_to_check; } } // Not anywhere in this type's table joins, so we can't search this particular type if (!$table_with_column){ continue 2; } // open our wrapper $where.= ' AND ('; /** * This particular type needs to join with other parent tables to * form a complete, and searchable row **/ if (isset($type['JoinTables'])){ foreach ($type['JoinTables'] as $join_table){ //$joins.= "LEFT JOIN \"".$type['Table']."\" ON \"".$join_table."\".\"ID\" = \"".$type['Table']."\".\"ID\""; } } if (is_array($filter['Value'])){ $valuesString = ''; foreach ($filter['Value'] as $value){ if ($valuesString != ''){ $valuesString.= ','; } $valuesString.= "'".$value."'"; } } else { $valuesString = $filter['Value']; } $where.= "\"".$table_with_column."\".\"".$filter['Column']."\" ".$filter['Operator']." '".$valuesString ."'"; // close our wrapper $where.= ')'; break; /** * Simple relational filter (ie Page.Author) **/ case 'has_one': // Identify which table has the column which we're trying to filter by $table_with_column = null; if (isset($type['JoinTables'])){ $tables_to_check = $type['JoinTables']; } else { $tables_to_check = []; } $tables_to_check[] = $type['Table']; foreach ($tables_to_check as $table_to_check){ $column_exists_query = DB::query( "SHOW COLUMNS FROM \"".$table_to_check."\" LIKE '".$filter['Column']."'" ); foreach ($column_exists_query as $column){ $table_with_column = $table_to_check; } } // Not anywhere in this type's table joins, so we can't search this particular type if (!$table_with_column){ continue 2; } // join the relationship table to our record(s) $joins.= "LEFT JOIN \"".$filter['Table']."\" ON \"".$filter['Table']."\".\"ID\" = \"".$table_with_column."\".\"".$filter['Column']."\""; if (is_array($filter['Value'])){ $ids = ''; foreach ($filter['Value'] as $id){ if ($ids != ''){ $ids.= ','; } $ids.= "'".$id."'"; } } else { $ids = $filter['Value']; } $where.= ' AND ('."\"".$table_with_column."\".\"".$filter['Column']."\" IN (". $ids .")".')'; break; /** * Complex relational filter (ie Page.Tags) **/ case 'many_many': // Make sure this type has a relationship to this filter object if (isset($filter['JoinTables'][$type['Key']])){ $filter_join = $filter['JoinTables'][$type['Key']]; $joins.= "LEFT JOIN \"".$filter_join['Table']."\" ON \"".$type['Table']."\".\"ID\" = \"".$filter_join['Column']."\""; if (is_array($filter['Value'])){ $ids = ''; foreach ($filter['Value'] as $id){ if ($ids != ''){ $ids.= ','; } $ids.= "'".$id."'"; } } else { $ids = $filter['Value']; } $relations_sql.= "\"".$filter_join['Table']."\".\"".$filter['Table']."ID\" IN (". $ids .")"; } break; } } // Append any required relations SQL if ($relations_sql !== ''){ $where.= ' AND ('.$relations_sql.')'; } } // Compile our sql string $sql.= $joins; $sql.= $where; // Debugging //echo '<h3 style="position: relative; padding: 20px; background: #EEEEEE; z-index: 999;">'.$sql.'</h3>'; // Eexecutioner enter stage left $results = DB::query($sql); $resultIDs = array(); // Add all the result ids to our array foreach ($results as $result){ // Make sure we're not already a result if (!isset($resultIDs[$result['ResultObject_ID']])){ $resultIDs[$result['ResultObject_ID']] = $result['ResultObject_ID']; } } // Convert our SQL results into SilverStripe objects of the appropriate class if ($resultIDs){ $resultObjects = $type['ClassName']::get()->filter(['ID' => $resultIDs]); $allResults->merge($resultObjects); } } // Apply sorting $sort = self::get_mapped_sort()['Sort']; $sort = str_replace("'", "\'", $sort); $sort = str_replace('"', '\"', $sort); $sort = str_replace('`', '\`', $sort); $allResults = $allResults->Sort($sort); // Remove duplicates //$allResults->removeDuplicates('ID'); // load into a paginated list. To change the items per page, set via the template (ie Results.setPageLength(20)) $paginatedItems = PaginatedList::create($allResults, $this->request); return $paginatedItems; } } |