Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This is a proof of concept showing some objects that WordPress could potentially use within WP_Query to assemble SQL instead of assembling it as strings. If these objects were passed to a new hook just before the SQL query is assembled into a string then hooks could more robustly modify SQL queries. It would take some work to ensure 100% backwar…
<?php
/**
* Proof of concept.
*/
require __DIR__ . '/wp-load.php';
class WP_Sql_Query {
var $type;
var $fields = array();
var $from;
var $joins = array();
var $where;
var $groupby = array();
var $orderby = array();
function __construct( $type, $args = array() ) {
global $wpdb;
$this->type = strtoupper( $type );
foreach( $args as $name => $value )
if ( property_exists( $this, $name ) ) {
if ( is_array( $this->$name ) && is_string( $value ) )
$value = explode( ',', $value );
$this->$name = $value;
}
if ( ! isset( $this->from ) )
$this->from = $wpdb->posts;
}
function __toString() {
/** @var wpdb $wpdb */
global $wpdb;
$sql = false;
switch ( $this->type ) {
case 'SELECT':
$fields = implode( ',', $this->fields );
$joins = implode( ',', $this->joins );
$orderby = ! empty( $this->orderby ) ? ' ORDER BY ' . implode( ',', $this->orderby ) : false;
$sql = sprintf( 'SELECT %s FROM %s %s', esc_sql($fields), esc_sql($this->from), esc_sql($joins) );
if ( ! empty( $this->where ) )
$sql .= " WHERE {$this->where}";
$groupby = ! empty( $this->groupby ) ? ' GROUP BY ' . implode( ',', $this->groupby ) : false;
$having = ! empty( $this->having ) ? " HAVING {$this->having}" : false;
$sql .= sprintf( '%s%s', esc_sql($having), esc_sql($orderby) );
break;
case 'UPDATE':
case 'INSERT':
case 'DELETE':
// @todo
}
return $sql;
}
}
class WP_Sql_Like {
var $before;
var $data;
var $after;
function __construct( $before, $data, $after ) {
$this->before = $before;
$this->data = $data;
$this->after = $after;
}
function __toString() {
/** @var wpdb $wpdb */
global $wpdb;
if ( is_array( $this->data ) )
$this->data = implode( '%', $this->data );
$sql =
( ! empty( $this->before ) ? '%' : false ) .
esc_sql( like_escape( $this->data ) ) .
( ! empty( $this->after ) ? '%' : false );
return "'$sql'";
}
}
class WP_Sql_Literal {
var $literal;
function __construct( $literal ) {
$this->literal = $literal;
}
function __toString() {
/** @var wpdb $wpdb */
global $wpdb;
$sql = $wpdb->prepare( "'%s'", $this->literal );
return $sql;
}
}
class WP_Sql_Join {
var $type;
var $table;
var $expression;
function __construct( $type, $table, $expression ) {
$this->type = $type;
$this->table = $table;
if ( $expression )
$this->expression = $expression;
}
function __toString() {
global $wpdb;
$sql = sprintf( '%s JOIN %s ON %s', esc_sql( $this->type ), esc_sql( $this->table ), esc_sql( $this->expression ) );
return $sql;
}
}
class WP_Sql_Expr {
var $args = array();
function __construct() {
$this->args = func_get_args();
}
function __toString() {
global $wpdb;
$args = $this->args;
foreach( $args as $index => $arg ) {
if ( is_string( $arg ) ) {
$args[$index] = esc_sql( $arg );
}
}
array_unshift( $args, implode( ' ', array_fill( 0, count( $args ), '%s' ) ) );
$sql = '(' . call_user_func_array( 'sprintf', $args ) . ')';
return $sql;
}
}
/**
* Class Sql - Convenience class
*/
class Sql {
/**
* Query
*/
static function Q( $type, $args = array() ) {
return new WP_Sql_Query( $type, $args );
}
/**
* Expression
*/
static function E() {
$class = new ReflectionClass( 'WP_Sql_Expr' );
return $class->newInstanceArgs( func_get_args() );
}
/**
* Join
*/
static function J( $type, $table, $expression ) {
return new WP_Sql_Join( $type, $table, $expression );
}
/**
* Literal
*/
static function L( $literal ) {
return new WP_Sql_Literal( $literal );
}
/**
* Like
*/
static function Lk( $before, $data, $after ) {
return new WP_Sql_Like( $before, $data, $after );
}
}
/**
* There's almost no benefit to doing this instead of writing literal SQL queries.
* BUT, if WP_Query were to use this internally and pass the objects to new hooks
* compared with the we'd be able to write more robust
* hooks that modify the SQL output.
*/
global $wpdb;
$sql = Sql::Q( 'SELECT', array(
'fields' => 'ID,post_name,post_title',
'from' => $wpdb->posts,
'joins' => array( Sql::J( 'INNER', $wpdb->postmeta, Sql::E( 'ID', '=', 'post_id' ) ) ),
'where' => Sql::E( 'post_type', '=', Sql::L('post'),
'AND', Sql::E( 'post_status', '=', Sql::L( 'publish' ), 'OR', 'post_title', 'LIKE', Sql::Lk( '%','Special','%' ) ) ),
'orderby' => 'post_title'
));
echo $sql;
@tomjn

This comment has been minimized.

Copy link

tomjn commented Apr 23, 2014

Any particular reason for $class = new ReflectionClass( 'WP_Sql_Expr' ); ? Would it not be better to just do a direct $expr = new WP_Sql_Expr( func_get_args() ); ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.