/** * 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; }