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