488 lines
10 KiB
PHP
488 lines
10 KiB
PHP
<?php
|
|
|
|
namespace AutomateWoo;
|
|
|
|
/**
|
|
* @class Query_Abstract
|
|
* @since 3.8
|
|
*/
|
|
abstract class Query_Abstract {
|
|
|
|
/**
|
|
* Set's the database table ID.
|
|
*
|
|
* @var string (required)
|
|
*/
|
|
public $table_id;
|
|
|
|
/** @var string (optional) */
|
|
public $meta_table_id;
|
|
|
|
/**
|
|
* Set's the class to use for the return object.
|
|
*
|
|
* @var string (required)
|
|
*/
|
|
protected $model;
|
|
|
|
/** @var array - top array level uses AND condition, for OR conditions nest */
|
|
public $where = [];
|
|
|
|
/** @var array - top array level uses AND condition, for OR conditions nest */
|
|
public $where_meta = [];
|
|
|
|
/**
|
|
* @var string Possible values: 'objects', 'ids'
|
|
*/
|
|
protected $return = 'objects';
|
|
|
|
/** @var int */
|
|
public $found_rows = 0;
|
|
|
|
/** @var int */
|
|
protected $limit;
|
|
|
|
/** @var int */
|
|
protected $offset;
|
|
|
|
/** @var int */
|
|
protected $page;
|
|
|
|
/** @var string */
|
|
protected $orderby;
|
|
|
|
/** @var string */
|
|
protected $order;
|
|
|
|
/** @var int */
|
|
protected $calc_found_rows = false;
|
|
|
|
/** @var string */
|
|
public $sql;
|
|
|
|
/** @var bool */
|
|
public $combine_wheres_with_or = false;
|
|
|
|
|
|
/**
|
|
* Possible compare values: =, <, > IN, NOT IN
|
|
*
|
|
* @param string $column
|
|
* @param mixed $value
|
|
* @param bool|string $compare - defaults to '=' or 'IN' if array
|
|
* @return $this
|
|
*/
|
|
public function where( $column, $value, $compare = false ) {
|
|
|
|
// if $column is not a column, do a meta query instead
|
|
if ( ! array_key_exists( $column, $this->get_table_columns() ) ) {
|
|
return $this->where_meta( $column, $value, $compare );
|
|
}
|
|
|
|
$this->where[] = [
|
|
'column' => $column,
|
|
'value' => $value,
|
|
'compare' => $compare,
|
|
];
|
|
|
|
return $this;
|
|
}
|
|
|
|
|
|
/**
|
|
* Does not support EXISTS or NOT EXISTS
|
|
*
|
|
* @param string $key
|
|
* @param mixed $value
|
|
* @param bool|string $compare - defaults to '=' or 'IN' if array
|
|
* @return $this
|
|
*/
|
|
public function where_meta( $key, $value, $compare = false ) {
|
|
|
|
$this->where_meta[] = [
|
|
'key' => $key,
|
|
'value' => $value,
|
|
'compare' => $compare,
|
|
];
|
|
|
|
return $this;
|
|
}
|
|
|
|
|
|
/**
|
|
* @param string $column The column to order_by the query
|
|
* @param string $order The ordering direction (ASC or DESC)
|
|
* @param array $valid_sortable_columns The allowed sortable columns. By default, empty array (all allowed)
|
|
* @return $this
|
|
*/
|
|
public function set_ordering( $column, $order = 'DESC', $valid_sortable_columns = [] ) {
|
|
$this->orderby = Clean::order_by( $column, $valid_sortable_columns );
|
|
$this->order = Clean::order( $order );
|
|
return $this;
|
|
}
|
|
|
|
|
|
/**
|
|
* @param int $i
|
|
* @return $this
|
|
*/
|
|
public function set_limit( $i ) {
|
|
$this->limit = absint( $i );
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* @param bool $calc_found_rows defaults to true now, but will change to default to false soon
|
|
* @return $this
|
|
*/
|
|
public function set_calc_found_rows( $calc_found_rows ) {
|
|
$this->calc_found_rows = (bool) $calc_found_rows;
|
|
return $this;
|
|
}
|
|
|
|
|
|
/**
|
|
* @param int $i
|
|
* @return $this
|
|
*/
|
|
public function set_offset( $i ) {
|
|
$this->offset = absint( $i );
|
|
return $this;
|
|
}
|
|
|
|
|
|
/**
|
|
* Don't set a page if an offset is also set.
|
|
* Requires a limit to be set.
|
|
*
|
|
* @since 4.0
|
|
*
|
|
* @param int $page
|
|
* @return $this
|
|
*/
|
|
public function set_page( $page ) {
|
|
$this->page = absint( $page );
|
|
return $this;
|
|
}
|
|
|
|
|
|
/**
|
|
* @deprecated in 5.1.0 use self::get_results_as_ids() instead
|
|
*
|
|
* @param string $return - 'objects' (default) | 'ids'
|
|
* @return $this
|
|
*/
|
|
public function set_return( $return ) {
|
|
wc_deprecated_function( __METHOD__, '5.1.0', '\AutomateWoo\Query_Abstract::get_results_as_ids' );
|
|
$this->return = Clean::string( $return );
|
|
return $this;
|
|
}
|
|
|
|
|
|
/**
|
|
* @return Database_Table
|
|
*/
|
|
public function get_table() {
|
|
|
|
if ( ! isset( $this->table_id ) ) {
|
|
// phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
|
|
wp_die( sprintf( 'AutomateWoo - %s is an incompatible subclass of %s. You need to update your AutomateWoo add-ons.', get_called_class(), __CLASS__ ) );
|
|
}
|
|
|
|
return Database_Tables::get( $this->table_id );
|
|
}
|
|
|
|
|
|
/**
|
|
* @return string
|
|
*/
|
|
public function get_table_name() {
|
|
return $this->get_table()->get_name();
|
|
}
|
|
|
|
|
|
/**
|
|
* @return array
|
|
*/
|
|
public function get_table_columns() {
|
|
return $this->get_table()->get_columns();
|
|
}
|
|
|
|
|
|
/**
|
|
* @return Database_Table|false
|
|
*/
|
|
public function get_meta_table() {
|
|
if ( ! $this->meta_table_id ) {
|
|
return false;
|
|
}
|
|
|
|
return Database_Tables::get( $this->meta_table_id );
|
|
}
|
|
|
|
|
|
/**
|
|
* @return string
|
|
*/
|
|
public function get_meta_table_name() {
|
|
if ( ! $this->meta_table_id ) {
|
|
return false;
|
|
}
|
|
|
|
return $this->get_meta_table()->get_name();
|
|
}
|
|
|
|
|
|
/**
|
|
* @return string
|
|
*/
|
|
public function get_meta_object_id_column() {
|
|
if ( ! $this->meta_table_id ) {
|
|
return false;
|
|
}
|
|
|
|
return $this->get_meta_table()->get_object_id_column();
|
|
}
|
|
|
|
|
|
/**
|
|
* @param array $where
|
|
* @return string
|
|
*/
|
|
private function parse_where( $where ) {
|
|
global $wpdb;
|
|
|
|
if ( ! is_array( $where ) ) {
|
|
return '';
|
|
}
|
|
|
|
$column = $where['column'];
|
|
$value = $where['value'];
|
|
$compare = isset( $where['compare'] ) ? $where['compare'] : false;
|
|
|
|
// Accepts AutomateWoo\DateTime, DateTime or WC_DateTime
|
|
if ( is_a( $value, 'DateTime' ) ) {
|
|
// always use UTC time
|
|
/** @var \DateTime $value */
|
|
$utc_date = new DateTime();
|
|
$utc_date->setTimestamp( $value->getTimestamp() );
|
|
$value = $utc_date->to_mysql_string();
|
|
}
|
|
|
|
if ( ! $compare ) {
|
|
$compare = is_array( $value ) ? 'IN' : '='; // set default values
|
|
}
|
|
|
|
if ( is_array( $value ) ) {
|
|
|
|
$value = "('" . implode( "','", esc_sql( $value ) ) . "')";
|
|
|
|
return "$column $compare $value";
|
|
} else {
|
|
return $wpdb->prepare(
|
|
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
|
|
"$column $compare %s",
|
|
$value
|
|
);
|
|
}
|
|
}
|
|
|
|
|
|
/**
|
|
* @param array $query
|
|
*/
|
|
protected function build_where_query( &$query ) {
|
|
$query_joins = [];
|
|
$query_wheres = [];
|
|
|
|
foreach ( $this->where as $where ) {
|
|
if ( isset( $where['column'] ) ) {
|
|
$query_wheres[] = $this->parse_where( $where );
|
|
} else {
|
|
$ors = [];
|
|
|
|
// nested OR conditions
|
|
foreach ( $where as $where_or ) {
|
|
$ors[] = $this->parse_where( $where_or );
|
|
}
|
|
|
|
$query_wheres[] = '( ' . implode( "\nOR ", $ors ) . ' )';
|
|
}
|
|
}
|
|
|
|
if ( ! empty( $this->where_meta ) && $this->get_meta_table_name() ) {
|
|
$i = 1;
|
|
foreach ( $this->where_meta as $where ) {
|
|
|
|
$query_joins[] = "INNER JOIN {$this->get_meta_table_name()} AS mt$i ON ({$this->get_table_name()}.id = mt$i.{$this->get_meta_object_id_column()})";
|
|
|
|
if ( isset( $where['key'] ) ) {
|
|
|
|
$meta_key = esc_sql( $where['key'] );
|
|
$where['column'] = "mt$i.meta_value";
|
|
|
|
$query_wheres[] = "(mt$i.meta_key = '$meta_key' AND "
|
|
. $this->parse_where( $where )
|
|
. ')';
|
|
} else {
|
|
|
|
$ors = [];
|
|
|
|
// nested OR conditions
|
|
foreach ( $where as $where_or ) {
|
|
|
|
$meta_key = esc_sql( $where_or['key'] );
|
|
$where_or['column'] = "mt$i.meta_value";
|
|
|
|
$ors[] = "(mt$i.meta_key = '$meta_key' AND "
|
|
. $this->parse_where( $where_or )
|
|
. ')';
|
|
}
|
|
|
|
$query_wheres[] = '( ' . implode( "\nOR ", $ors ) . ' )';
|
|
|
|
}
|
|
++$i;
|
|
}
|
|
}
|
|
|
|
if ( $query_joins ) {
|
|
$query[] = implode( "\n", $query_joins );
|
|
}
|
|
|
|
if ( ! empty( $query_wheres ) ) {
|
|
$query[] = 'WHERE';
|
|
$query[] = implode( $this->combine_wheres_with_or ? "\nOR " : "\nAND ", $query_wheres );
|
|
}
|
|
}
|
|
|
|
|
|
/**
|
|
* @return Model[]|array
|
|
*/
|
|
public function get_results() {
|
|
global $wpdb;
|
|
|
|
$return_fields = $this->return === 'ids' ? 'id' : '*';
|
|
$found_rows = $this->calc_found_rows ? 'SQL_CALC_FOUND_ROWS ' : '';
|
|
|
|
$query = [
|
|
"SELECT {$found_rows}{$return_fields} FROM {$this->get_table_name()}",
|
|
];
|
|
|
|
$this->build_where_query( $query );
|
|
|
|
// When a query involves a meta-table, adding the `GROUP BY` clause
|
|
// may still be necessary to avoid duplicate results.
|
|
//
|
|
// See `Query_Data_Layer_Abstract::where_customer_or_legacy_user`
|
|
if ( ! empty( $this->where_meta ) ) {
|
|
$query[] = "GROUP BY {$this->get_table_name()}.id";
|
|
}
|
|
|
|
if ( $this->orderby ) {
|
|
$query[] = "ORDER BY {$this->orderby} {$this->order}";
|
|
}
|
|
|
|
if ( $this->limit ) {
|
|
$query[] = "LIMIT $this->limit ";
|
|
}
|
|
|
|
if ( $this->offset ) {
|
|
$query[] = "OFFSET $this->offset ";
|
|
} elseif ( $this->page && $this->limit ) {
|
|
$query[] = 'OFFSET ' . ( $this->limit * ( $this->page - 1 ) ) . ' ';
|
|
}
|
|
|
|
$this->sql = apply_filters( 'automatewoo/query/get_results_sql', implode( "\n", $query ), $this );
|
|
|
|
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
|
|
$results = $wpdb->get_results( $this->sql, ARRAY_A );
|
|
|
|
if ( empty( $results ) ) {
|
|
return [];
|
|
}
|
|
|
|
if ( $this->calc_found_rows ) {
|
|
$this->found_rows = (int) $wpdb->get_var( 'SELECT FOUND_ROWS()' );
|
|
}
|
|
|
|
if ( $this->return === 'ids' ) {
|
|
return wp_list_pluck( $results, 'id' );
|
|
} else {
|
|
|
|
if ( $this->model ) {
|
|
$modelled_results = [];
|
|
|
|
foreach ( $results as $result ) {
|
|
/** @var Model $modelled_result */
|
|
$modelled_result = new $this->model();
|
|
$modelled_result->fill( $result );
|
|
$modelled_results[] = $modelled_result;
|
|
}
|
|
|
|
return $modelled_results;
|
|
}
|
|
|
|
return $results;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get query results as item IDs.
|
|
*
|
|
* @since 5.1.0
|
|
*
|
|
* @return int[]
|
|
*/
|
|
public function get_results_as_ids() {
|
|
$this->return = 'ids';
|
|
$ids = array_map( 'intval', $this->get_results() );
|
|
$this->return = 'objects';
|
|
return $ids;
|
|
}
|
|
|
|
/**
|
|
* @return int
|
|
*/
|
|
public function get_count() {
|
|
global $wpdb;
|
|
|
|
$query = [
|
|
"SELECT COUNT(id) FROM {$this->get_table_name()}",
|
|
];
|
|
|
|
$this->build_where_query( $query );
|
|
|
|
$this->sql = apply_filters( 'automatewoo/query/get_count_sql', implode( "\n", $query ), $this );
|
|
|
|
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
|
|
return (int) $wpdb->get_var( $this->sql );
|
|
}
|
|
|
|
|
|
/**
|
|
* Checks to see if a query would have at least one result
|
|
* Then returns the query back to normal state
|
|
*
|
|
* @since 3.2.4
|
|
* @return bool
|
|
*/
|
|
public function has_results() {
|
|
$limit = $this->limit;
|
|
$return = $this->return;
|
|
$calc_found_rows = $this->calc_found_rows;
|
|
|
|
$this->set_limit( 1 );
|
|
$this->return = 'ids';
|
|
$this->set_calc_found_rows( false );
|
|
|
|
$results = $this->get_results();
|
|
|
|
$this->limit = $limit;
|
|
$this->return = $return;
|
|
$this->calc_found_rows = $calc_found_rows;
|
|
|
|
return ! empty( $results );
|
|
}
|
|
}
|
