<?php
namespace Jet_Engine\Query_Builder\Queries;
use Jet_Engine\Query_Builder\Manager;
class SQL_Query extends Base_Query {
public $current_query = null;
/**
* Returns queries items
*
* @return [type] [description]
*/
public function _get_items() {
$sql = $this->build_sql_query();
$result = $this->wpdb()->get_results( $sql );
return $result;
}
public function get_current_items_page() {
$offset = ! empty( $this->final_query['offset'] ) ? absint( $this->final_query['offset'] ) : 0;
$per_page = $this->get_items_per_page();
if ( ! $offset || ! $per_page ) {
return 1;
} else {
return ceil( $offset / $per_page ) + 1;
}
}
/**
* Returns total found items count
*
* @return [type] [description]
*/
public function get_items_total_count() {
$cached = $this->get_cached_data( 'count' );
if ( false !== $cached ) {
return $cached;
}
$this->setup_query();
$sql = $this->build_sql_query( true );
$result = $this->wpdb()->get_var( $sql );
$this->update_query_cache( $result, 'count' );
return $result;
}
/**
* Returns count of the items visible per single listing grid loop/page
* @return [type] [description]
*/
public function get_items_per_page() {
$this->setup_query();
$limit = 0;
if ( ! empty( $this->final_query['limit_per_page'] ) ) {
$limit = absint( $this->final_query['limit_per_page'] );
} elseif ( ! empty( $this->final_query['limit'] ) ) {
$limit = absint( $this->final_query['limit'] );
}
return $limit;
}
/**
* Returns queried items count per page
*
* @return [type] [description]
*/
public function get_items_page_count() {
return $this->get_items_total_count();
}
public function set_filtered_prop( $prop = '', $value = null ) {
switch ( $prop ) {
case '_page':
$page = absint( $value );
if ( 0 < $page ) {
$offset = ( $page - 1 ) * $this->get_items_per_page();
$this->final_query['offset'] = $offset;
}
break;
case 'orderby':
case 'order':
case 'meta_key':
$key = $prop;
if ( 'orderby' === $prop ) {
$key = 'type';
$value = ( 'meta_key' === $value ) ? 'CHAR' : 'DECIMAL';
} elseif ( 'meta_key' === $prop ) {
$key = 'orderby';
}
$this->set_filtered_order( $key, $value );
break;
case 'meta_query':
foreach ( $value as $row ) {
$prepared_row = array(
'column' => ! empty( $row['key'] ) ? $row['key'] : false,
'compare' => ! empty( $row['compare'] ) ? $row['compare'] : '=',
'value' => ! empty( $row['value'] ) ? $row['value'] : '',
'type' => ! empty( $row['type'] ) ? $row['type'] : false,
);
$this->update_where_row( $prepared_row );
}
break;
}
}
public function set_filtered_order( $key, $value ) {
if ( empty( $this->final_query['orderby'] ) ) {
$this->final_query['orderby'] = array();
}
if ( ! isset( $this->final_query['orderby']['custom'] ) ) {
$this->final_query['orderby'] = array_merge( array( 'custom' => array() ), $this->final_query['orderby'] );
}
$this->final_query['orderby']['custom'][ $key ] = $value;
}
public function update_where_row( $row ) {
if ( empty( $this->final_query['where'] ) ) {
$this->final_query['where'] = array();
}
foreach ( $this->final_query['where'] as $index => $existing_row ) {
if ( $existing_row['column'] === $row['column'] && $existing_row['compare'] === $row['compare'] ) {
$this->final_query['where'][ $index ] = $row;
return;
}
}
$this->final_query['where'][] = $row;
}
/**
* Returns queried items pages count
*
* @return [type] [description]
*/
public function get_items_pages_count() {
$per_page = $this->get_items_per_page();
$total = $this->get_items_total_count();
if ( ! $per_page || ! $total ) {
return 1;
} else {
return ceil( $total / $per_page );
}
}
public function wpdb() {
global $wpdb;
return $wpdb;
}
public function build_sql_query( $is_count = false ) {
$prefix = $this->wpdb()->prefix;
$select_query = "SELECT ";
if ( $is_count ) {
$select_query .= " COUNT(*) ";
} else {
if ( ! empty( $this->final_query['include_columns'] ) ) {
$implode = array();
foreach ( $this->final_query['include_columns'] as $col ) {
$implode[] = $col . " AS '" . $col . "'";
}
$select_query .= implode( ', ', $implode ) . " ";
} else {
$select_query .= "* ";
}
}
if ( null === $this->current_query ) {
$raw_table = $this->final_query['table'];
$prefixed_table = $prefix . $raw_table;
$current_query = "";
$tables = array(
$raw_table => 1
);
$current_query .= "FROM $prefixed_table AS $raw_table ";
if ( ! empty( $this->final_query['join_tables'] ) ) {
foreach ( $this->final_query['join_tables'] as $table ) {
$type = $table['type'];
$raw_join_table = $table['table'];
$join_table = $prefix . $table['table'];
if ( ! empty( $tables[ $raw_join_table ] ) ) {
$tables[ $raw_join_table ] = $tables[ $raw_join_table ] + 1;
$as_table = $raw_join_table . $tables[ $raw_join_table ];
} else {
$tables[ $raw_join_table ] = 1;
$as_table = $raw_join_table;
}
$base_col = $table['on_base'];
$current_col = $table['on_current'];
$current_query .= "$type $join_table AS $as_table ON $raw_table.$base_col = $as_table.$current_col ";
}
}
if ( ! empty( $this->final_query['where'] ) ) {
$where = array();
foreach ( $this->final_query['where'] as $row ) {
$where[] = $row;
}
$current_query .= $this->add_where_args( $where );
}
if ( ! empty( $this->final_query['orderby'] ) ) {
$orderby = array();
$current_query .= " ";
foreach ( $this->final_query['orderby'] as $row ) {
$row['column'] = $row['orderby'];
$orderby[] = $row;
}
$current_query .= $this->add_order_args( $orderby );
}
$this->current_query = $current_query;
}
$limit_offset = "";
if ( ! $is_count ) {
$limit = $this->get_items_per_page();
} else {
$limit = ! empty( $this->final_query['limit'] ) ? absint( $this->final_query['limit'] ) : 0;;
}
if ( $limit ) {
$limit_offset .= " ";
$limit_offset .= "LIMIT $limit";
$offset = ! empty( $this->final_query['offset'] ) ? absint( $this->final_query['offset'] ) : 0;
if ( $offset ) {
$limit_offset .= ", $offset";
}
}
$result = apply_filters(
'jet-engine/query-builder/build-query/result',
$select_query . $this->current_query . $limit_offset . ";",
$this,
$is_count
);
return $result;
}
/**
* Add ordering arguments
*/
public function add_order_args( $args = array() ) {
$query = "ORDER BY ";
$glue = ' ';
foreach ( $args as $arg ) {
$column = $arg['column'];
$type = $arg['type'];
$order = $arg['order'];
$query .= $glue;
switch ( $arg['type'] ) {
case 'NUMERIC':
case 'DECIMAL':
$query .= "CAST( $column as DECIMAL )";
break;
case 'CHAR':
$query .= $column;
break;
default:
$query .= "CAST( $column as $type )";
break;
}
$query .= " ";
$query .= $order;
$glue = ", ";
}
return $query;
}
/**
* Add nested query arguments
*
* @param [type] $key [description]
* @param [type] $value [description]
* @param boolean $format [description]
* @return [type] [description]
*/
public function get_sub_query( $key, $value, $format = false ) {
$query = '';
$glue = '';
if ( ! $format ) {
if ( false !== strpos( $key, '!' ) ) {
$format = '%1$s != \'%2$s\'';
$key = ltrim( $key, '!' );
} else {
$format = '%1$s = \'%2$s\'';
}
}
foreach ( $value as $child ) {
$query .= $glue;
$query .= sprintf( $format, esc_sql( $key ), esc_sql( $child ) );
$glue = ' OR ';
}
return $query;
}
/**
* Add where arguments to query
*
* @param array $args [description]
* @param string $rel [description]
*/
public function add_where_args( $args = array(), $rel = 'AND', $add_where_string = true ) {
$query = '';
$multi_args = false;
if ( ! empty( $args ) ) {
if ( $add_where_string ) {
$query .= ' WHERE ';
}
$glue = '';
if ( count( $args ) > 1 ) {
$multi_args = true;
}
foreach ( $args as $key => $arg ) {
if ( is_array( $arg ) && isset( $arg['column'] ) ) {
$column = ! empty( $arg['column'] ) ? $arg['column'] : false;
$compare = ! empty( $arg['compare'] ) ? $arg['compare'] : '=';
$value = ! empty( $arg['value'] ) ? $arg['value'] : '';
$type = ! empty( $arg['type'] ) ? $arg['type'] : false;
} else {
$column = $key;
$compare = '=';
$value = $arg;
$type = false;
}
$clause = $this->prepare_where_clause( $column, $compare, $value, $type );
if ( $clause ) {
$query .= $glue;
$query .= $clause;
$glue = ' ' . $rel . ' ';
}
}
}
return $query;
}
/**
* Adjust SQL value string according queried argument type
*
* @param string $value [description]
* @param boolean $type [description]
* @return [type] [description]
*/
public function adjust_value_by_type( $value = '', $type = false ) {
if ( is_array( $value ) ) {
return false;
}
if ( false !== strpos( strtolower( $type ), 'decimal' ) ) {
$type = 'float';
}
switch ( $type ) {
case 'integer':
$value = absint( $value );
break;
case 'float':
$value = floatval( $value );
break;
case 'timestamp':
if ( ! \Jet_Engine_Tools::is_valid_timestamp( $value ) ) {
$value = strtotime( $value );
}
$value = absint( $value );
break;
case 'date':
$value = strtotime( $value );
$value = sprintf( "'%s'", date( $value, 'Y-m-d H:i:s' ) );
break;
default:
$value = sprintf( "'%s'", esc_sql( $value ) );
break;
}
return $value;
}
/**
* Check if $haystack start from $needle
*
* @param [type] $haystack [description]
* @param [type] $needle [description]
* @return [type] [description]
*/
public function starts_with( $haystack, $needle ) {
$length = strlen( $needle );
return substr( $haystack, 0, $length ) === $needle;
}
/**
* Check if $haystack ends with $needle
*
* @param [type] $haystack [description]
* @param [type] $needle [description]
* @return [type] [description]
*/
public function ends_with( $haystack, $needle ) {
$length = strlen( $needle );
if ( ! $length ) {
return true;
}
return substr( $haystack, -$length ) === $needle;
}
/**
* Prepare string to use in like or not like operator
*
* @param [type] $value [description]
* @return [type] [description]
*/
public function prepare_value_for_like_operator( $value ) {
if ( $this->starts_with( $value, '%' ) || $this->ends_with( $value, '%' ) ) {
return sprintf( "'%s'", $value );
} else {
return sprintf( "'%%%s%%'", esc_sql( $value ) );
}
}
/**
* Prepare single query clause by arguments
*
* @return [type] [description]
*/
public function prepare_where_clause( $column = false, $compare = '=', $value = '', $type = false ) {
if ( ! $column ) {
return '';
}
$format = '%1$s %3$s %2$s';
$array_operators = array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' );
if ( ! is_array( $value ) && in_array( $compare, $array_operators ) && false !== strpos( $value, ',' ) ) {
$value = explode( ',', $value );
$value = array_map( 'trim', $value );
}
if ( is_array( $value ) ) {
switch ( $compare ) {
case 'IN':
case 'NOT IN':
array_walk( $value, function( &$item ) use ( $type ) {
$item = $this->adjust_value_by_type( $item, $type );
} );
$value = sprintf( '( %s )', implode( ', ', $value ) );
break;
case 'BETWEEN':
case 'NOT BETWEEN':
$from = isset( $value[0] ) ? $value[0] : 0;
$to = isset( $value[1] ) ? $value[1] : $from;
$from = $this->adjust_value_by_type( $from, $type );
$to = $this->adjust_value_by_type( $to, $type );
$value = sprintf( '%1$s AND %2$s', $from, $to );
break;
default:
$format = '(%2$s)';
$args = array();
foreach ( $value as $val ) {
$args[] = array(
'column' => $column,
'compare' => $compare,
'type' => $type,
'value' => $val,
);
}
$value = $this->add_where_args( $args, 'OR', false );
break;
}
} else {
if ( in_array( $compare, array( 'LIKE', 'NOT LIKE' ) ) ) {
$value = $this->prepare_value_for_like_operator( $value );
} else {
$value = $this->adjust_value_by_type( $value, $type );
}
if ( in_array( $compare, $array_operators ) ) {
$compare = '=';
}
}
$result = sprintf( $format, esc_sql( $column ), $value, $compare );
return $result;
}
/**
* Get fields list are available for the current instance of this query
*
* @return [type] [description]
*/
public function get_instance_fields() {
$cols = array();
if ( ! empty( $this->query['include_columns'] ) ) {
$cols = $this->query['include_columns'];
} elseif ( ! empty( $this->query['default_columns'] ) ) {
$cols = $this->query['default_columns'];
}
$result = array();
if ( ! empty( $cols ) ) {
foreach ( $cols as $col ) {
$result[ $col ] = $col;
}
}
return $result;
}
}