class-query-iterator.php

<?php
/**
 * Iterates over results of a query, split into many queries via LIMIT and OFFSET
 */
class QueryIterator implements Iterator {

	var $limit = 500;
	var $query = '';

	var $global_index = 0;
	var $index_in_results = 0;
	var $results = array();
	var $offset = 0;
	var $db = null;
	var $depleted = false;

	/**
	 * Creates a new query iterator
	 *
	 * This will loop over all users, but will retrieve them 100 by 100:
	 * <code>
	 * foreach( new QueryIterator( array( 'query' => 'SELECT * FROM users', 'limit' => 100 ) ) as $user ) {
	 *     tickle( $user );
	 * }
	 * </code>
	 *
	 *
	 * @param array $args Supported arguments:
	 *		query – the query as a string. It shouldn't include any LIMIT clauses
	 *		limit – (optional) how many rows to retrieve at once, default value is 500
	 */
	function __construct( $args = array() ) {
		$this->db = $GLOBALS['wpdb'];
		foreach( $args as $key => $value ) {
			$this->$key = $value;
		}
		if ( !$this->query ) {
			throw new InvalidArgumentException( 'Missing query argument.' );
		}
	}

	function load_items_from_db() {
		$query = $this->query . sprintf( ' LIMIT %d OFFSET %d', $this->limit, $this->offset );
		$this->results = $this->db->get_results( $query );
		if ( !$this->results ) {
			if ( $this->db->last_error ) {
				throw new QueryIteratorException( 'Database error: '.$this->db->last_error );
			} else {
				return false;
			}
		}
		$this->offset += $this->limit;
		return true;
	}

	function current() {
		return $this->results[$this->index_in_results];
	}

	function key() {
		return $this->global_index;
	}

	function next() {
		$this->index_in_results++;
		$this->global_index++;
	}

	function rewind() {
		$this->results = array();
		$this->global_index = 0;
		$this->index_in_results = 0;
		$this->offset = 0;
		$this->depleted = false;
	}

	function valid() {
		if ( $this->depleted ) {
			return false;
		}
		if ( !isset( $this->results[$this->index_in_results] ) ) {
			$items_loaded = $this->load_items_from_db();
			if ( !$items_loaded ) {
				$this->rewind();
				$this->depleted = true;
				return false;
			}
			$this->index_in_results = 0;
		}
		return true;
	}
}

class QueryIteratorException extends RuntimeException {
}

class-table-iterator.php

<?php
require_once __DIR__ . '/class-query-iterator.php';

class TableIterator extends QueryIterator {

	/**
	 * Creates an iterator over a database table
	 *
	 * <code>
	 * foreach( new TableIterator( array( 'table' => $wpdb->posts, 'fields' => array( 'ID', 'post_content' ) ) ) as $post ) {
	 *     count_words_for( $post->ID, $post->post_content );
	 * }
	 * </code>
	 *
	 * <code>
	 * foreach( new TableIterator( array( 'table' => $wpdb->posts, 'where' => 'ID = 8 OR post_status = "publish"' ) ) as $post ) {
	 *     …
	 * }
	 * </code>
	 *
	 * <code>
	 * foreach( new PostIterator( array( 'table' => $wpdb->posts, 'where' => array( 'post_status' => 'publish', 'post_date_gmt BETWEEN x AND y' ) ) ) as $post ) {
	 *     …
	 * }
	 * </code>
	 *
	 *
	 * @param array $args Supported arguments:
	 *		table – the name of the database table
	 *		fields – an array of columns to get from the posst table, * is a valid value and the default
	 *		where – conditions for filtering rows. Supports two formats:
	 *			= string – this will be the where clause
	 *			= array – each element is treated as a condition if it's positional, or as column => value if
	 *				it's a key/value pair. In the latter case the value is automatically quoted and escaped
	 */
	function __construct( $args = array() ) {
		global $wpdb;
		$defaults = array(
			'fields' => array( '*' ),
			'where' => array(),
			'table' => null,
		);
		$table = $args['table'];
		$args = array_merge( $defaults, $args );

		$fields = self::build_fields( $args['fields'] );
		$conditions = self::build_where_conditions( $args['where'] );
		$where_sql = $conditions? " WHERE $conditions" : '';
		$query = "SELECT $fields FROM $table $where_sql";
		$parent_args = compact( 'query' );
		if ( isset( $args['limit'] ) ) {
			$parent_args['limit'] = $args['limit'];
		}
		parent::__construct( $parent_args );
	}

	static function build_fields( $fields ) {
		return implode( ', ', $fields );
	}

	static function build_where_conditions( $where ) {
		global $wpdb;
		if ( is_array( $where ) ) {
			$conditions = array();
			foreach( $where as $key => $value ) {
				if ( is_numeric( $key ) )
					$conditions[] = $value;
				else
					$conditions[] = $key . ' = "' . $wpdb->escape( $value ) .'"';
			}
			$where = implode( ' AND ', $conditions );
		}
		return $where;
	}

}