public static function query_field($field, $q, $pod = null, &$params = null)
{
global $wpdb;
$simple_tableless_objects = PodsForm::simple_tableless_objects();
$field_query = null;
// Plain queries
if (is_numeric($field) && !is_array($q)) {
return $q;
} elseif (!is_numeric($field) && (!is_array($q) || !isset($q['key']) && !isset($q['field']))) {
$new_q = array('field' => $field, 'compare' => pods_var_raw('compare', $q, '=', null, true), 'value' => pods_var_raw('value', $q, $q, null, true), 'sanitize' => pods_var_raw('sanitize', $q, true), 'sanitize_format' => pods_var_raw('sanitize_format', $q), 'cast' => pods_var_raw('cast', $q));
if (is_array($new_q['value'])) {
if ('=' == $new_q['compare']) {
$new_q['compare'] = 'IN';
}
if (isset($new_q['value']['compare'])) {
unset($new_q['value']['compare']);
}
}
$q = $new_q;
}
$field_name = trim(pods_var_raw('field', $q, pods_var_raw('key', $q, $field, null, true), null, true));
$field_type = strtoupper(trim(pods_var_raw('type', $q, 'CHAR', null, true)));
$field_value = pods_var_raw('value', $q);
$field_compare = strtoupper(trim(pods_var_raw('compare', $q, is_array($field_value ? 'IN' : '='), null, true)));
$field_sanitize = (bool) pods_var('sanitize', $q, true);
$field_sanitize_format = pods_var_raw('sanitize_format', $q, null, null, true);
$field_cast = pods_var_raw('cast', $q, null, null, true);
if (is_object($params)) {
$params->meta_query_syntax = true;
$params->query_fields[] = $field_name;
}
// Deprecated WP type
if ('NUMERIC' == $field_type) {
$field_type = 'SIGNED';
} elseif (!in_array($field_type, array('BINARY', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL', 'SIGNED', 'TIME', 'UNSIGNED'))) {
$field_type = 'CHAR';
}
// Alias / Casting
if (empty($field_cast)) {
// Setup field casting from field name
if (false === strpos($field_name, '`') && false === strpos($field_name, '(') && false === strpos($field_name, ' ')) {
// Handle field naming if Pod-based
if (!empty($pod) && false === strpos($field_name, '.')) {
$field_cast = '';
$tableless_field_types = PodsForm::tableless_field_types();
if (isset($pod['fields'][$field_name]) && in_array($pod['fields'][$field_name]['type'], $tableless_field_types)) {
if (in_array($pod['fields'][$field_name]['pick_object'], $simple_tableless_objects)) {
if ('meta' == $pod['storage']) {
$field_cast = "`{$field_name}`.`meta_value`";
} else {
$field_cast = "`t`.`{$field_name}`";
}
} else {
$table = pods_api()->get_table_info($pod['fields'][$field_name]['pick_object'], $pod['fields'][$field_name]['pick_val']);
if (!empty($table)) {
$field_cast = "`{$field_name}`.`" . $table['field_index'] . '`';
}
}
}
if (empty($field_cast)) {
if (!in_array($pod['type'], array('pod', 'table'))) {
if (isset($pod['object_fields'][$field_name])) {
$field_cast = "`t`.`{$field_name}`";
} elseif (isset($pod['fields'][$field_name])) {
if ('meta' == $pod['storage']) {
$field_cast = "`{$field_name}`.`meta_value`";
} else {
$field_cast = "`d`.`{$field_name}`";
}
} else {
foreach ($pod['object_fields'] as $object_field => $object_field_opt) {
if ($object_field == $field_name || in_array($field_name, $object_field_opt['alias'])) {
$field_cast = "`t`.`{$object_field}`";
break;
}
}
}
} elseif (isset($pod['fields'][$field_name])) {
if ('meta' == $pod['storage']) {
$field_cast = "`{$field_name}`.`meta_value`";
} else {
$field_cast = "`t`.`{$field_name}`";
}
}
if (empty($field_cast)) {
if ('meta' == $pod['storage']) {
$field_cast = "`{$field_name}`.`meta_value`";
} else {
$field_cast = "`t`.`{$field_name}`";
}
}
}
} else {
$field_cast = '`' . str_replace('.', '`.`', $field_name) . '`';
}
} else {
$field_cast = $field_name;
}
// Cast field if needed
if ('CHAR' != $field_type) {
$field_cast = 'CAST( ' . $field_cast . ' AS ' . $field_type . ' )';
}
}
// Setup string sanitizing for $wpdb->prepare()
if (empty($field_sanitize_format)) {
// Sanitize as string
$field_sanitize_format = '%s';
// Sanitize as integer if needed
if (in_array($field_type, array('UNSIGNED', 'SIGNED'))) {
$field_sanitize_format = '%d';
}
}
// Restrict to supported comparisons
if (!in_array($field_compare, array('=', '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'ALL', 'BETWEEN', 'NOT BETWEEN', 'EXISTS', 'NOT EXISTS', 'REGEXP', 'NOT REGEXP', 'RLIKE'))) {
$field_compare = '=';
}
// Restrict to supported array comparisons
if (is_array($field_value) && !in_array($field_compare, array('IN', 'NOT IN', 'ALL', 'BETWEEN', 'NOT BETWEEN'))) {
if (in_array($field_compare, array('!=', 'NOT LIKE'))) {
$field_compare = 'NOT IN';
} else {
$field_compare = 'IN';
}
} elseif (!is_array($field_value) && in_array($field_compare, array('IN', 'NOT IN', 'ALL', 'BETWEEN', 'NOT BETWEEN'))) {
$check_value = preg_split('/[,\\s]+/', $field_value);
if (1 < count($check_value)) {
$field_value = $check_value;
} elseif (in_array($field_compare, array('NOT IN', 'NOT BETWEEN'))) {
$field_compare = '!=';
} else {
$field_compare = '=';
}
}
// Restrict to two values, force = and != if only one value provided
if (in_array($field_compare, array('BETWEEN', 'NOT BETWEEN'))) {
$field_value = array_values(array_slice($field_value, 0, 2));
if (1 == count($field_value)) {
if ('NOT IN' == $field_compare) {
$field_compare = '!=';
} else {
$field_compare = '=';
}
}
}
// Single array handling
if (1 == count($field_value) && $field_compare == 'ALL') {
$field_compare = '=';
} elseif (empty($field_value) && in_array($field_compare, array('IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'))) {
$field_compare = 'EXISTS';
}
// Rebuild $q
$q = array('field' => $field_name, 'type' => $field_type, 'value' => $field_value, 'compare' => $field_compare, 'sanitize' => $field_sanitize, 'sanitize_format' => $field_sanitize_format, 'cast' => $field_cast);
// Make the query
if (in_array($field_compare, array('=', '!=', '>', '>=', '<', '<=', 'REGEXP', 'NOT REGEXP', 'RLIKE'))) {
if ($field_sanitize) {
$field_query = $wpdb->prepare($field_cast . ' ' . $field_compare . ' ' . $field_sanitize_format, $field_value);
} else {
$field_query = $field_cast . ' ' . $field_compare . ' "' . $field_value . '"';
}
} elseif (in_array($field_compare, array('LIKE', 'NOT LIKE'))) {
if ($field_sanitize) {
$field_query = $field_cast . ' ' . $field_compare . ' "%' . pods_sanitize_like($field_value) . '%"';
} else {
$field_query = $field_cast . ' ' . $field_compare . ' "' . $field_value . '"';
}
} elseif (in_array($field_compare, array('IN', 'NOT IN', 'ALL'))) {
if ($field_compare == 'ALL') {
$field_compare = 'IN';
if (!empty($pod)) {
$params->having[] = 'COUNT( DISTINCT ' . $field_cast . ' ) = ' . count($field_value);
if (empty($params->groupby) || !in_array('`t`.`' . $pod['field_id'] . '`', $params->groupby) && !in_array('t.' . $pod['field_id'] . '', $params->groupby)) {
$params->groupby[] = '`t`.`' . $pod['field_id'] . '`';
}
}
}
if ($field_sanitize) {
$field_query = $wpdb->prepare($field_cast . ' ' . $field_compare . ' ( ' . substr(str_repeat(', ' . $field_sanitize_format, count($field_value)), 1) . ' )', $field_value);
} else {
$field_query = $field_cast . ' ' . $field_compare . ' ( "' . implode('", "', $field_value) . '" )';
}
} elseif (in_array($field_compare, array('BETWEEN', 'NOT BETWEEN'))) {
if ($field_sanitize) {
$field_query = $wpdb->prepare($field_cast . ' ' . $field_compare . ' ' . $field_sanitize_format . ' AND ' . $field_sanitize_format, $field_value);
} else {
$field_query = $field_cast . ' ' . $field_compare . ' "' . $field_value[0] . '" AND "' . $field_value[1] . '"';
}
} elseif ('EXISTS' == $field_compare) {
$field_query = $field_cast . ' IS NOT NULL';
} elseif ('NOT EXISTS' == $field_compare) {
$field_query = $field_cast . ' IS NULL';
}
$field_query = apply_filters('pods_data_field_query', $field_query, $q);
return $field_query;
}