/**
* this function exists because mysql is a turd, see the docs for
* build_accurate_search_querylet() for a full explanation
*/
private static function build_ugly_search_querylet($terms)
{
global $config, $database;
$tag_querylets = array();
$img_querylets = array();
$positive_tag_count = 0;
$negative_tag_count = 0;
$stpe = new SearchTermParseEvent(null, $terms);
send_event($stpe);
if ($stpe->is_querylet_set()) {
foreach ($stpe->get_querylets() as $querylet) {
$img_querylets[] = new ImgQuerylet($querylet, true);
}
}
// turn each term into a specific type of querylet
foreach ($terms as $term) {
$negative = false;
if (strlen($term) > 0 && $term[0] == '-') {
$negative = true;
$term = substr($term, 1);
}
$term = Tag::resolve_alias($term);
$stpe = new SearchTermParseEvent($term, $terms);
send_event($stpe);
if ($stpe->is_querylet_set()) {
foreach ($stpe->get_querylets() as $querylet) {
$img_querylets[] = new ImgQuerylet($querylet, !$negative);
}
} else {
$term = str_replace("*", "%", $term);
$term = str_replace("?", "_", $term);
if (!preg_match("/^[%_]+\$/", $term)) {
$tag_querylets[] = new TagQuerylet($term, !$negative);
}
}
}
// merge all the tag querylets into one generic one
$sql = "0";
$terms = array();
foreach ($tag_querylets as $tq) {
$sign = $tq->positive ? "+" : "-";
$sql .= " {$sign} (tag LIKE ?)";
$terms[] = $tq->tag;
if ($sign == "+") {
$positive_tag_count++;
} else {
$negative_tag_count++;
}
}
$tag_search = new Querylet($sql, $terms);
// merge all the image metadata searches into one generic querylet
$n = 0;
$sql = "";
$terms = array();
foreach ($img_querylets as $iq) {
if ($n++ > 0) {
$sql .= " AND";
}
if (!$iq->positive) {
$sql .= " NOT";
}
$sql .= " (" . $iq->qlet->sql . ")";
$terms = array_merge($terms, $iq->qlet->variables);
}
$img_search = new Querylet($sql, $terms);
// no tags, do a simple search (+image metadata if we have any)
if ($positive_tag_count + $negative_tag_count == 0) {
$query = new Querylet("SELECT images.*,UNIX_TIMESTAMP(posted) AS posted_timestamp FROM images ");
if (strlen($img_search->sql) > 0) {
$query->append_sql(" WHERE ");
$query->append($img_search);
}
} else {
if ($positive_tag_count == 1 && $negative_tag_count == 0) {
$query = new Querylet("\n\t\t\t\t\tSELECT images.*, UNIX_TIMESTAMP(posted) AS posted_timestamp\n\t\t\t\t\tFROM tags, image_tags, images\n\t\t\t\t\tWHERE\n\t\t\t\t\t\ttag LIKE ?\n\t\t\t\t\t\tAND tags.id = image_tags.tag_id\n\t\t\t\t\t\tAND image_tags.image_id = images.id\n\t\t\t\t", $tag_search->variables);
if (strlen($img_search->sql) > 0) {
$query->append_sql(" AND ");
$query->append($img_search);
}
} else {
$s_tag_array = array_map("sql_escape", $tag_search->variables);
$s_tag_list = join(', ', $s_tag_array);
$tag_id_array = array();
$tags_ok = true;
foreach ($tag_search->variables as $tag) {
$tag_ids = $database->db->GetCol("SELECT id FROM tags WHERE tag LIKE ?", array($tag));
$tag_id_array = array_merge($tag_id_array, $tag_ids);
$tags_ok = count($tag_ids) > 0;
if (!$tags_ok) {
break;
}
}
if ($tags_ok) {
$tag_id_list = join(', ', $tag_id_array);
$subquery = new Querylet("\n\t\t\t\t\tSELECT images.*, SUM({$tag_search->sql}) AS score\n\t\t\t\t\tFROM images\n\t\t\t\t\tLEFT JOIN image_tags ON image_tags.image_id = images.id\n\t\t\t\t\tJOIN tags ON image_tags.tag_id = tags.id\n\t\t\t\t\tWHERE tags.id IN ({$tag_id_list})\n\t\t\t\t\tGROUP BY images.id\n\t\t\t\t\tHAVING score = ?", array_merge($tag_search->variables, array($positive_tag_count)));
$query = new Querylet("\n\t\t\t\t\tSELECT *, UNIX_TIMESTAMP(posted) AS posted_timestamp\n\t\t\t\t\tFROM ({$subquery->sql}) AS images ", $subquery->variables);
if (strlen($img_search->sql) > 0) {
$query->append_sql(" WHERE ");
$query->append($img_search);
}
} else {
# there are no results, "where 1=0" should shortcut things
$query = new Querylet("\n\t\t\t\t\tSELECT images.*\n\t\t\t\t\tFROM images\n\t\t\t\t\tWHERE 1=0\n\t\t\t\t");
}
}
}
return $query;
}
/**
* this function exists because mysql is a turd, see the docs for
* build_accurate_search_querylet() for a full explanation
*
* @param array $terms
* @return Querylet
*/
private static function build_ugly_search_querylet($terms)
{
global $database;
$tag_querylets = array();
$img_querylets = self::parse_meta_terms($terms);
$positive_tag_count = 0;
$negative_tag_count = 0;
$terms = Tag::resolve_aliases($terms);
reset($terms);
// rewind to first element in array.
// turn each term into a specific type of querylet
foreach ($terms as $term) {
$negative = false;
if (!empty($term) && $term[0] == '-') {
$negative = true;
$term = substr($term, 1);
}
$stpe = new SearchTermParseEvent($term, $terms);
send_event($stpe);
if ($stpe->is_querylet_set()) {
foreach ($stpe->get_querylets() as $querylet) {
$img_querylets[] = new ImgQuerylet($querylet, !$negative);
}
} else {
$term = str_replace("*", "%", $term);
$term = str_replace("?", "_", $term);
if (!preg_match("/^[%_]+\$/", $term)) {
$tag_querylets[] = new TagQuerylet($term, !$negative);
}
}
}
// merge all the tag querylets into one generic one
$sql = "0";
$terms = array();
foreach ($tag_querylets as $tq) {
$sign = $tq->positive ? "+" : "-";
$sql .= ' ' . $sign . ' (tag LIKE :tag' . Image::$tag_n . ')';
$terms['tag' . Image::$tag_n] = $tq->tag;
Image::$tag_n++;
if ($sign === "+") {
$positive_tag_count++;
} else {
$negative_tag_count++;
}
}
$tag_search = new Querylet($sql, $terms);
$img_search = self::build_img_search($img_querylets);
// no tags, do a simple search (+image metadata if we have any)
if ($positive_tag_count + $negative_tag_count == 0) {
$query = self::build_simple_query($img_search);
} else {
if ($positive_tag_count === 1 && $negative_tag_count === 0) {
// MySQL is braindead, and does a full table scan on images, running the subquery once for each row -_-
// "{$this->get_images} WHERE images.id IN (SELECT image_id FROM tags WHERE tag LIKE ?) ",
$query = new Querylet("\n\t\t\t\tSELECT images.*\n\t\t\t\tFROM images\n\t\t\t\tJOIN image_tags ON images.id=image_tags.image_id\n\t\t\t\tJOIN tags ON image_tags.tag_id=tags.id\n\t\t\t\tWHERE tag LIKE :tag0\n\t\t\t", $tag_search->variables);
if (!empty($img_search->sql)) {
$query->append_sql(" AND ");
$query->append($img_search);
}
} else {
if ($positive_tag_count >= 1) {
$tag_id_array = array();
$tags_ok = true;
$x = 0;
foreach ($tag_search->variables as $tag) {
$tag_ids = $database->get_col("SELECT id FROM tags WHERE tag LIKE :tag", array("tag" => $tag));
$tag_id_array = array_merge($tag_id_array, $tag_ids);
$tags_ok = count($tag_ids) > 0 || !$tag_querylets[$x]->positive;
if (!$tags_ok) {
break;
}
$x++;
}
if ($tags_ok) {
$tag_id_list = join(', ', $tag_id_array);
$subquery = new Querylet('
SELECT images.*, SUM(' . $tag_search->sql . ') AS score
FROM images
LEFT JOIN image_tags ON image_tags.image_id = images.id
JOIN tags ON image_tags.tag_id = tags.id
WHERE tags.id IN (' . $tag_id_list . ')
GROUP BY images.id
HAVING score = :score', array_merge($tag_search->variables, array("score" => $positive_tag_count)));
$query = new Querylet('
SELECT *
FROM (' . $subquery->sql . ') AS images ', $subquery->variables);
if (!empty($img_search->sql)) {
$query->append_sql(" WHERE ");
$query->append($img_search);
}
} else {
# there are no results, "where 1=0" should shortcut things
$query = new Querylet("\n\t\t\t\t\tSELECT images.*\n\t\t\t\t\tFROM images\n\t\t\t\t\tWHERE 1=0\n\t\t\t\t");
}
} else {
$query = new Querylet("\n\t\t\t\tSELECT images.*\n\t\t\t\tFROM images\n\t\t\t\tWHERE 1=0\n\t\t\t");
}
}
}
Image::$tag_n = 0;
return $query;
}
/**
* this function exists because mysql is a turd, see the docs for
* build_accurate_search_querylet() for a full explanation
*/
private static function build_ugly_search_querylet($terms)
{
global $database;
$tag_querylets = array();
$img_querylets = array();
$positive_tag_count = 0;
$negative_tag_count = 0;
$stpe = new SearchTermParseEvent(null, $terms);
send_event($stpe);
if ($stpe->is_querylet_set()) {
foreach ($stpe->get_querylets() as $querylet) {
$img_querylets[] = new ImgQuerylet($querylet, true);
}
}
$terms = Tag::resolve_aliases($terms);
reset($terms);
// rewind to first element in array.
// turn each term into a specific type of querylet
foreach ($terms as $term) {
$negative = false;
if (!empty($term) && $term[0] == '-') {
$negative = true;
$term = substr($term, 1);
}
$stpe = new SearchTermParseEvent($term, $terms);
send_event($stpe);
if ($stpe->is_querylet_set()) {
foreach ($stpe->get_querylets() as $querylet) {
$img_querylets[] = new ImgQuerylet($querylet, !$negative);
}
} else {
$term = str_replace("*", "%", $term);
$term = str_replace("?", "_", $term);
if (!preg_match("/^[%_]+\$/", $term)) {
$tag_querylets[] = new TagQuerylet($term, !$negative);
}
}
}
// merge all the tag querylets into one generic one
$sql = "0";
$terms = array();
foreach ($tag_querylets as $tq) {
global $tag_n;
$sign = $tq->positive ? "+" : "-";
//$sql .= " $sign (tag LIKE :tag$tag_n)";
$sql .= ' ' . $sign . ' (tag LIKE :tag' . $tag_n . ')';
//$terms["tag$tag_n"] = $tq->tag;
$terms['tag' . $tag_n] = $tq->tag;
$tag_n++;
if ($sign === "+") {
$positive_tag_count++;
} else {
$negative_tag_count++;
}
}
$tag_search = new Querylet($sql, $terms);
// merge all the image metadata searches into one generic querylet
$n = 0;
$sql = "";
$terms = array();
foreach ($img_querylets as $iq) {
if ($n++ > 0) {
$sql .= " AND";
}
if (!$iq->positive) {
$sql .= " NOT";
}
$sql .= " (" . $iq->qlet->sql . ")";
$terms = array_merge($terms, $iq->qlet->variables);
}
$img_search = new Querylet($sql, $terms);
// no tags, do a simple search (+image metadata if we have any)
if ($positive_tag_count + $negative_tag_count == 0) {
$query = new Querylet("SELECT images.*,UNIX_TIMESTAMP(posted) AS posted_timestamp FROM images ");
if (!empty($img_search->sql)) {
$query->append_sql(" WHERE ");
$query->append($img_search);
}
} else {
if ($positive_tag_count === 1 && $negative_tag_count === 0) {
$query = new Querylet("\n\t\t\t\t\tSELECT images.*, UNIX_TIMESTAMP(posted) AS posted_timestamp\n\t\t\t\t\tFROM tags, image_tags, images\n\t\t\t\t\tWHERE\n\t\t\t\t\t\ttag LIKE :tag0\n\t\t\t\t\t\tAND tags.id = image_tags.tag_id\n\t\t\t\t\t\tAND image_tags.image_id = images.id\n\t\t\t\t", $tag_search->variables);
if (!empty($img_search->sql)) {
$query->append_sql(" AND ");
$query->append($img_search);
}
} else {
if ($positive_tag_count >= 1) {
$tag_id_array = array();
$tags_ok = true;
$x = 0;
foreach ($tag_search->variables as $tag) {
$tag_ids = $database->get_col("SELECT id FROM tags WHERE tag LIKE :tag", array("tag" => $tag));
$tag_id_array = array_merge($tag_id_array, $tag_ids);
$tags_ok = count($tag_ids) > 0 || !$tag_querylets[$x]->positive;
if (!$tags_ok) {
break;
}
$x++;
}
if ($tags_ok) {
$tag_id_list = join(', ', $tag_id_array);
$subquery = new Querylet('
SELECT images.*, SUM(' . $tag_search->sql . ') AS score
FROM images
LEFT JOIN image_tags ON image_tags.image_id = images.id
JOIN tags ON image_tags.tag_id = tags.id
WHERE tags.id IN (' . $tag_id_list . ')
GROUP BY images.id
HAVING score = :score', array_merge($tag_search->variables, array("score" => $positive_tag_count)));
$query = new Querylet('
SELECT *, UNIX_TIMESTAMP(posted) AS posted_timestamp
FROM (' . $subquery->sql . ') AS images ', $subquery->variables);
if (!empty($img_search->sql)) {
$query->append_sql(" WHERE ");
$query->append($img_search);
}
} else {
# there are no results, "where 1=0" should shortcut things
$query = new Querylet("\n\t\t\t\t\tSELECT images.*\n\t\t\t\t\tFROM images\n\t\t\t\t\tWHERE 1=0\n\t\t\t\t");
}
} else {
$query = new Querylet("\n\t\t\t\tSELECT images.*\n\t\t\t\tFROM images\n\t\t\t\tWHERE 1=0\n\t\t\t");
}
}
}
$tag_n = 0;
return $query;
}
