/**
* @param bool $use_temp_table
* @return string|array
*/
public function get_sql_query($use_temp_table)
{
$index = $this->index_table;
$has_conditions = count($this->condition_stack[0]) > 0;
$create_temp_table = $use_temp_table && $has_conditions;
if (!$create_temp_table) {
foreach ($this->condition_joins as $table_name => $join_condition) {
$this->add_left_join($table_name);
}
}
// prepare ORDER BY and value for the `Score` column
$ranking_select = "1 AS `Score` ";
$order_by = "";
$sort_field_name = $this->query->get('sort_by');
if ($sort_field_name == "last_modified") {
$this->add_join($this->document_table);
$order_by = "ORDER BY `{$this->document_table}`.`LastModified` DESC";
} elseif ($sort_field_name) {
// some other field
/** @var $sort_fields nc_search_provider_index_field_manager */
$sort_fields = $this->translator->get_fields('name', $sort_field_name)->where('is_sortable', true);
if (count($sort_fields)) {
$order_by = array();
/** @var $f nc_search_provider_index_field */
foreach ($sort_fields as $f) {
$field_table_name = $f->get_field_table_name();
$this->add_left_join($field_table_name);
$order_by[] = "`{$field_table_name}`.`RawData`";
}
$order_by = "ORDER BY IFNULL(" . join(", ", $order_by) . ", 0) " . ($this->query->get('sort_direction') == SORT_DESC ? "DESC" : "ASC");
}
}
if (!$order_by) {
// standard sorting by relevance
$ranking_select = $this->term_ranking_calculation();
$order_by = "ORDER BY `Score` DESC";
}
// Prepare area condition (will need join)
$area_condition = "";
$area = $this->query->get('area');
if ($area) {
$this->add_join($this->document_table);
if (!$area instanceof nc_search_area) {
$area = new nc_search_area($area);
}
$area_condition = "AND " . $area->get_sql_condition() . "\n";
}
// Compose query
// SELECT
$query_string = "SELECT " . ($create_temp_table ? "" : "SQL_CALC_FOUND_ROWS ") . "`{$index}`.`Document_ID`,\n" . $ranking_select;
// FROM
$query_string .= "FROM `{$index}` FORCE INDEX (`Content`)\n";
// INNER JOINs
foreach ($this->joins as $j) {
$query_string .= "{$j}\n";
}
// LEFT JOINs
foreach ($this->left_joins as $j) {
$query_string .= "{$j}\n";
}
// WHERE [index_match_condition]
// (1) Index match; (2) site/path filter
$query_string .= "WHERE {$this->index_match}\n{$area_condition}";
// (3) Extra conditions
if ($has_conditions && !$use_temp_table) {
$query_string .= "AND " . join("\nAND ", $this->condition_stack[0]);
}
// ORDER
$query_string .= "\n{$order_by}";
if ($use_temp_table) {
$t = $this->get_temporary_table_name();
$queries = array("temp_table" => $t, "prefilter" => $query_string, "refinement" => "SELECT t.`Document_ID`, t.`Rank`\n" . "FROM `{$t}` AS t\n" . join("\n", $this->condition_joins) . "\n" . "WHERE t.`Rank` >= IFNULL(@rank_value,0) AND" . join("\nAND ", $this->condition_stack[0]) . "\n" . "ORDER BY t.`Rank`\n");
return $queries;
} else {
// LIMIT, OFFSET
$query_string .= "\nLIMIT " . (int) $this->query->get('limit') . "\nOFFSET " . (int) $this->query->get('offset');
return $query_string;
}
}
