Skip to content

Instantly share code, notes, and snippets.

@nb

nb/class-query-iterator.php

Created November 12, 2012 15:34
Download ZIP

Revisions

  1. nb revised this gist Nov 12, 2012. 1 changed file with 76 additions and 0 deletions.
    76 changes: 76 additions & 0 deletions class-table-iterator.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,76 @@
    <?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;
    }

    }
  2. nb created this gist Nov 12, 2012.
    95 changes: 95 additions & 0 deletions class-query-iterator.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,95 @@
    <?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 {
    }