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 | PreparedStatementQuery | 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 1876
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->sql, "field" . $date_field) === false) {
$select->sql .= ", 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_SQL] FROM resource r " . $sql_join->sql . " WHERE " . $sql_filter->sql . " ORDER BY ref DESC LIMIT $last ) r2 [ORDER_BY_SQL] " . $sql_suffix;
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters);
} elseif (substr($search, 0, 12) == "!nodownloads") {
// View Resources With No Downloads
if ($orig_order == "relevance") {
$order_by = "ref DESC";
}
$select->sql = "r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] 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_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters);
} elseif (substr($search, 0, 11) == "!duplicates") {
// Duplicate Resources (based on file_checksum)
// 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 [SELECT_SQL]
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_SQL]
[ORDER_BY_SQL]",
$sql_join->sql,
$sql_filter->sql
);
$order_by = "file_checksum, ref";
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters, ["i",$ref]);
} else {
// Given resource is not a valid identifier
return [];
}
} else {
// Find all duplicate resources
$order_by = "file_checksum, ref";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] 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_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters);
}
$select->sql = "r.hit_count score, {$select->sql}";
} elseif (substr($search, 0, 11) == '!collection') {
# View 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');
$externally_requested_collections = array_column(ps_query('SELECT ref FROM collection WHERE user = -2'), 'ref');
$request_collections = array_merge($request_collections, $externally_requested_collections);
}
# 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);
}
}
}
$select->sql = "DISTINCT c.date_added,c.comment,r.hit_count score,length(c.comment) commentset, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r JOIN collection_resource c ON r.ref=c.resource " . $colcustperm->sql . " WHERE c.collection = ? AND (" . $sql_filter->sql . ") [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $colcustperm->parameters, ["i",$collection], $sql_filter->parameters);
$collectionsearchsql = hook('modifycollectionsearchsql', '', array($sql));
if ($collectionsearchsql) {
$sql = $collectionsearchsql;
}
} elseif (substr($search, 0, 14) == "!relatedpushed") {
# View Related - Pushed Metadata (for the view page)
# Extract the resource number
$resource = explode(" ", $search);
$resource = str_replace("!relatedpushed", "", $resource[0]);
if (isset($GLOBALS["related_pushed_order_by"])) {
if (is_int_loose($GLOBALS["related_pushed_order_by"])) {
if (metadata_field_view_access($GLOBALS["related_pushed_order_by"])) {
$order_by = set_search_order_by($search, "field" . $GLOBALS["related_pushed_order_by"], "ASC");
}
} else {
$order_by = set_search_order_by($search, $GLOBALS["related_pushed_order_by"], "ASC");
}
}
$order_by = str_replace("r.", "", $order_by); # UNION below doesn't like table aliases in the ORDER BY.
$select->sql = "DISTINCT r.hit_count score, rt.name resource_type_name, {$select->sql}";
$relatedselect = $sql_prefix . "
SELECT [SELECT_SQL]
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_SQL]";
$sql->sql = sprintf($relatedselect, "t.related=r.ref AND t.resource = ?")
. " UNION "
. sprintf($relatedselect, "t.resource=r.ref AND t.related= ?")
. " [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge(
$select->parameters,
["i",$resource],
$sql_join->parameters,
$sql_filter->parameters,
$select->parameters,
["i",$resource],
$sql_join->parameters,
$sql_filter->parameters
);
} elseif (substr($search, 0, 8) == "!related") {
# View 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();
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
if ($related_search_show_self && ($pagename == 'search' || $pagename == 'collections')) {
$sql_self->sql = " SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE r.ref = ? AND " . $sql_filter->sql . " GROUP BY r.ref UNION ";
$sql_self->parameters = array_merge($select->parameters, $sql_join->parameters, ["i",$resource], $sql_filter->parameters);
}
$sql->sql = $sql_prefix . $sql_self->sql . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " JOIN resource_related t ON (t.related = r.ref AND t.resource = ?) WHERE " . $sql_filter->sql . " [GROUP_BY_SQL]
UNION
SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " JOIN resource_related t ON (t.resource = r.ref AND t.related = ?) WHERE " . $sql_filter->sql . " [GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($sql_self->parameters, $select->parameters, $sql_join->parameters, ["i", $resource], $sql_filter->parameters, $select->parameters, $sql_join->parameters, ["i", $resource], $sql_filter->parameters);
} elseif (substr($search, 0, 4) == "!geo") {
# Geographic search
$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]);
$select->sql = "r.hit_count score, {$select->sql}";
$sql->sql = "SELECT [SELECT_SQL]
FROM resource r " . $sql_join->sql .
"WHERE geo_lat > ? AND geo_lat < ? " .
"AND geo_long > ? AND geo_long < ?
AND " . $sql_filter->sql .
" [GROUP_BY_SQL] [ORDER_BY_SQL]";
$sql->parameters = array_merge($select->parameters, $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;
} elseif (substr($search, 0, 10) == "!colourkey") {
# Similar to a colour by key
# Extract the colour key
$colourkey = explode(" ", $search);
$colourkey = str_replace("!colourkey", "", $colourkey[0]);
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql = new PreparedStatementQuery();
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE has_image > 0 AND LEFT(colour_key,4) = ? AND " . $sql_filter->sql . " [GROUP_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, ["s",$colourkey], $sql_filter->parameters);
} elseif (substr($search, 0, 7) == "!colour") {
# Colour search
$colour = explode(" ", $search);
$colour = str_replace("!colour", "", $colour[0]);
$select->sql = "r.hit_count score, {$select->sql}";
$sql = new PreparedStatementQuery();
$sql->sql = "SELECT [SELECT_SQL]
FROM resource r " . $sql_join->sql .
" WHERE colour_key LIKE ? " .
"OR colour_key LIKE ? " .
"AND " . $sql_filter->sql .
" [GROUP_BY_SQL] [ORDER_BY_SQL]";
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, ["s",$colour . "%","s","_" . $colour . "%"], $sql_filter->parameters);
$searchsql = $sql_prefix . $sql->sql . $sql_suffix;
$sql->sql = $searchsql;
} elseif (substr($search, 0, 4) == "!rgb") {
// Similar to a colour
$rgb = explode(":", $search);
$rgb = explode(",", $rgb[1]);
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$searchsql = new PreparedStatementQuery();
$searchsql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE has_image > 0 AND " . $sql_filter->sql . " [GROUP_BY_SQL] [ORDER_BY_SQL]" . $sql_suffix;
$order_by = "(abs(image_red - ?)+abs(image_green - ?)+abs(image_blue - ?)) ASC";
$order_by_params = ["i",$rgb[0],"i",$rgb[1],"i",$rgb[2]];
$hardlimit = 500;
$searchsql->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters);
$sql = $searchsql;
} elseif (substr($search, 0, 10) == "!nopreview") {
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql = new PreparedStatementQuery();
$sql->sql = $sql_prefix .
"SELECT [SELECT_SQL]
FROM resource r
$sql_join->sql
WHERE has_image=0
AND {$sql_filter->sql}
[GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $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);
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE r.ref = ? AND " . $sql_filter->sql . " [GROUP_BY_SQL] [ORDER_BY_SQL]" . $sql_suffix;
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, ["i",$searchref], $sql_filter->parameters);
} elseif (substr($search, 0, 15) == "!archivepending") {
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE r.archive=1 AND " . $sql_filter->sql . " [GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters);
} elseif (substr($search, 0, 12) == "!userpending") {
if ($orig_order == "rating") {
$order_by = "request_count DESC," . $order_by;
}
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE r.archive=-1
AND {$sql_filter->sql} [GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $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->sql = str_replace(["rca.access", "rca2.access"], "0", $select->sql);
}
$select->sql = "DISTINCT r.hit_count score, " . str_replace(",rca.access group_access,rca2.access user_access ", ",null group_access, null user_access ", $select->sql);
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE created_by = ? AND r.ref > 0 AND " . $sql_filter->sql . " [GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, ["i", $cuser], $sql_filter->parameters);
} elseif ($search == "!images") {
// Search for resources with images
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE has_image>0 AND " . $sql_filter->sql . " [GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters);
} elseif (substr($search, 0, 7) == "!unused") {
// Search for resources not used in any collections
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix;
$sql->sql .= sprintf(
"SELECT [SELECT_SQL]
FROM resource r %s
WHERE r.ref>0
AND r.ref NOT IN (SELECT c.resource FROM collection_resource c)
AND %s
[GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}",
$sql_join->sql,
$sql_filter->sql
);
$sql->sql .= $sql_suffix;
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters);
} 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");
}
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix;
$sql->sql .= sprintf(
"SELECT [SELECT_SQL]
FROM resource r %s%s
AND %s
[GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}",
$sql_join->sql,
$listsql->sql,
$sql_filter->sql,
);
$sql->parameters = array_merge($select->parameters, $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;
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " AND " . $sql_filter->sql . " [GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $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->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters);
}
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE r.ref > 0 AND " . $sql_filter->sql . " [GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $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
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE integrity_fail=1 AND no_file=0 AND " . $sql_filter->sql . " [GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters);
} elseif ($search == "!locked") {
// Search for locked resources
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE lock_user<>0 AND " . $sql_filter->sql . " [GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $sql_join->parameters, $sql_filter->parameters);
} elseif (checkperm('a') && $search == "!noningested") {
// System admins only - search for resources that have not been ingested - unfiltered
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$sql->sql = $sql_prefix . "SELECT [SELECT_SQL] FROM resource r " . $sql_join->sql . " WHERE file_path IS NOT NULL AND file_path <> '' [GROUP_BY_SQL] [ORDER_BY_SQL] {$sql_suffix}";
$sql->parameters = array_merge($select->parameters, $sql_join->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...');
$select->sql = "DISTINCT r.hit_count score, {$select->sql}";
$no_results_sql = new PreparedStatementQuery(
$sql_prefix . "SELECT [SELECT_SQL] FROM resource AS r "
. $sql_join->sql
. ' WHERE 1 = 2 AND ' . $sql_filter->sql
. ' [GROUP_BY_SQL] [ORDER_BY_SQL] ' . $sql_suffix,
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 [SELECT_SQL] FROM resource AS r"
. " INNER JOIN ($report_sql) AS rsr ON rsr.thumbnail = r.ref "
. $sql_join->sql
. ' WHERE ' . $sql_filter->sql . ' [GROUP_BY_SQL] [ORDER_BY_SQL] ' . $sql_suffix;
$sql->parameters = array_merge($select->parameters, $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 != "") {
// Construct a reduced query for refs only searches, disk usage or count only searches
$removecolumns = ["c.date_added",
"c.comment",
"length\\(c\\.comment\\) commentset",
"r.has_image",
"r.is_transcoding",
"r.creation_date",
"r.user_rating_count",
"r.user_rating_total",
"r.user_rating",
"r.rating",
"r.file_extension",
"r.preview_extension",
"r.image_red",
"r.image_green",
"r.image_blue",
"r.thumb_width",
"r.thumb_height",
"r.colour_key",
"r.created_by",
"r.file_modified",
"r.file_checksum",
"r.request_count",
"r.new_hit_count",
"r.expiry_notification_sent",
"r.preview_tweaks",
"r.file_path",
"r.modified",
"r.file_size",
"rty.order_by",
"c.date_added",
"c.comment",
];
$reducedselect = $select->sql;
foreach ($removecolumns as $removecolumn) {
$reducedselect = preg_replace("/(,\s?" . $removecolumn . ")/", "", $reducedselect);
}
$reducedselect = preg_replace("/(,\s?r\\.field\\d+)/", "", $reducedselect); // remove any fieldXX columns from select
$reduced_sql = clone $sql;
$reduced_sql->sql = str_replace(
["[SELECT_SQL]", "[GROUP_BY_SQL]", "[ORDER_BY_SQL]"],
[$reducedselect, "GROUP BY r.ref", ""],
$reduced_sql->sql
);
if (isset($hardlimit)) {
$reduced_sql->sql .= " LIMIT $hardlimit";
}
if ($return_refs_only) {
$sql = $reduced_sql;
} else {
$sql->sql = str_replace(
["[SELECT_SQL]", "[GROUP_BY_SQL]", "[ORDER_BY_SQL]"],
[$select->sql, "GROUP BY r.ref", "ORDER BY {$order_by}"],
$sql->sql
);
if (isset($order_by_params)) {
// Only used by $rgb search
$sql->parameters = array_merge($sql->parameters, $order_by_params);
if (isset($hardlimit) && $chunk_offset > $hardlimit - $search_chunk_size) {
$chunk_offset = $hardlimit - $search_chunk_size;
}
}
}
if ($returnsql) {
return $sql;
} else {
$result = sql_limit_with_total_count($sql, $search_chunk_size, $chunk_offset, $b_cache_count, $reduced_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 16th June 2025 11:05 Europe/London time based on the source file dated 4th June 2025 11:45 Europe/London time.