Compare WP_Query SQL

compare-wp-query-sql.php

<?php
/*
Plugin Name: Compare WP_Query SQL
Version: 1.0
Description: Compare SQL generated by WP_Query between WordPress versions.
Author: scribu
Author URI: http://scribu.net/


Copyright (C) 2011 Cristi Burcă (scribu@gmail.com)

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 3 of the License, or
( at your option ) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>.
*/

class Compare_WP_Query_SQL {

	function admin_notices() {
		if ( !defined( 'WP_QUERY_DEBUG' ) )
			return;

		if ( 'old' == WP_QUERY_DEBUG )
			self::store_results();
		elseif ( 'new' == WP_QUERY_DEBUG )
			self::compare_results();
	}

	static $cases = array(
		array( 'cat' => -1 ),
		array( 'tag' => 'foo', 'cat' => 'bar' ),
	);

	static $results = array();

	function store_results() {
		self::capture_sql( self::$cases );

		$file = WP_QUERY_DEBUG_RESULTS_PATH;
		$output = '<?php $previous_results = ' . var_export( self::$results, true ) . ';';

		$r = file_put_contents( $file, $output );

		if ( false !== $r )
			echo "<div class='updated'><p>Wrote WP_Query results to <code>$file</code>.</p></div>";
		else
			echo "<div class='error'><p>Failed writing WP_Query results to <code>$file</code>.</p></div>";
	}

	function compare_results() {
		$file = WP_QUERY_DEBUG_RESULTS_PATH;

		if ( !file_exists( $file ) ) {
			echo "<div class='error'><p>Can't find WP_Query results file: <code>$file</code>.</p></div>";
			return;	
		}

		include $file;

		echo "<div class='updated'><p>Comparing WP_Query results:</p>";
		echo "<pre style='overflow-x: scroll'>";

		self::capture_sql( wp_list_pluck( $previous_results, 'args' ) );

		foreach ( $previous_results as $key => $old_result ) {
			$args = $old_result['args'];

			$old_sql = $old_result['sql'];			
			$new_sql = self::$results[$key]['sql'];

			if ( $old_sql != $new_sql ) {
				echo "Different:\n";

				echo "Args:\n";
				print_r( $args );

				echo "\n";

				echo "Old:\n";
				echo $old_sql . "\n";

				echo "\n";

				echo "New:\n";
				echo $new_sql . "\n";
			}
		}

		echo "</pre>";
		echo "</div>";
	}

	function capture_sql( $cases ) {
		add_filter( 'posts_clauses', array( __CLASS__, 'posts_clauses' ), 10, 2 );
		add_filter( 'posts_request', array( __CLASS__, 'posts_request' ), 10, 2 );

		foreach ( $cases as $case ) {
			new WP_Query( $case );
		}

		remove_filter( 'posts_clauses', array( __CLASS__, 'posts_clauses' ), 10, 2 );
		remove_filter( 'posts_request', array( __CLASS__, 'posts_request' ), 10, 2 );
	}

	function posts_clauses( $clauses, $wp_query ) {
		ksort( $wp_query->query );

		$key = md5( serialize( $wp_query->query ) );

		self::$results[ $key ]['args'] = $wp_query->query;
		self::$results[ $key ]['clauses'] = $clauses;

		return $clauses;
	}

	function posts_request( $sql, $wp_query ) {
		ksort( $wp_query->query );

		$key = md5( serialize( $wp_query->query ) );

		self::$results[ $key ]['args'] = $wp_query->query;
		self::$results[ $key ]['sql'] = $sql;

		return false;	// don't actually need to run the query
	}
}

add_action( 'admin_notices', array( 'Compare_WP_Query_SQL', 'admin_notices' ) );