/**
* Return the orderby argv for query on a custom field sort
*
* @param mixed $sort_view_id
* @param mixed $query
*/
function query_get_orderby_custom_field($data_set_key, $sort_view_id, $orderBy, $query)
{
global $wpdb;
$sort_view_settings = $this->get_sort_view_settings($sort_view_id);
$sort_view_data = get_post($sort_view_id);
$sortID = $sort_view_data->post_parent;
$sort_settings = $this->get_sort_settings($sortID);
$data_set = array('order_by' => (array) $sort_view_settings['_auto_order_by'], 'custom_field_name' => (array) $sort_view_settings['_auto_custom_field_name'], 'custom_field_type' => (array) $sort_view_settings['_auto_custom_field_type'], 'custom_function_name' => (array) $sort_view_settings['_auto_custom_function_name'], 'order' => (array) $sort_view_settings['_auto_order']);
$custom_field_name = $data_set['custom_field_name'][$data_set_key];
//if empty no need to continue
if (empty($custom_field_name)) {
return $orderBy;
}
$custom_field_type = $data_set['custom_field_type'][$data_set_key];
//fallback compatibility
if ($custom_field_type == '') {
$custom_field_type = 'none';
}
$order_list = array();
//retrieve the list of posts which contain the custom field
if (isset($sort_settings['_view_type']) && $sort_settings['_view_type'] == 'simple') {
//this is the simple view
$mysql_query = "SELECT DISTINCT " . $wpdb->posts . ".ID, pm1.meta_value FROM " . $wpdb->posts . " \n JOIN " . $wpdb->postmeta . " as pm1 ON (" . $wpdb->posts . ".ID = pm1.post_id)";
//taxonomy
if (isset($sort_settings['_rules']['taxonomy']) && count($sort_settings['_rules']['taxonomy']) > 0) {
$q_inner_count = 1;
foreach ($sort_settings['_rules']['taxonomy'] as $rule_tax) {
$mysql_query .= " INNER JOIN " . $wpdb->term_relationships . " AS tr" . $q_inner_count . " ON (" . $wpdb->posts . ".ID = tr" . $q_inner_count . ".object_id)";
$q_inner_count++;
}
}
$mysql_query .= " WHERE 1=1";
//taxonomy
if (isset($sort_settings['_rules']['taxonomy']) && count($sort_settings['_rules']['taxonomy']) > 0) {
$mysql_query .= " AND ( ";
$first_tax = TRUE;
$q_inner_count = 1;
foreach ($sort_settings['_rules']['taxonomy'] as $rule_tax) {
if ($first_tax === TRUE) {
$first_tax = FALSE;
$mysql_query .= " ( ";
} else {
$mysql_query .= " " . $sort_settings['_rules']['taxonomy_relation'] . " ( ";
}
$query_terms = array();
foreach ($rule_tax['terms'] as $term_id) {
$term_data = get_term($term_id, $rule_tax['taxonomy']);
$query_terms[] = $term_data->term_taxonomy_id;
}
if ($rule_tax['operator'] == 'IN') {
$mysql_query .= "tr" . $q_inner_count . ".term_taxonomy_id IN (" . implode(",", $query_terms) . ")";
} else {
if ($rule_tax['operator'] == 'NOT IN') {
$mysql_query .= $wpdb->posts . ".ID NOT IN (\n SELECT object_id\n FROM tr" . $q_inner_count . "\n WHERE term_taxonomy_id IN (" . implode(",", $query_terms) . "))";
} else {
if ($rule_tax['operator'] == 'AND') {
$mysql_query .= " (\n SELECT COUNT(1)\n FROM " . $wpdb->term_relationships . "\n WHERE term_taxonomy_id IN (" . implode(",", $query_terms) . ")\n AND object_id = wp_posts.ID\n ) = " . count($query_terms) . " ";
}
}
}
$mysql_query .= " ) ";
$q_inner_count++;
}
$mysql_query .= " ) ";
}
//add author if set
if (isset($sort_settings['_rules']['author']) && count($sort_settings['_rules']['author']) > 0) {
$mysql_query .= " AND " . $wpdb->posts . ".post_author IN ('" . implode("', '", $sort_settings['_rules']['author']) . "')";
}
$mysql_query .= " AND pm1.meta_key = '" . esc_sql($custom_field_name) . "'\n AND " . $wpdb->posts . ".post_type IN ('" . implode("', '", $sort_settings['_rules']['post_type']) . "') ";
switch ($custom_field_type) {
case "SIGNED":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS SIGNED) " . $data_set['order'][$data_set_key];
break;
case "UNSIGNED":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS UNSIGNED) " . $data_set['order'][$data_set_key];
break;
case "float":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS DECIMAL(20,6)) " . $data_set['order'][$data_set_key];
break;
case "DATE":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS DATE) " . $data_set['order'][$data_set_key];
break;
case "DATETIME":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS DATETIME) " . $data_set['order'][$data_set_key];
break;
case "TIME":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS TIME) " . $data_set['order'][$data_set_key];
break;
default:
$mysql_query .= " ORDER BY pm1.meta_value " . $data_set['order'][$data_set_key];
break;
}
$results = $wpdb->get_results($mysql_query);
} else {
/**
* To deep Check !!
* Possible just to run query?
*/
//this is the multiple view
$mysql_query = "SELECT DISTINCT " . $wpdb->posts . ".ID, pm1.meta_value FROM " . $wpdb->posts . " \n JOIN " . $wpdb->postmeta . " as pm1 ON (" . $wpdb->posts . ".ID = pm1.post_id)";
//taxonomy
if (isset($query->tax_query->queries) && APTO_query_utils::queries_count($query->tax_query->queries) > 0) {
$q_inner_count = 1;
foreach (APTO_query_utils::get_tax_queries($query->tax_query->queries) as $rule_tax) {
$mysql_query .= " INNER JOIN " . $wpdb->term_relationships . " AS tr" . $q_inner_count . " ON (" . $wpdb->posts . ".ID = tr" . $q_inner_count . ".object_id)";
$q_inner_count++;
}
}
$mysql_query .= " WHERE 1=1";
//taxonomy
if (isset($query->tax_query->queries) && APTO_query_utils::queries_count($query->tax_query->queries) > 0) {
$mysql_query .= " AND ( ";
$first_tax = TRUE;
$q_inner_count = 1;
foreach (APTO_query_utils::get_tax_queries($query->tax_query->queries) as $rule_tax) {
if ($first_tax === TRUE) {
$first_tax = FALSE;
$mysql_query .= " ( ";
} else {
$mysql_query .= " " . $query->tax_query->relation . " ( ";
}
$query_terms = array();
foreach ($rule_tax['terms'] as $term_id) {
$term_data = get_term_by($rule_tax['field'], $term_id, $rule_tax['taxonomy']);
$query_terms[] = $term_data->term_taxonomy_id;
}
if ($rule_tax['operator'] == 'IN') {
$mysql_query .= "tr" . $q_inner_count . ".term_taxonomy_id IN (" . implode(",", $query_terms) . ")";
} else {
if ($rule_tax['operator'] == 'NOT IN') {
$mysql_query .= $wpdb->posts . ".ID NOT IN (\n SELECT object_id\n FROM tr" . $q_inner_count . "\n WHERE term_taxonomy_id IN (" . implode(",", $query_terms) . "))";
} else {
if ($rule_tax['operator'] == 'AND') {
$mysql_query .= " (\n SELECT COUNT(1)\n FROM " . $wpdb->term_relationships . "\n WHERE term_taxonomy_id IN (" . implode(",", $query_terms) . ")\n AND object_id = wp_posts.ID\n ) = " . count($query_terms) . " ";
}
}
}
$mysql_query .= " ) ";
$q_inner_count++;
}
$mysql_query .= " ) ";
}
//add author if set
if (isset($query->query['author']) && $query->query['author'] != '') {
$authors = (array) $query->query['author'];
$mysql_query .= " AND " . $wpdb->posts . ".post_author IN ('" . implode("', '", $query->query['author']) . "')";
}
$post_types = $this->query_get_post_types($query, TRUE);
$mysql_query .= " AND pm1.meta_key = '" . esc_sql($custom_field_name) . "'\n AND " . $wpdb->posts . ".post_type IN ('" . implode("', '", $post_types) . "')";
switch ($custom_field_type) {
case "SIGNED":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS SIGNED) " . $data_set['order'][$data_set_key];
break;
case "UNSIGNED":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS UNSIGNED) " . $data_set['order'][$data_set_key];
break;
case "float":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS DECIMAL(20,6)) " . $data_set['order'][$data_set_key];
break;
case "DATE":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS DATE) " . $data_set['order'][$data_set_key];
break;
case "DATETIME":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS DATETIME) " . $data_set['order'][$data_set_key];
break;
case "TIME":
$mysql_query .= " ORDER BY CAST(pm1.meta_value AS TIME) " . $data_set['order'][$data_set_key];
break;
default:
$mysql_query .= " ORDER BY pm1.meta_value " . $data_set['order'][$data_set_key];
break;
}
$results = $wpdb->get_results($mysql_query);
}
$orderBy = '';
if (count($results) > 0) {
$counter = 1;
$previous_meta_value = NULL;
$orderBy = "CASE ";
foreach ($results as $result) {
if ($previous_meta_value !== NULL && $previous_meta_value != $result->meta_value) {
$counter++;
}
$previous_meta_value = $result->meta_value;
$orderBy .= " WHEN " . $wpdb->posts . ".ID = " . $result->ID . " THEN " . $counter;
}
$counter++;
$orderBy .= " ELSE " . $counter . " END";
}
return $orderBy;
}
function APTO_posts_distinct($distinct, $query)
{
//check for NOT IN taxonomy operator
if (isset($query->tax_query->queries) && APTO_query_utils::queries_count($query->tax_query->queries) == 1) {
if (isset($query->tax_query->queries[0]['operator']) && $query->tax_query->queries[0]['operator'] == 'NOT IN') {
$distinct = 'DISTINCT';
}
}
return $distinct;
}
