search_special()
Description
Processes special searches and constructs a corresponding SQL query.This function handles various special search commands (like viewing the last resources,
resources with no downloads, duplicates, collections, etc.) and creates a prepared statement
for the query that retrieves the desired resources based on the search parameters.
It also incorporates user permissions and other configurations into the search logic.
Parameters
| Column | Type | Default | Description |
|---|---|---|---|
| $search | string | The search string indicating the type of special search. | |
| $sql_join | PreparedStatementQuery | The SQL JOIN query to be applied. | |
| $fetchrows | int | The number of rows to fetch. | |
| $sql_prefix | string | The prefix for the SQL query. | |
| $sql_suffix | string | The suffix for the SQL query. | |
| $order_by | string | The order by clause for sorting the results. | |
| $orig_order | string | The original order specified by the user. | |
| $select | string | The fields to select in the query. | |
| $sql_filter | PreparedStatementQuery | The SQL WHERE filter to apply. | |
| $archive | mixed | Archive states to filter by. | |
| $return_disk_usage | bool | Indicates whether to return disk usage information. | |
| $return_refs_only | bool | false | If true, returns only resource references. |
| $returnsql | bool | false | If true, returns the constructed SQL query instead of executing it. |
Return
| mixed | The results of the special search or false if no special search was matched. |
Location
include/search_functions.php lines 1175 to 1812
Definition
function search_special($search, $sql_join, $fetchrows, $sql_prefix, $sql_suffix, $order_by, $orig_order, $select, $sql_filter, $archive, $return_disk_usage, $return_refs_only = false, $returnsql = false)
{
# Process special searches. These return early with results.
global $FIXED_LIST_FIELD_TYPES, $lang, $k, $USER_SELECTION_COLLECTION, $date_field;
global $allow_smart_collections, $smart_collections_async;
global $config_search_for_number,$userref;
setup_search_chunks($fetchrows, $chunk_offset, $search_chunk_size);
// Don't cache special searches by default as often used for special purposes
// e.g. collection count to determine edit accesss
$b_cache_count = false;
if (!is_a($sql_join, "PreparedStatementQuery") && trim($sql_join == "")) {
$sql_join = new PreparedStatementQuery();
}
if (!is_a($sql_filter, "PreparedStatementQuery") && trim($sql_filter == "")) {
$sql_filter = new PreparedStatementQuery();
}
$sql = new PreparedStatementQuery();
# View Last
if (substr($search, 0, 5) == "!last") {
# Replace r2.ref with r.ref for the alternative query used here.
$order_by = str_replace("r.ref", "r2.ref", $order_by);
if ($orig_order == "relevance") {
# Special case for ordering by relevance for this query.
$direction = ((strpos($order_by, "DESC") === false) ? "ASC" : "DESC");
$order_by = "r2.ref " . $direction;
}
# add date field, if access allowed, for use in $order_by
if (metadata_field_view_access($date_field) && strpos($select, "field" . $date_field) === false) {
$select .= ", field{$date_field} ";
}
# Extract the number of records to produce
$last = explode(",", $search);
$last = str_replace("!last", "", $last[0]);
# !Last must be followed by an integer. SQL injection filter.
if (is_int_loose($last)) {
$last = (int)$last;
} else {
$last = 1000;
$search = "!last1000";
}
# Fix the ORDER BY for this query (special case due to inner query)
$order_by = str_replace("r.rating", "rating", $order_by);
$sql->sql = $sql_prefix . "SELECT DISTINCT *,r2.total_hit_count score FROM (SELECT $select FROM resource r " . $sql_join->sql . " WHERE " . $sql_filter->sql . " ORDER BY ref DESC LIMIT $last ) r2 ORDER BY $order_by" . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
}
// View Resources With No Downloads
elseif (substr($search, 0, 12) == "!nodownloads") {
if ($orig_order == "relevance") {
$order_by = "ref DESC";
}
$sql->sql = $sql_prefix . "SELECT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE " . $sql_filter->sql . " AND r.ref NOT IN (SELECT DISTINCT object_ref FROM daily_stat WHERE activity_type='Resource download') GROUP BY r.ref ORDER BY $order_by" . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
}
// Duplicate Resources (based on file_checksum)
elseif (substr($search, 0, 11) == "!duplicates") {
// Extract the resource ID
$ref = explode(" ", $search);
$ref = str_replace("!duplicates", "", $ref[0]);
$ref = explode(",", $ref); // just get the number
$ref = $ref[0];
if ($ref != "") {
# Find duplicates of a given resource
if (is_int_loose($ref)) {
$sql->sql = sprintf(
"SELECT DISTINCT r.hit_count score, %s
FROM resource r %s
WHERE %s
AND file_checksum <> ''
AND file_checksum IS NOT NULL
AND file_checksum = (
SELECT file_checksum
FROM resource
WHERE ref= ?
AND (file_checksum <> '' AND file_checksum IS NOT NULL)
)
GROUP BY r.ref
ORDER BY file_checksum, ref",
$select,
$sql_join->sql,
$sql_filter->sql
);
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters, ["i",$ref]);
} else {
# Given resource is not a valid identifier
return array();
}
} else {
# Find all duplicate resources
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE " . $sql_filter->sql . " AND file_checksum IN (SELECT file_checksum FROM (SELECT file_checksum FROM resource WHERE file_checksum <> '' AND file_checksum IS NOT null GROUP BY file_checksum having count(file_checksum)>1)r2) ORDER BY file_checksum, ref" . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
}
}
# View Collection
elseif (substr($search, 0, 11) == '!collection') {
global $userref,$ignore_collection_access;
$colcustperm = $sql_join;
# Extract the collection number
$collection = explode(' ', $search);
$collection = str_replace('!collection', '', $collection[0]);
$collection = explode(',', $collection); // just get the number
$collection = (int)$collection[0];
if (!checkperm('a')) {
# Check access
$validcollections = [];
if (upload_share_active() !== false) {
$validcollections = get_session_collections(get_rs_session_id(), $userref);
} else {
$user_collections = array_column(get_user_collections($userref, "", "name", "ASC", -1, false), "ref");
$public_collections = array_column(search_public_collections('', 'name', 'ASC', true, false), 'ref');
# include collections of requested resources
$request_collections = array();
if (checkperm("R")) {
include_once 'request_functions.php';
$request_collections = array_column(get_requests(), 'collection');
}
# include collections of research resources
$research_collections = array();
if (checkperm("r")) {
include_once 'research_functions.php';
$research_collections = array_column(get_research_requests(), 'collection');
}
$validcollections = array_unique(array_merge($user_collections, array($USER_SELECTION_COLLECTION), $public_collections, $request_collections, $research_collections));
}
// Attach the negated user reference special collection
$validcollections[] = (0 - $userref);
if (in_array($collection, $validcollections) || (in_array($collection, array_column(get_all_featured_collections(), 'ref')) && featured_collection_check_access_control($collection)) || $ignore_collection_access) {
if (!collection_readable($collection)) {
return array();
}
} elseif ($k == "" || upload_share_active() !== false) {
return [];
}
}
if ($allow_smart_collections) {
global $smartsearch_ref_cache;
if (isset($smartsearch_ref_cache[$collection])) {
$smartsearch_ref = $smartsearch_ref_cache[$collection]; // this value is pretty much constant
} else {
$smartsearch_ref = ps_value('SELECT savedsearch value FROM collection WHERE ref = ?', ['i',$collection], '');
$smartsearch_ref_cache[$collection] = $smartsearch_ref;
}
global $php_path;
if ($smartsearch_ref != '' && !$return_disk_usage) {
if ($smart_collections_async && isset($php_path) && file_exists($php_path . '/php')) {
exec($php_path . '/php ' . dirname(__FILE__) . '/../pages/ajax/update_smart_collection.php ' . escapeshellarg($smartsearch_ref) . ' ' . '> /dev/null 2>&1 &');
} else {
update_smart_collection($smartsearch_ref);
}
}
}
$sql->sql = $sql_prefix . "SELECT DISTINCT c.date_added,c.comment,r.hit_count score,length(c.comment) commentset, $select FROM resource r join collection_resource c on r.ref=c.resource " . $colcustperm->sql . " WHERE c.collection = ? AND (" . $sql_filter->sql . ") GROUP BY r.ref ORDER BY $order_by" . $sql_suffix;
$sql->parameters = array_merge($colcustperm->parameters, ["i",$collection], $sql_filter->parameters);
$collectionsearchsql = hook('modifycollectionsearchsql', '', array($sql));
if ($collectionsearchsql) {
$sql = $collectionsearchsql;
}
}
# View Related - Pushed Metadata (for the view page)
elseif (substr($search, 0, 14) == "!relatedpushed") {
# Extract the resource number
$resource = explode(" ", $search);
$resource = str_replace("!relatedpushed", "", $resource[0]);
if (isset($GLOBALS["related_pushed_order_by"])) {
$related_order = is_int_loose($GLOBALS["related_pushed_order_by"]) ? "field" . $GLOBALS["related_pushed_order_by"] : $GLOBALS["related_pushed_order_by"];
$order_by = set_search_order_by($search, $related_order, "ASC");
}
$order_by = str_replace("r.", "", $order_by); # UNION below doesn't like table aliases in the ORDER BY.
$relatedselect = $sql_prefix . "
SELECT DISTINCT r.hit_count score,rt.name resource_type_name, $select
FROM resource r
JOIN resource_type rt ON r.resource_type=rt.ref AND rt.push_metadata=1
JOIN resource_related t ON (%s) "
. $sql_join->sql
. " WHERE 1=1 AND " . $sql_filter->sql
. " GROUP BY r.ref";
$sql->sql = sprintf($relatedselect, "t.related=r.ref AND t.resource = ?")
. " UNION "
. sprintf($relatedselect, "t.resource=r.ref AND t.related= ?")
. " ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge(
["i",$resource],
$sql_join->parameters,
$sql_filter->parameters,
["i",$resource],
$sql_join->parameters,
$sql_filter->parameters
);
}
# View Related
elseif (substr($search, 0, 8) == "!related") {
# Extract the resource number
$resource = explode(" ", $search);
$resource = str_replace("!related", "", $resource[0]);
$order_by = str_replace("r.", "", $order_by); # UNION below doesn't like table aliases in the ORDER BY.
global $pagename, $related_search_show_self;
$sql_self = new PreparedStatementQuery();
if ($related_search_show_self && $pagename == 'search') {
$sql_self->sql = " SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE r.ref = ? AND " . $sql_filter->sql . " GROUP BY r.ref UNION ";
$sql_self->parameters = array_merge($sql_join->parameters, ["i",$resource], $sql_filter->parameters);
}
$sql->sql = $sql_prefix . $sql_self->sql . "SELECT DISTINCT r.hit_count score, $select FROM resource r join resource_related t on (t.related=r.ref AND t.resource = ?) " . $sql_join->sql . " WHERE " . $sql_filter->sql . " GROUP BY r.ref
UNION
SELECT DISTINCT r.hit_count score, $select FROM resource r join resource_related t on (t.resource=r.ref AND t.related = ?) " . $sql_join->sql . " WHERE " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_self->parameters, ["i", $resource], $sql_join->parameters, $sql_filter->parameters, ["i", $resource], $sql_join->parameters, $sql_filter->parameters);
}
# Geographic search
elseif (substr($search, 0, 4) == "!geo") {
$geo = explode("t", str_replace(array("m","p"), array("-","."), substr($search, 4))); # Specially encoded string to avoid keyword splitting
if (!isset($geo[0]) || empty($geo[0]) || !isset($geo[1]) || empty($geo[1])) {
exit($lang["geographicsearchmissing"]);
}
$bl = explode("b", $geo[0]);
$tr = explode("b", $geo[1]);
$sql->sql = "SELECT r.hit_count score, " . $select .
" FROM resource r " . $sql_join->sql .
"WHERE geo_lat > ? AND geo_lat < ? " .
"AND geo_long > ? AND geo_long < ?
AND " . $sql_filter->sql .
" GROUP BY r.ref
ORDER BY $order_by";
$sql->parameters = array_merge($sql_join->parameters, ["d",$bl[0],"d",$tr[0],"d",$bl[1],"d",$tr[1]], $sql_filter->parameters);
$sql->sql = $sql_prefix . $sql->sql . $sql_suffix;
}
# Similar to a colour by key
elseif (substr($search, 0, 10) == "!colourkey") {
# Extract the colour key
$colourkey = explode(" ", $search);
$colourkey = str_replace("!colourkey", "", $colourkey[0]);
$sql = new PreparedStatementQuery();
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE has_image > 0 AND LEFT(colour_key,4) = ? AND " . $sql_filter->sql . " GROUP BY r.ref" . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, ["s",$colourkey], $sql_filter->parameters);
}
# Colour search
elseif (substr($search, 0, 7) == "!colour") {
$colour = explode(" ", $search);
$colour = str_replace("!colour", "", $colour[0]);
$sql = new PreparedStatementQuery();
$sql->sql = "SELECT r.hit_count score, " . $select .
" FROM resource r " . $sql_join->sql .
" WHERE colour_key LIKE ? " .
"OR colour_key LIKE ? " .
"AND " . $sql_filter->sql .
" GROUP BY r.ref
ORDER BY " . $order_by;
$sql->parameters = array_merge($sql_join->parameters, ["s",$colour . "%","s","_" . $colour . "%"], $sql_filter->parameters);
$searchsql = $sql_prefix . $sql->sql . $sql_suffix;
$sql->sql = $searchsql;
}
# Similar to a colour
elseif (substr($search, 0, 4) == "!rgb") {
$rgb = explode(":", $search);
$rgb = explode(",", $rgb[1]);
$searchsql = new PreparedStatementQuery();
$searchsql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE has_image > 0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY (abs(image_red - ?)+abs(image_green - ?)+abs(image_blue - ?)) ASC LIMIT 500" . $sql_suffix;
$searchsql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters, ["i",$rgb[0],"i",$rgb[1],"i",$rgb[2]]);
$sql = $searchsql;
} elseif (substr($search, 0, 10) == "!nopreview") {
$sql = new PreparedStatementQuery();
$sql->sql = $sql_prefix .
"SELECT DISTINCT r.hit_count score, $select
FROM resource r
$sql_join->sql
WHERE has_image=0
AND $sql_filter->sql
GROUP BY r.ref
ORDER BY $order_by"
. $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
} elseif (($config_search_for_number && is_numeric($search)) || substr($search, 0, 9) == "!resource") {
$searchref = preg_replace("/[^0-9]/", "", $search);
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE r.ref = ? AND " . $sql_filter->sql . " GROUP BY r.ref" . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, ["i",$searchref], $sql_filter->parameters);
} elseif (substr($search, 0, 15) == "!archivepending") {
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE r.archive=1 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
} elseif (substr($search, 0, 12) == "!userpending") {
if ($orig_order == "rating") {
$order_by = "request_count DESC," . $order_by;
}
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE r.archive=-1
AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
} elseif (substr($search, 0, 14) == "!contributions") {
global $userref;
# Extract the user ref
$cuser = explode(" ", $search);
$cuser = str_replace("!contributions", "", $cuser[0]);
// Don't filter if user is searching for their own resources and $open_access_for_contributor=true;
global $open_access_for_contributor;
if ($open_access_for_contributor && $userref == $cuser) {
$sql_filter->sql = "archive IN (" . ps_param_insert(count($archive)) . ")";
$sql_filter->parameters = ps_param_fill($archive, "i");
$sql_join->sql = " JOIN resource_type AS rty ON r.resource_type = rty.ref ";
$sql_join->parameters = array();
// Remove reference to custom access
$select = str_replace(["rca.access", "rca2.access"], "0", $select);
}
$select = str_replace(",rca.access group_access,rca2.access user_access ", ",null group_access, null user_access ", $select);
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE created_by = ? AND r.ref > 0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, ["i", $cuser], $sql_filter->parameters);
} elseif ($search == "!images") {
// Search for resources with images
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE has_image>0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
} elseif (substr($search, 0, 7) == "!unused") {
// Search for resources not used in any collections
$sql->sql = $sql_prefix;
$sql->sql .= sprintf(
"SELECT DISTINCT %s
FROM resource r %s
WHERE r.ref>0
AND r.ref NOT IN (SELECT c.resource FROM collection_resource c)
AND %s
GROUP BY r.ref",
$select,
$sql_join->sql,
$sql_filter->sql
);
$sql->sql .= $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
$order_by = '';
} elseif (substr($search, 0, 5) == "!list") {
// Search for a list of resources
// !listall = archive state is not applied as a filter to the list of resources.
$resources = explode(" ", $search);
if (substr($search, 0, 8) == "!listall") {
$resources = str_replace("!listall", "", $resources[0]);
} else {
$resources = str_replace("!list", "", $resources[0]);
}
$resources = explode(",", $resources); // Separate out any additional keywords
$resources = array_filter(explode(":", $resources[0]), "is_int_loose");
$listsql = new PreparedStatementQuery();
if (count($resources) == 0) {
$listsql->sql = " WHERE r.ref IS NULL";
$listsql->parameters = [];
} else {
$listsql->sql = " WHERE r.ref IN (" . ps_param_insert(count($resources)) . ")";
$listsql->parameters = ps_param_fill($resources, "i");
}
$sql->sql = $sql_prefix;
$sql->sql .= sprintf(
"SELECT DISTINCT r.hit_count score, %s
FROM resource r %s%s
AND %s
GROUP BY r.ref
ORDER BY %s",
$select,
$sql_join->sql,
$listsql->sql,
$sql_filter->sql,
$order_by
);
$sql->sql .= $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $listsql->parameters, $sql_filter->parameters);
} elseif (substr($search, 0, 8) == "!hasdata") {
// View resources that have data in the specified field reference - useful if deleting unused fields
$fieldref = intval(trim(substr($search, 8)));
$sql_join->sql .= " RIGHT JOIN resource_node rn ON r.ref=rn.resource JOIN node n ON n.ref=rn.node WHERE n.resource_type_field = ?";
array_push($sql_join->parameters, "i", $fieldref);
// Cache this as it is a very slow query
$b_cache_count = true;
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
} elseif (substr($search, 0, 11) == "!properties") {
// Search for resource properties
// Note: in order to combine special searches with normal searches, these are separated by space (" ")
$searches_array = explode(' ', $search);
$properties = explode(';', substr($searches_array[0], 11));
// Use a new variable to ensure nothing changes $sql_filter unless this is a valid property search
$propertiessql = new PreparedStatementQuery();
foreach ($properties as $property) {
$propertycheck = explode(":", $property);
if (count($propertycheck) == 2) {
$propertyname = $propertycheck[0];
$propertyval = $propertycheck[1];
$sql_filter_properties_and = $propertiessql->sql != "" ? " AND " : "";
switch ($propertyname) {
case "hmin":
$propertiessql->sql .= $sql_filter_properties_and . " rdim.height >= ?";
array_push($propertiessql->parameters, "i", $propertyval);
break;
case "hmax":
$propertiessql->sql .= $sql_filter_properties_and . " rdim.height <= ?";
array_push($propertiessql->parameters, "i", $propertyval);
break;
case "wmin":
$propertiessql->sql .= $sql_filter_properties_and . " rdim.width >= ?";
array_push($propertiessql->parameters, "i", $propertyval);
break;
case "wmax":
$propertiessql->sql .= $sql_filter_properties_and . " rdim.width <= ?";
array_push($propertiessql->parameters, "i", $propertyval);
break;
case "fmin":
// Need to convert MB value to bytes
$propertiessql->sql .= $sql_filter_properties_and . " r.file_size >= ?";
array_push($propertiessql->parameters, "i", floatval($propertyval) * 1024 * 1024);
break;
case "fmax":
// Need to convert MB value to bytes
$propertiessql->sql .= $sql_filter_properties_and . " r.file_size <= ?";
array_push($propertiessql->parameters, "i", floatval($propertyval) * 1024 * 1024);
break;
case "fext":
$propertyval = str_replace("*", "%", $propertyval);
$propertiessql->sql .= $sql_filter_properties_and . " r.file_extension ";
if (substr($propertyval, 0, 1) == "-") {
$propertyval = substr($propertyval, 1);
$propertiessql->sql .= " NOT ";
}
if (substr($propertyval, 0, 1) == ".") {
$propertyval = substr($propertyval, 1);
}
$propertiessql->sql .= " LIKE ?";
array_push($propertiessql->parameters, "s", $propertyval);
break;
case "pi":
$propertiessql->sql .= $sql_filter_properties_and . " r.has_image = ?";
array_push($propertiessql->parameters, "i", $propertyval);
break;
case "cu":
$propertiessql->sql .= $sql_filter_properties_and . " r.created_by = ?";
array_push($propertiessql->parameters, "i", $propertyval);
break;
case "orientation":
$orientation_filters = array(
"portrait" => "COALESCE(rdim.height, 0) > COALESCE(rdim.width, 0)",
"landscape" => "COALESCE(rdim.height, 0) < COALESCE(rdim.width, 0)",
"square" => "COALESCE(rdim.height, 0) = COALESCE(rdim.width, 0)",
);
if (!in_array($propertyval, array_keys($orientation_filters))) {
break;
}
$propertiessql->sql .= $sql_filter_properties_and . $orientation_filters[$propertyval];
break;
}
}
}
if ($propertiessql->sql != "") {
if (strpos($sql_join->sql, "LEFT JOIN resource_dimensions rdim on r.ref=rdim.resource") === false) {
$sql_join->sql .= " LEFT JOIN resource_dimensions rdim on r.ref=rdim.resource";
}
if ($sql_filter->sql == "") {
$sql_filter->sql .= " WHERE " . $propertiessql->sql;
} else {
$sql_filter-> sql .= " AND " . $propertiessql->sql;
}
$sql_filter->parameters = array_merge($sql_filter->parameters, $propertiessql->parameters);
}
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE r.ref > 0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
} elseif ($search == "!integrityfail") {
// Search for resources where the file integrity has been marked as problematic or the file is missing
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE integrity_fail=1 AND no_file=0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
}
# Search for locked resources
elseif ($search == "!locked") {
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE lock_user<>0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
} elseif (preg_match('/^!report(\d+)(p[-1\d]+)?(d\d+)?(fy\d{4})?(fm\d{2})?(fd\d{2})?(ty\d{4})?(tm\d{2})?(td\d{2})?/i', $search, $report_search_data)) {
/*
View report as search results.
Special search "!report" can contain extra info for the reports' query period.
Syntax: !reportID[p?][d??][fy????][fm??][fd??][ty????][tm??][td??]
Where:
- ID is the actual report ref (mandatory)
- p is the selected period (see $reporting_periods_default config option)
- d is the period in specific number of days (p=0 in this case)
- fy,fm,fd (and their counter parts: ty,tm,td) represent a full date range (p=-1 in this case)
Examples for viewing as search results report #18:
- Last 7 days: !report18p7
- Last 23 days: !report18p0d23
- Between 2000-01-06 & 2023-03-16: !report18p-1fy2000fm01fd06ty2023tm03td16
*/
debug('[search_special] Running a "!report" search...');
$no_results_sql = new PreparedStatementQuery(
$sql_prefix . "SELECT DISTINCT r.hit_count score, {$select} FROM resource AS r "
. $sql_join->sql
. ' WHERE 1 = 2 AND ' . $sql_filter->sql
. ' GROUP BY r.ref ORDER BY ' . $order_by,
array_merge($sql_join->parameters, $sql_filter->parameters)
);
// Users with no access control to reports get no results back (ie []).
if (!checkperm('t')) {
debug(sprintf('[WARNING][search_special][access control] User #%s attempted to run "%s" search without the right permissions', (int) $userref, $search));
$sql = $no_results_sql;
} else {
include_once 'reporting_functions.php';
$report_id = $report_search_data[1];
$all_reports = get_reports();
$reports_w_thumbnail = array_filter(array_column($all_reports, 'query', 'ref'), 'report_has_thumbnail');
$reports_w_support_non_correlated_sql = array_filter(array_column($all_reports, 'support_non_correlated_sql', 'ref'));
$reports = array_diff_key($reports_w_thumbnail, $reports_w_support_non_correlated_sql);
if (isset($reports[$report_id])) {
$report = $reports[$report_id];
$report_period = [];
$report_period_info_idxs = range(2, 9);
$report_period_info_names = array_combine($report_period_info_idxs, ['period', 'period_days', 'from-y', 'from-m', 'from-d', 'to-y', 'to-m', 'to-d']);
$report_period_info_lookups = array_combine($report_period_info_idxs, ['p', 'd', 'fy', 'fm', 'fd', 'ty', 'tm', 'td']);
foreach ($report_period_info_names as $idx => $info_name) {
if (!isset($report_search_data[$idx])) {
continue;
}
$report_period[$info_name] = str_replace($report_period_info_lookups[$idx], '', $report_search_data[$idx]);
}
$period = report_process_period($report_period);
$report_sql = report_process_query_placeholders($report, [
'[from-y]' => $period['from_year'],
'[from-m]' => $period['from_month'],
'[from-d]' => $period['from_day'],
'[to-y]' => $period['to_year'],
'[to-m]' => $period['to_month'],
'[to-d]' => $period['to_day'],
]);
$report_sql = preg_replace('/;\s?/m', '', $report_sql, 1);
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource AS r"
. " INNER JOIN ($report_sql) AS rsr ON rsr.thumbnail = r.ref "
. $sql_join->sql
. ' WHERE ' . $sql_filter->sql
. ' GROUP BY r.ref ORDER BY ' . $order_by
. $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
debug("[search_special] SQL = " . json_encode($sql));
} else {
debug("[search_special] Report #{$report_id} not found");
$sql = $no_results_sql;
}
}
}
# Within this hook implementation, set the value of the global $sql variable:
# Since there will only be one special search executed at a time, only one of the
# hook implementations will set the value. So, you know that the value set
# will always be the correct one (unless two plugins use the same !<type> value).
$hooksql = hook("addspecialsearch", "", array($search, $select, $sql_join , $sql_filter, $sql));
if (is_a($hooksql, 'PreparedStatementQuery')) {
debug("Addspecialsearch hook returned useful results.");
$hooksql->sql = $sql_prefix . $hooksql->sql . $sql_suffix;
$sql = $hooksql;
}
if ($sql->sql != "") {
if ($returnsql) {
return $sql;
} else {
if ($return_refs_only) {
// Reformat order by as the new derived table wont have the same alias as the original query
$order_by = implode(',', array_map(function ($field) {
$field = trim($field);
if(strpos($field, '.') !== false) {
$field = substr($field, strpos($field, '.') + 1);
}
return $field;
}, explode(',', $order_by)));
$sql->sql = "SELECT ref, resource_type, archive, created_by, access FROM ($sql->sql) as refs_only";
$sql->sql .= trim($order_by) !== '' ? " ORDER BY $order_by" : "";
}
$count_sql = clone $sql;
$count_sql->sql = str_replace("ORDER BY " . $order_by, "", $count_sql->sql);
$result = sql_limit_with_total_count($sql, $search_chunk_size, $chunk_offset, $b_cache_count, $count_sql);
if (is_array($fetchrows)) {
return $result;
}
$resultcount = $result["total"] ?? 0;
if ($resultcount > 0 && count($result["data"]) > 0) {
$return = $result['data'];
$resultcount -= count($return);
while ($resultcount > 0) {
$return = array_merge($return, array_pad([], ($resultcount > 1000000 ? 1000000 : $resultcount), 0));
$resultcount -= 1000000;
}
} else {
$return = [];
}
}
hook('beforereturnresults', '', array($result, $archive));
return $return;
}
# Arrived here? There were no special searches. Return false.
return false;
}
This article was last updated 11th February 2025 19:05 Europe/London time based on the source file dated 10th February 2025 15:05 Europe/London time.