/classes/PodsData.php

Large files files are truncated, but you can click here to view the full file

<?php
/**
 * @package Pods
 */
class PodsData {

    // base
    /**
     * @var string
     */
    static protected $prefix = 'pods_';

    /**
     * @var array
     */
    static protected $field_types = array();

    /**
     * @var bool
     */
    public static $display_errors = true;

    /**
     * @var PodsAPI
     */
    public $api = null;

    /**
     * @var null
     */
    public $select = null;

    /**
     * @var null
     */
    public $table = null;

    /**
     * @var null
     */
    public $pod = null;

    /**
     * @var array|bool|mixed|null|void
     */
    public $pod_data = null;

    /**
     * @var int
     */
    public $id = 0;

    /**
     * @var string
     */
    public $field_id = 'id';

    /**
     * @var string
     */
    public $field_index = 'name';

    /**
     * @var string
     */
    public $field_slug = '';

    /**
     * @var string
     */
    public $join = '';

    /**
     * @var array
     */
    public $where = array();

    /**
     * @var array
     */
    public $where_default = array();

    /**
     * @var string
     */
    public $orderby = '';

    /**
     * @var array
     */
    public $fields = array();

    /**
     * @var array
     */
    public $aliases = array();

    /**
     * @var
     */
    public $detail_page;

    // data
    /**
     * @var int
     */
    public $row_number = -1;

    /**
     * @var
     */
    public $data;

    /**
     * @var
     */
    public $row;

    /**
     * @var
     */
    public $insert_id;

    /**
     * @var
     */
    public $total;

    /**
     * @var
     */
    public $total_found;

    /**
     * @var bool
     */
    public $total_found_calculated;

    // pagination
    /**
     * @var string
     */
    public $page_var = 'pg';

    /**
     * @var int
     */
    public $page = 1;

    /**
     * @var int
     */
    public $limit = 15;

    /**
     * @var bool
     */
    public $pagination = true;

    // search
    /**
     * @var bool
     */
    public $search = true;

    /**
     * @var string
     */
    public $search_var = 'search';

    /**
     * @var string
     */
    public $search_mode = 'int'; // int | text | text_like
    /**
     * @var string
     */
    public $search_query = '';

    /**
     * @var array
     */
    public $search_fields = array();

    /**
     * @var string
     */
    public $search_where = array();

    /**
     * @var array
     */
    public $filters = array();

    /**
     * Holds Traversal information about Pods
     *
     * @var array
     */
    public $traversal = array();

    /**
     * Holds custom Traversals to be included
     *
     * @var array
     */
    public $traverse = array();

    /**
     * Last select() query SQL
     *
     * @var string
     */
    public $sql = false;

    /**
     * Last total sql
     *
     * @var string
     */
    public $total_sql = false;

    /**
     * Data Abstraction Class for Pods
     *
     * @param string $pod Pod name
     * @param integer $id Pod Item ID
     * @param bool $strict If true throws an error if a pod is not found.
     *
     * @license http://www.gnu.org/licenses/gpl-2.0.html
     * @since 2.0.0
     */
    public function __construct ( $pod = null, $id = 0, $strict = true ) {
        global $wpdb;

        if ( is_object( $pod ) && 'PodsAPI' == get_class( $pod ) ) {
            $this->api = $pod;
            $pod = $this->api->pod;
        }
        else
            $this->api = pods_api( $pod );

        $this->api->display_errors =& self::$display_errors;

        if ( !empty( $pod ) ) {
            $this->pod_data =& $this->api->pod_data;

            if ( false === $this->pod_data ) {
                if ( true === $strict )
                    return pods_error( 'Pod not found', $this );
                else
                    return $this;
            }

            $this->pod_id = $this->pod_data[ 'id' ];
            $this->pod = $this->pod_data[ 'name' ];
            $this->fields = $this->pod_data[ 'fields' ];

            if ( isset( $this->pod_data[ 'options' ][ 'detail_url' ] ) )
                $this->detail_page = $this->pod_data[ 'options' ][ 'detail_url' ];

            if ( isset( $this->pod_data[ 'select' ] ) )
                $this->select = $this->pod_data[ 'select' ];

            if ( isset( $this->pod_data[ 'table' ] ) )
                $this->table = $this->pod_data[ 'table' ];

            if ( isset( $this->pod_data[ 'join' ] ) )
                $this->join = $this->pod_data[ 'join' ];

            if ( isset( $this->pod_data[ 'field_id' ] ) )
                $this->field_id = $this->pod_data[ 'field_id' ];

            if ( isset( $this->pod_data[ 'field_index' ] ) )
                $this->field_index = $this->pod_data[ 'field_index' ];

            if ( isset( $this->pod_data[ 'field_slug' ] ) )
                $this->field_slug = $this->pod_data[ 'field_slug' ];

            if ( isset( $this->pod_data[ 'where' ] ) )
                $this->where = $this->pod_data[ 'where' ];

            if ( isset( $this->pod_data[ 'where_default' ] ) )
                $this->where_default = $this->pod_data[ 'where_default' ];

            if ( isset( $this->pod_data[ 'orderby' ] ) )
                $this->orderby = $this->pod_data[ 'orderby' ];

            if ( null !== $id && !is_array( $id ) && !is_object( $id ) ) {
                $this->id = $id;

                $this->fetch( $this->id );
            }
        }
    }

    /**
     * Insert an item, eventually mapping to WPDB::insert
     *
     * @param string $table Table name
     * @param array $data Data to insert (in column => value pairs). Both $data columns and $data values should be "raw" (neither should be SQL escaped).
     * @param array $format (optional) An array of formats to be mapped to each of the value in $data.
     *
     * @return int|bool The ID of the item
     *
     * @uses wpdb::insert
     *
     * @since 2.0.0
     */
    public function insert ( $table, $data, $format = null ) {
        /**
         * @var $wpdb wpdb
         */
        global $wpdb;

        if ( strlen( $table ) < 1 || empty( $data ) || !is_array( $data ) )
            return false;

        if ( empty( $format ) ) {
            $format = array();

            foreach ( $data as $field ) {
                if ( isset( self::$field_types[ $field ] ) )
                    $format[] = self::$field_types[ $field ];
                elseif ( isset( $wpdb->field_types[ $field ] ) )
                    $format[] = $wpdb->field_types[ $field ];
                else
                    break;
            }
        }

        list( $table, $data, $format ) = $this->do_hook( 'insert', array( $table, $data, $format ) );

        $result = $wpdb->insert( $table, $data, $format );
        $this->insert_id = $wpdb->insert_id;

        if ( false !== $result )
            return $this->insert_id;

        return false;
    }

    /**
     * @static
     *
     * Insert into a table, if unique key exists just update values.
     *
     * Data must be a key value pair array, keys act as table rows.
     *
     * Returns the prepared query from wpdb or false for errors
     *
     * @param string $table Name of the table to update
     * @param array $data column => value pairs
     * @param array $formats For $wpdb->prepare, uses sprintf formatting
     *
     * @return mixed Sanitized query string
     *
     * @uses wpdb::prepare
     *
     * @since 2.0.0
     */
    public static function insert_on_duplicate ( $table, $data, $formats = array() ) {
        /**
         * @var $wpdb wpdb
         */
        global $wpdb;

        $columns = array_keys( $data );

        $update = array();
        $values = array();

        foreach ( $columns as $column ) {
            $update[] = "`{$column}` = VALUES( `{$column}` )";
            $values[] = "%s";
        }

        if ( empty( $formats ) )
            $formats = $values;

        $columns_data = implode( '`, `', $columns );
        $formats = implode( ", ", $formats );
        $update = implode( ', ', $update );

        $sql = "INSERT INTO `{$table}` ( `{$columns_data}` ) VALUES ( {$formats} ) ON DUPLICATE KEY UPDATE {$update}";

        return $wpdb->prepare( $sql, $data );
    }

    /**
     * Update an item, eventually mapping to WPDB::update
     *
     * @param string $table Table name
     * @param array $data Data to update (in column => value pairs). Both $data columns and $data values should be "raw" (neither should be SQL escaped).
     * @param array $where A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs. Both $where columns and $where values should be "raw".
     * @param array $format (optional) An array of formats to be mapped to each of the values in $data.
     * @param array $where_format (optional) An array of formats to be mapped to each of the values in $where.
     *
     * @return bool
     * @since 2.0.0
     */
    public function update ( $table, $data, $where, $format = null, $where_format = null ) {
        /**
         * @var $wpdb wpdb
         */
        global $wpdb;

        if ( strlen( $table ) < 1 || empty( $data ) || !is_array( $data ) )
            return false;

        if ( empty( $format ) ) {
            $format = array();

            foreach ( $data as $field ) {
                if ( isset( self::$field_types[ $field ] ) )
                    $form = self::$field_types[ $field ];
                elseif ( isset( $wpdb->field_types[ $field ] ) )
                    $form = $wpdb->field_types[ $field ];
                else
                    $form = '%s';

                $format[] = $form;
            }
        }

        if ( empty( $where_format ) ) {
            $where_format = array();

            foreach ( (array) array_keys( $where ) as $field ) {
                if ( isset( self::$field_types[ $field ] ) )
                    $form = self::$field_types[ $field ];
                elseif ( isset( $wpdb->field_types[ $field ] ) )
                    $form = $wpdb->field_types[ $field ];
                else
                    $form = '%s';

                $where_format[] = $form;
            }
        }

        list( $table, $data, $where, $format, $where_format ) = $this->do_hook( 'update', array(
            $table,
            $data,
            $where,
            $format,
            $where_format
        ) );

        $result = $wpdb->update( $table, $data, $where, $format, $where_format );

        if ( false !== $result )
            return true;

        return false;
    }

    /**
     * Delete an item
     *
     * @param string $table Table name
     * @param array $where A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs. Both $where columns and $where values should be "raw".
     * @param array $where_format (optional) An array of formats to be mapped to each of the values in $where.
     *
     * @return array|bool|mixed|null|void
     *
     * @uses PodsData::query
     * @uses PodsData::prepare
     *
     * @since 2.0.0
     */
    public function delete ( $table, $where, $where_format = null ) {
        /**
         * @var $wpdb wpdb
         */
        global $wpdb;

        if ( strlen( $table ) < 1 || empty( $where ) || !is_array( $where ) )
            return false;

        $wheres = array();
        $where_formats = $where_format = (array) $where_format;

        foreach ( (array) array_keys( $where ) as $field ) {
            if ( !empty( $where_format ) )
                $form = ( $form = array_shift( $where_formats ) ) ? $form : $where_format[ 0 ];
            elseif ( isset( self::$field_types[ $field ] ) )
                $form = self::$field_types[ $field ];
            elseif ( isset( $wpdb->field_types[ $field ] ) )
                $form = $wpdb->field_types[ $field ];
            else
                $form = '%s';

            $wheres[] = "`{$field}` = {$form}";
        }

        $sql = "DELETE FROM `$table` WHERE " . implode( ' AND ', $wheres );

        list( $sql, $where ) = $this->do_hook( 'delete', array(
            $sql,
            array_values( $where )
        ), $table, $where, $where_format, $wheres );

        return $this->query( self::prepare( $sql, $where ) );
    }

    /**
     * Select items, eventually building dynamic query
     *
     * @param array $params
     *
     * @return array|bool|mixed
     * @since 2.0.0
     */
    public function select ( $params ) {
        $cache_key = $results = false;

        // Debug purposes
        if ( 1 == pods_var( 'pods_debug_params', 'get', 0 ) && is_user_logged_in() && ( is_super_admin() || current_user_can( 'delete_users' ) || current_user_can( 'pods' ) ) )
            pods_debug( $params );

        // Get from cache if enabled
        if ( null !== pods_var( 'expires', $params, null, null, true ) ) {
            $cache_key = md5( serialize( get_object_vars( $params ) ) );

            $results = pods_view_get( $cache_key, pods_var( 'cache_mode', $params, 'cache', null, true ), 'pods_data_select' );

            if ( empty( $results ) )
                $results = false;
        }

        if ( empty( $results ) ) {
            // Build
            $this->sql = $this->build( $params );

            // Debug purposes
            if ( ( 1 == pods_var( 'pods_debug_sql', 'get', 0 ) || 1 == pods_var( 'pods_debug_sql_all', 'get', 0 ) ) && is_user_logged_in() && ( is_super_admin() || current_user_can( 'delete_users' ) || current_user_can( 'pods' ) ) )
                echo "<textarea cols='100' rows='24'>{$this->sql}</textarea>";

            if ( empty( $this->sql ) )
                return array();

            // Get Data
            $results = pods_query( $this->sql, $this );

            // Cache if enabled
            if ( false !== $cache_key )
                pods_view_set( $cache_key, $results, pods_var( 'expires', $params, 0, null, true ), pods_var( 'cache_mode', $params, 'cache', null, true ), 'pods_data_select' );
        }

        $results = $this->do_hook( 'select', $results );

        $this->data = $results;

        $this->row_number = -1;

        // Fill in empty field data (if none provided)
        if ( ( !isset( $this->fields ) || empty( $this->fields ) ) && !empty( $this->data ) ) {
            $this->fields = array();
            $data = (array) @current( $this->data );

            foreach ( $data as $data_key => $data_value ) {
                $this->fields[ $data_key ] = array( 'label' => ucwords( str_replace( '-', ' ', str_replace( '_', ' ', $data_key ) ) ) );
            }

            $this->fields = PodsForm::fields_setup( $this->fields );
        }
        $this->total_found_calculated = false;

        $this->total = count( (array) $this->data );

        return $this->data;
    }

    public function calculate_totals () {
        /**
         * @var $wpdb wpdb
         */
        global $wpdb;

        // Set totals
        if ( false !== $this->total_sql )
            $total = @current( $wpdb->get_col( $this->get_sql( $this->total_sql ) ) );
        else
            $total = @current( $wpdb->get_col( "SELECT FOUND_ROWS()" ) );

        $total = $this->do_hook( 'select_total', $total );
        $this->total_found = 0;
        $this->total_found_calculated = true;

        if ( is_numeric( $total ) )
            $this->total_found = $total;
    }

    /**
     * Build/Rewrite dynamic SQL and handle search/filter/sort
     *
     * @param array $params
     *
     * @return bool|mixed|string
     * @since 2.0.0
     */
    public function build ( $params ) {
        $simple_tableless_objects = PodsForm::field_method( 'pick', 'simple_objects' );

        $defaults = array(
            'select' => '*',
            'distinct' => true,
            'table' => null,
            'join' => null,
            'where' => null,
            'groupby' => null,
            'having' => null,
            'orderby' => null,
            'limit' => -1,
            'offset' => null,

            'id' => null,
            'index' => null,

            'page' => 1,
            'pagination' => $this->pagination,
            'search' => $this->search,
            'search_query' => null,
            'search_mode' => null,
            'search_across' => false,
            'search_across_picks' => false,
            'search_across_files' => false,
            'filters' => array(),

            'fields' => array(),
            'traverse' => array(),

            'sql' => null,

            'strict' => false
        );

        $params = (object) array_merge( $defaults, (array) $params );

        if ( 0 < strlen( $params->sql ) )
            return $params->sql;

        // Validate
        $params->page = pods_absint( $params->page );

        $params->pagination = (boolean) $params->pagination;

        if ( 0 == $params->page || !$params->pagination )
            $params->page = 1;

        $params->limit = (int) $params->limit;

        if ( 0 == $params->limit )
            $params->limit = -1;

        $this->limit = $params->limit;

        $offset = ( $params->limit * ( $params->page - 1 ) );

        if ( 0 < (int) $params->offset )
            $params->offset += $offset;
        else
            $params->offset = $offset;

        if ( !$params->pagination || -1 == $params->limit )
            $params->offset = 0;

        if ( ( empty( $params->fields ) || !is_array( $params->fields ) ) && is_array( $this->pod_data ) && isset( $this->fields ) && !empty( $this->fields ) )
            $params->fields = $this->fields;

        if ( empty( $params->index ) )
            $params->index = $this->field_index;

        if ( empty( $params->id ) )
            $params->id = $this->field_id;

        if ( empty( $params->table ) && is_array( $this->pod_data ) && isset( $this->table ) && !empty( $this->table ) )
            $params->table = $this->table;

        if ( empty( $params->table ) )
            return false;

        if ( false === strpos( $params->table, '(' ) && false === strpos( $params->table, '`' ) )
            $params->table = '`' . $params->table . '`';

        if ( !empty( $params->join ) )
            $params->join = array_merge( (array) $this->join, (array) $params->join );
        elseif ( false === $params->strict )
            $params->join = $this->join;

        if ( false === $params->strict && !empty( $this->where ) ) {
            if ( empty( $params->where ) && !empty( $this->where_default ) )
                $params->where = array_values( (array) $this->where_default );

            $params->where = array_merge( (array) $params->where, array_values( (array) $this->where ) );
        }

        // Allow where array ( 'field' => 'value' ) and WP_Query meta_query syntax
        $params->where = $this->query_fields( (array) $params->where, $this->pod_data );

        if ( empty( $params->where ) )
            $params->where = array();


        // Allow having array ( 'field' => 'value' ) and WP_Query meta_query syntax
        $params->having = $this->query_fields( (array) $params->having, $this->pod_data );

        if ( empty( $params->having ) )
            $params->having = array();


        if ( !empty( $params->orderby ) )
            $params->orderby = (array) $params->orderby;
        else
            $params->orderby = array();

        if ( false === $params->strict && !empty( $this->orderby ) )
            $params->orderby = array_merge( $params->orderby, (array) $this->orderby );

        if ( !empty( $params->traverse ) )
            $this->traverse = $params->traverse;

        $allowed_search_modes = array( 'int', 'text', 'text_like' );

        if ( !empty( $params->search_mode ) && in_array( $params->search_mode, $allowed_search_modes ) )
            $this->search_mode = $params->search_mode;

        $params->search = (boolean) $params->search;

        // Get Aliases for future reference
        $selectsfound = '';

        if ( !empty( $params->select ) ) {
            if ( is_array( $params->select ) )
                $selectsfound = implode( ', ', $params->select );
            else
                $selectsfound = $params->select;
        }

        // Pull Aliases from SQL query too
        if ( null !== $params->sql ) {
            $temp_sql = ' ' . trim( str_replace( array( "\n", "\r" ), ' ', $params->sql ) );
            $temp_sql = preg_replace( array(
                    '/\sSELECT\sSQL_CALC_FOUND_ROWS\s/i',
                    '/\sSELECT\s/i'
                ),
                array(
                    ' SELECT ',
                    ' SELECT SQL_CALC_FOUND_ROWS '
                ),
                $temp_sql );
            preg_match( '/\sSELECT SQL_CALC_FOUND_ROWS\s(.*)\sFROM/i', $temp_sql, $selectmatches );
            if ( isset( $selectmatches[ 1 ] ) && !empty( $selectmatches[ 1 ] ) && false !== stripos( $selectmatches[ 1 ], ' AS ' ) )
                $selectsfound .= ( !empty( $selectsfound ) ? ', ' : '' ) . $selectmatches[ 1 ];
        }

        // Build Alias list
        $this->aliases = array();

        if ( !empty( $selectsfound ) && false !== stripos( $selectsfound, ' AS ' ) ) {
            $theselects = array_filter( explode( ', ', $selectsfound ) );

            if ( empty( $theselects ) )
                $theselects = array_filter( explode( ',', $selectsfound ) );

            foreach ( $theselects as $selected ) {
                $selected = trim( $selected );

                if ( strlen( $selected ) < 1 )
                    continue;

                $selectfield = explode( ' AS ', str_replace( ' as ', ' AS ', $selected ) );

                if ( 2 == count( $selectfield ) ) {
                    $field = trim( trim( $selectfield[ 1 ] ), '`' );
                    $real_field = trim( trim( $selectfield[ 0 ] ), '`' );
                    $this->aliases[ $field ] = $real_field;
                }
            }
        }

        // Search
        if ( !empty( $params->search ) && !empty( $params->fields ) ) {
            if ( false !== $params->search_query && 0 < strlen( $params->search_query ) ) {
                $where = $having = array();

                if ( false !== $params->search_across ) {
                    foreach ( $params->fields as $key => $field ) {
                        if ( is_array( $field ) ) {
                            $attributes = $field;
                            $field = pods_var( 'name', $field, $key, null, true );
                        }
                        else {
                            $attributes = array(
                                'type' => '',
                                'options' => array()
                            );
                        }

                        if ( isset( $attributes[ 'options' ][ 'search' ] ) && !$attributes[ 'options' ][ 'search' ] )
                            continue;

                        if ( in_array( $attributes[ 'type' ], array( 'date', 'time', 'datetime', 'number', 'decimal', 'currency', 'phone', 'password', 'boolean' ) ) )
                            continue;

                        if ( in_array( $field, $params->filters ) )
                            continue;

                        $fieldfield = '`' . $field . '`';

                        if ( 'pick' == $attributes[ 'type' ] && !in_array( pods_var( 'pick_object', $attributes ), $simple_tableless_objects ) ) {
                            if ( false === $params->search_across_picks )
                                continue;
                            else {
                                if ( !isset( $attributes[ 'table_info' ] ) || empty( $attributes[ 'table_info' ] ) )
                                    $attributes[ 'table_info' ] = $this->api->get_table_info( pods_var( 'pick_object', $attributes ), pods_var( 'pick_val', $attributes ) );

                                if ( empty( $attributes[ 'table_info' ][ 'field_index' ] ) )
                                    continue;

                                $fieldfield = $fieldfield . '.`' . $attributes[ 'table_info' ][ 'field_index' ] . '`';
                            }
                        }
                        elseif ( in_array( $attributes[ 'type' ], apply_filters( 'pods_file_field_types', array( 'file', 'avatar' ) ) ) ) {
                            if ( false === $params->search_across_files )
                                continue;
                            else
                                $fieldfield = $fieldfield . '.`post_title`';
                        }
                        else
                            $fieldfield = '`t`.' . $fieldfield;

                        if ( isset( $this->aliases[ $field ] ) )
                            $fieldfield = '`' . $this->aliases[ $field ] . '`';

                        if ( isset( $attributes[ 'real_name' ] ) && !empty( $attributes[ 'real_name' ] ) )
                            $fieldfield = $attributes[ 'real_name' ];

                        if ( isset( $attributes[ 'group_related' ] ) && false !== $attributes[ 'group_related' ] )
                            $having[] = "{$fieldfield} LIKE '%" . pods_sanitize( $params->search_query ) . "%'";
                        else
                            $where[] = "{$fieldfield} LIKE '%" . pods_sanitize( $params->search_query ) . "%'";
                    }
                }
                elseif ( !empty( $params->index ) ) {
                    $attributes = array();

                    if ( isset( $params->fields[ $params->index ] ) )
                        $attributes = $params->fields[ $params->index ];

                    $fieldfield = '`t`.`' . $params->index . '`';

                    if ( isset( $attributes[ 'real_name' ] ) && false !== $attributes[ 'real_name' ] && !empty( $attributes[ 'real_name' ] ) )
                        $fieldfield = $attributes[ 'real_name' ];

                    if ( isset( $attributes[ 'group_related' ] ) && false !== $attributes[ 'group_related' ] )
                        $having[] = "{$fieldfield} LIKE '%" . pods_sanitize( $params->search_query ) . "%'";
                    else
                        $where[] = "{$fieldfield} LIKE '%" . pods_sanitize( $params->search_query ) . "%'";
                }

                if ( !empty( $where ) )
                    $params->where[] = '(' . implode( ' OR ', $where ) . ')';

                if ( !empty( $having ) )
                    $params->having[] = '(' . implode( ' OR ', $having ) . ')';
            }

            // Filter
            foreach ( $params->filters as $filter ) {
                $where = $having = array();

                if ( !isset( $params->fields[ $filter ] ) )
                    continue;

                $attributes = $params->fields[ $filter ];
                $field = pods_var( 'name', $attributes, $filter, null, true );

                $filterfield = '`' . $field . '`';

                if ( 'pick' == $attributes[ 'type' ] && !in_array( pods_var( 'pick_object', $attributes ), $simple_tableless_objects ) ) {
                    if ( !isset( $attributes[ 'table_info' ] ) || empty( $attributes[ 'table_info' ] ) )
                        $attributes[ 'table_info' ] = $this->api->get_table_info( pods_var( 'pick_object', $attributes ), pods_var( 'pick_val', $attributes ) );

                    if ( empty( $attributes[ 'table_info' ][ 'field_index' ] ) )
                        continue;

                    $filterfield = $filterfield . '.`' . $attributes[ 'table_info' ][ 'field_index' ] . '`';
                }
                elseif ( in_array( $attributes[ 'type' ], apply_filters( 'pods_file_field_types', array( 'file', 'avatar' ) ) ) )
                    $filterfield = $filterfield . '.`post_title`';
                else
                    $filterfield = '`t`.' . $filterfield;

                if ( isset( $this->aliases[ $field ] ) )
                    $filterfield = '`' . $this->aliases[ $field ] . '`';

                if ( isset( $attributes[ 'real_name' ] ) && false !== $attributes[ 'real_name' ] && !empty( $attributes[ 'real_name' ] ) )
                    $filterfield = $attributes[ 'real_name' ];

                if ( 'pick' == $attributes[ 'type' ] ) {
                    $filter_value = pods_var( 'filter_' . $field, 'get', false, null, true );

                    if ( in_array( pods_var( 'pick_object', $attributes ), $simple_tableless_objects ) ) {
                        if ( strlen( $filter_value ) < 1 )
                            continue;

                        if ( isset( $attributes[ 'group_related' ] ) && false !== $attributes[ 'group_related' ] ) {
                            $having[] = "( {$filterfield} = '" . $filter_value . "'"
                                         . " OR {$filterfield} LIKE '%\"" . $filter_value . "\"%' )";
                        }
                        else {
                            $where[] = "( {$filterfield} = '" . $filter_value . "'"
                                        . " OR {$filterfield} LIKE '%\"" . $filter_value . "\"%' )";
                        }
                    }
                    else {
                        $filter_value = (int) $filter_value;

                        if ( empty( $filter_value ) || empty( $attributes[ 'table_info' ][ 'field_id' ] ) )
                            continue;

                        $filterfield = '`' . $field . '`.`' . $attributes[ 'table_info' ][ 'field_id' ] . '`';

                        if ( isset( $attributes[ 'group_related' ] ) && false !== $attributes[ 'group_related' ] )
                            $having[] = "{$filterfield} = " . $filter_value;
                        else
                            $where[] = "{$filterfield} = " . $filter_value;
                    }
                }
                elseif ( in_array( $attributes[ 'type' ], array( 'date', 'datetime' ) ) ) {
                    $start = date_i18n( 'Y-m-d' ) . ( 'datetime' == $attributes[ 'type' ] ) ? ' 00:00:00' : '';
                    $end = date_i18n( 'Y-m-d' ) . ( 'datetime' == $attributes[ 'type' ] ) ? ' 23:59:59' : '';

                    $start_value = pods_var( 'filter_' . $field . '_start', 'get', false );
                    $end_value = pods_var( 'filter_' . $field . '_end', 'get', false );

                    if ( empty( $start_value ) && empty( $end_value ) )
                        continue;

                    if ( !empty( $start_value ) )
                        $start = date_i18n( 'Y-m-d', strtotime( $start_value ) ) . ( 'datetime' == $attributes[ 'type' ] ? ' 00:00:00' : '' );

                    if ( !empty( $end_value ) )
                        $end = date_i18n( 'Y-m-d', strtotime( $end_value ) ) . ( 'datetime' == $attributes[ 'type' ] ? ' 23:59:59' : '' );

                    if ( true === $attributes[ 'date_ongoing' ] ) {
                        $date_ongoing = '`' . $attributes[ 'date_ongoing' ] . '`';

                        if ( isset( $this->aliases[ $date_ongoing ] ) )
                            $date_ongoing = '`' . $this->aliases[ $date_ongoing ] . '`';

                        if ( isset( $attributes[ 'group_related' ] ) && false !== $attributes[ 'group_related' ] )
                            $having[] = "(({$filterfield} <= '$start' OR ({$filterfield} >= '$start' AND {$filterfield} <= '$end')) AND ({$date_ongoing} >= '$start' OR ({$date_ongoing} >= '$start' AND {$date_ongoing} <= '$end')))";
                        else
                            $where[] = "(({$filterfield} <= '$start' OR ({$filterfield} >= '$start' AND {$filterfield} <= '$end')) AND ({$date_ongoing} >= '$start' OR ({$date_ongoing} >= '$start' AND {$date_ongoing} <= '$end')))";
                    }
                    else {
                        if ( isset( $attributes[ 'group_related' ] ) && false !== $attributes[ 'group_related' ] )
                            $having[] = "({$filterfield} BETWEEN '$start' AND '$end')";
                        else
                            $where[] = "({$filterfield} BETWEEN '$start' AND '$end')";
                    }
                }
                else {
                    $filter_value = pods_var( 'filter_' . $field, 'get', '' );

                    if ( strlen( $filter_value ) < 1 )
                        continue;

                    if ( isset( $attributes[ 'group_related' ] ) && false !== $attributes[ 'group_related' ] )
                        $having[] = "{$filterfield} LIKE '%" . $filter_value . "%'";
                    else
                        $where[] = "{$filterfield} LIKE '%" . $filter_value . "%'";
                }

                if ( !empty( $where ) )
                    $params->where[] = '(' . implode( ' AND ', $where ) . ')';

                if ( !empty( $having ) )
                    $params->having[] = '(' . implode( ' AND ', $having ) . ')';
            }
        }

        // Traverse the Rabbit Hole
        if ( !empty( $this->pod ) ) {
            $haystack = implode( ' ', (array) $params->select )
                        . ' ' . implode( ' ', (array) $params->where )
                        . ' ' . implode( ' ', (array) $params->groupby )
                        . ' ' . implode( ' ', (array) $params->having )
                        . ' ' . implode( ' ', (array) $params->orderby );
            $haystack = preg_replace( '/\s/', ' ', $haystack );
            $haystack = preg_replace( '/\w\(/', ' ', $haystack );
            $haystack = str_replace( array( '(', ')', '  ', '\\\'', "\\\"" ), ' ', $haystack );

            preg_match_all( '/`?[\w]+`?(?:\\.`?[\w]+`?)+(?=[^"\']*(?:"[^"]*"[^"]*|\'[^\']*\'[^\']*)*$)/', $haystack, $found, PREG_PATTERN_ORDER );

            $found = (array) @current( $found );
            $find = $replace = array();

            foreach ( $found as $key => $value ) {
                $value = str_replace( '`', '', $value );
                $value = explode( '.', $value );
                $dot = array_pop( $value );

                if ( in_array( '/\b' . trim( $found[ $key ], '`' ) . '\b(?=[^"\']*(?:"[^"]*"[^"]*|\'[^\']*\'[^\']*)*$)/', $find ) ) {
                    unset( $found[ $key ] );

                    continue;
                }

                $find[ $key ] = '/\b' . trim( $found[ $key ], '`' ) . '\b(?=[^"\']*(?:"[^"]*"[^"]*|\'[^\']*\'[^\']*)*$)/';
                $esc_start = $esc_end = '`';

                if ( strlen( ltrim( $found[ $key ], '`' ) ) < strlen( $found[ $key ] ) )
                    $esc_start = '';

                if ( strlen( rtrim( $found[ $key ], '`' ) ) < strlen( $found[ $key ] ) )
                    $esc_end = '';

                if ( '*' != $dot )
                    $dot = '`' . $dot . $esc_end;

                $replace[ $key ] = $esc_start . implode( '_', $value ) . '`.' . $dot;

                if ( 't' == $value[ 0 ] ) {
                    unset( $found[ $key ] );

                    continue;
                }

                unset( $found[ $key ] );

                if ( !in_array( $value, $found ) )
                    $found[ $key ] = $value;
            }

            if ( !empty( $this->traverse ) ) {
                foreach ( (array) $this->traverse as $key => $traverse ) {
                    $traverse = str_replace( '`', '', $traverse );
                    $already_found = false;

                    foreach ( $found as $traversal ) {
                        if ( is_array( $traversal ) )
                            $traversal = implode( '.', $traversal );

                        if ( $traversal == $traverse ) {
                            $already_found = true;
                            break;
                        }
                    }

                    if ( !$already_found )
                        $found[ 'traverse_' . $key ] = explode( '.', $traverse );
                }
            }

            $joins = array();

            if ( !empty( $find ) ) {
                $params->select = preg_replace( $find, $replace, $params->select );
                $params->where = preg_replace( $find, $replace, $params->where );
                $params->groupby = preg_replace( $find, $replace, $params->groupby );
                $params->having = preg_replace( $find, $replace, $params->having );
                $params->orderby = preg_replace( $find, $replace, $params->orderby );

                if ( !empty( $found ) )
                    $joins = $this->traverse( $found, $params->fields, $params );
                elseif ( false !== $params->search )
                    $joins = $this->traverse( null, $params->fields, $params );
            }
        }

        // Traversal Search
        if ( !empty( $params->search ) && !empty( $this->search_where ) )
            $params->where = array_merge( (array) $this->search_where, $params->where );

        if ( !empty( $params->join ) && !empty( $joins ) )
            $params->join = array_merge( $joins, (array) $params->join );
        elseif ( !empty( $joins ) )
            $params->join = $joins;

        // Build
        if ( null === $params->sql ) {
            $sql = "
                SELECT
                " . ( $params->distinct ? 'DISTINCT' : '' ) . "
                " . ( !empty( $params->select ) ? ( is_array( $params->select ) ? implode( ', ', $params->select ) : $params->select ) : '*' ) . "
                FROM {$params->table} AS `t`
                " . ( !empty( $params->join ) ? ( is_array( $params->join ) ? implode( "\n                ", $params->join ) : $params->join ) : '' ) . "
                " . ( !empty( $params->where ) ? 'WHERE ' . ( is_array( $params->where ) ? implode( ' AND ', $params->where ) : $params->where ) : '' ) . "
                " . ( !empty( $params->groupby ) ? 'GROUP BY ' . ( is_array( $params->groupby ) ? implode( ', ', $params->groupby ) : $params->groupby ) : '' ) . "
                " . ( !empty( $params->having ) ? 'HAVING ' . ( is_array( $params->having ) ? implode( ' AND ', $params->having ) : $params->having ) : '' ) . "
                " . ( !empty( $params->orderby ) ? 'ORDER BY ' . ( is_array( $params->orderby ) ? implode( ', ', $params->orderby ) : $params->orderby ) : '' ) . "
                " . ( ( 0 < $params->page && 0 < $params->limit ) ? 'LIMIT ' . $params->offset . ', ' . ( $params->limit ) : '' ) . "
            ";
            $this->total_sql = "
                SELECT
                " . ( $params->distinct ? 'DISTINCT' : '' ) . "
                COUNT(*)
                FROM {$params->table} AS `t`
                " . ( !empty( $params->join ) ? ( is_array( $params->join ) ? implode( "\n                ", $params->join ) : $params->join ) : '' ) . "
                " . ( !empty( $params->where ) ? 'WHERE ' . ( is_array( $params->where ) ? implode( ' AND ', $params->where ) : $params->where ) : '' ) . "
                " . ( !empty( $params->groupby ) ? 'GROUP BY ' . ( is_array( $params->groupby ) ? implode( ', ', $params->groupby ) : $params->groupby ) : '' ) . "
                " . ( !empty( $params->having ) ? 'HAVING ' . ( is_array( $params->having ) ? implode( ' AND ', $params->having ) : $params->having ) : '' ) . "
            ";
        }
        // Rewrite
        else {
            $sql = ' ' . trim( str_replace( array( "\n", "\r" ), ' ', $params->sql ) );
            $sql = preg_replace( array(
                    '/\sSELECT\sSQL_CALC_FOUND_ROWS\s/i',
                    '/\sSELECT\s/i'
                ),
                array(
                    ' SELECT ',
                    ' SELECT SQL_CALC_FOUND_ROWS '
                ),
                $sql );

            // Insert variables based on existing statements
            if ( false === stripos( $sql, '%%SELECT%%' ) )
                $sql = preg_replace( '/\sSELECT\sSQL_CALC_FOUND_ROWS\s/i', ' SELECT SQL_CALC_FOUND_ROWS %%SELECT%% ', $sql );
            if ( false === stripos( $sql, '%%WHERE%%' ) )
                $sql = preg_replace( '/\sWHERE\s(?!.*\sWHERE\s)/i', ' WHERE %%WHERE%% ', $sql );
            if ( false === stripos( $sql, '%%GROUPBY%%' ) )
                $sql = preg_replace( '/\sGROUP BY\s(?!.*\sGROUP BY\s)/i', ' GROUP BY %%GROUPBY%% ', $sql );
            if ( false === stripos( $sql, '%%HAVING%%' ) )
                $sql = preg_replace( '/\sHAVING\s(?!.*\sHAVING\s)/i', ' HAVING %%HAVING%% ', $sql );
            if ( false === stripos( $sql, '%%ORDERBY%%' ) )
                $sql = preg_replace( '/\sORDER BY\s(?!.*\sORDER BY\s)/i', ' ORDER BY %%ORDERBY%% ', $sql );

            // Insert variables based on other existing statements
            if ( false === stripos( $sql, '%%JOIN%%' ) ) {
                if ( false !== stripos( $sql, ' WHERE ' ) )
                    $sql = preg_replace( '/\sWHERE\s(?!.*\sWHERE\s)/i', ' %%JOIN%% WHERE ', $sql );
                elseif ( false !== stripos( $sql, ' GROUP BY ' ) )
                    $sql = preg_replace( '/\sGROUP BY\s(?!.*\sGROUP BY\s)/i', ' %%WHERE%% GROUP BY ', $sql );
                elseif ( false !== stripos( $sql, ' ORDER BY ' ) )
                    $sql = preg_replace( '/\ORDER BY\s(?!.*\ORDER BY\s)/i', ' %%WHERE%% ORDER BY ', $sql );
                else
                    $sql .= ' %%JOIN%% ';
            }
            if ( false === stripos( $sql, '%%WHERE%%' ) ) {
                if ( false !== stripos( $sql, ' GROUP BY ' ) )
                    $sql = preg_replace( '/\sGROUP BY\s(?!.*\sGROUP BY\s)/i', ' %%WHERE%% GROUP BY ', $sql );
                elseif ( false !== stripos( $sql, ' ORDER BY ' ) )
                    $sql = preg_replace( '/\ORDER BY\s(?!.*\ORDER BY\s)/i', ' %%WHERE%% ORDER BY ', $sql );
                else
                    $sql .= ' %%WHERE%% ';
            }
            if ( false === stripos( $sql, '%%GROUPBY%%' ) ) {
                if ( false !== stripos( $sql, ' HAVING ' ) )
                    $sql = preg_replace( '/\sHAVING\s(?!.*\sHAVING\s)/i', ' %%GROUPBY%% HAVING ', $sql );
                elseif ( false !== stripos( $sql, ' ORDER BY ' ) )
                    $sql = preg_replace( '/\ORDER BY\s(?!.*\ORDER BY\s)/i', ' %%GROUPBY%% ORDER BY ', $sql );
                else
                    $sql .= ' %%GROUPBY%% ';
            }
            if ( false === stripos( $sql, '%%HAVING%%' ) ) {
                if ( false !== stripos( $sql, ' ORDER BY ' ) )
                    $sql = preg_replace( '/\ORDER BY\s(?!.*\ORDER BY\s)/i', ' %%HAVING%% ORDER BY ', $sql );
                else
                    $sql .= ' %%HAVING%% ';
            }
            if ( false === stripos( $sql, '%%ORDERBY%%' ) )
                $sql .= ' %%ORDERBY%% ';
            if ( false === stripos( $sql, '%%LIMIT%%' ) )
                $sql .= ' %%LIMIT%% ';

            // Replace variables
            if ( 0 < strlen( $params->select ) ) {
                if ( false === stripos( $sql, '%%SELECT%% FROM ' ) )
                    $sql = str_ireplace( '%%SELECT%%', $params->select . ', ', $sql );
                else
                    $sql = str_ireplace( '%%SELECT%%', $params->select, $sql );
            }
            if ( 0 < strlen( $params->join ) )
                $sql = str_ireplace( '%%JOIN%%', $params->join, $sql );
            if ( 0 < strlen( $params->where ) ) {
                if ( false !== stripos( $sql, ' WHERE ' ) ) {
                    if ( false !== stripos( $sql, ' WHERE %%WHERE%% ' ) )
                        $sql = str_ireplace( '%%WHERE%%', $params->where . ' AND ', $sql );
                    else
                        $sql = str_ireplace( '%%WHERE%%', ' AND ' . $params->where, $sql );
                }
                else
                    $sql = str_ireplace( '%%WHERE%%', ' WHERE ' . $params->where, $sql );
            }
            if ( 0 < strlen( $params->groupby ) ) {
                if ( false !== stripos( $sql, ' GROUP BY ' ) ) {
                    if ( false !== stripos( $sql, ' GROUP BY %%GROUPBY%% ' ) )
                        $sql = str_ireplace( '%%GROUPBY%%', $params->groupby . ', ', $sql );
                    else
                        $sql = str_ireplace( '%%GROUPBY%%', ', ' . $params->groupby, $sql );
                }
                else
                    $sql = str_ireplace( '%%GROUPBY%%', ' GROUP BY ' . $params->groupby, $sql );
            }
            if ( 0 < strlen( $params->having ) && false !== stripos( $sql, ' GROUP BY ' ) ) {
                if ( false !== stripos( $sql, ' HAVING ' ) ) {
                    if ( false !== stripos( $sql, ' HAVING %%HAVING%% ' ) )
                        $sql = str_ireplace( '%%HAVING%%', $params->having . ' AND ', $sql );
                    else
                        $sql = str_ireplace( '%%HAVING%%', ' AND ' . $params->having, $sql );
                }
                else
                    $sql = str_ireplace( '%%HAVING%%', ' HAVING ' . $params->having, $sql );
            }
            if ( 0 < strlen( $params->orderby ) ) {
                if ( false !== stripos( $sql, ' ORDER BY ' ) ) {
                    if ( false !== stripos( $sql, ' ORDER BY %%ORDERBY%% ' ) )
                        $sql = str_ireplace( '%%ORDERBY%%', $params->groupby . ', ', $sql );
                    else
                        $sql = str_ireplace( '%%ORDERBY%%', ', ' . $params->groupby, $sql );
                }
                else
                    $sql = str_ireplace( '%%ORDERBY%%', ' ORDER BY ' . $params->groupby, $sql );
            }
            if ( 0 < $params->page && 0 < $params->limit ) {
                $start = ( $params->page - 1 ) * $params->limit;
                $end = $start + $params->limit;
                $sql .= 'LIMIT ' . (int) $start . ', ' . (int) $end;
            }

            // Clear any unused variables
            $sql = str_ireplace( array(
                '%%SELECT%%',
                '%%JOIN%%',
                '%%WHERE%%',
                '%%GROUPBY%%',
                '%%HAVING%%',
                '%%ORDERBY%%',
                '%%LIMIT%%'
            ), '', $sql );
            $sql = str_replace( array( '``', '`' ), array( '  ', ' ' ), $sql );
        }

        return $sql;
    }

    /**
     * Fetch the total row count returned
     *
     * @return int Number of rows returned by select()
     * @since 2.0.0
     */
    public function total () {
        return (int) $this->total;
    }

    /**
     * Fetch the total row count total
     *
     * @return int Number of rows found by select()
     * @since 2.0.0
     */
    public function total_found () {
        if(false === $this->total_found_calculated)
            $this->calculate_totals();

        return (int) $this->total_found;
    }

    /**
     * Fetch the zebra switch
     *
     * @return bool Zebra state
     * @since 1.12
     */
    public function zebra () {
        $zebra = true;
        if ( 0 < ( $this->row_number % 2 ) ) // Odd numbers
            $zebra = false;
        return $zebra;
    }

    /**
     * Create a Table
     *
     * @param string $table Table name
     * @param string $fields
     * @param boolean $if_not_exists Check if the table exists.
     *
     * @return array|bool|mixed|null|void
     *
     * @uses PodsData::query
     *
     * @since 2.0.0
     */
    public static function table_create ( $table, $fields, $if_not_exists = false ) {
        /**
         * @var $wpdb wpdb
         */
        global $wpdb;

        $sql = "CREATE TABLE";

        if ( true === $if_not_exists )
            $sql .= " IF NOT EXISTS";

        $sql .= " `{$wpdb->prefix}" . self::$prefix . "{$table}` ({$fields})";

        if ( !empty( $wpdb->charset ) )
            $sql .= " DEFAULT CHARACTER SET {$wpdb->charset}";

        if ( !empty( $wpdb->collate ) )
            $sql .= " COLLATE {$wpdb->collate}";

        return self::query( $sql );
    }

    /**
     * Alter a Table
     *
     * @param string $table Table name
     * @param string $changes
     *
     * @return array|bool|mixed|null|void
     *
     * @uses PodsData::query
     *
     * @since 2.0.0
     */
    public static function table_alter ( $table, $changes ) {
        /**
         * @var $wpdb wpdb
         */
        global $wpdb;

        $sql = "ALTER TABLE `{$wpdb->prefix}" . self::$prefix . "{$table}` {$changes}";

        return self::query( $sql );
    }

    /**
     * Truncate a Table
     *
     * @param string $table Table name
     *
     * @return array|bool|mixed|null|void
     *
     * @uses PodsData::query
     *
     * @since 2.0.0
     */
    public static function table_truncate ( $table ) {
        /**
         * @var $wpdb wpdb
         */
        global $wpdb;

        $sql = "TRUNCATE TABLE `{$wpdb->prefix}" . self::$prefix . "{$table}`";

        return self::query( $sql );
    }

    /**
     * Drop a Table
     *
     * @param string $table Table name
     *
     * @uses PodsData::query
     *
     * @return array|bool|mixed|null|void
     *
     * @uses PodsData::query
     *
     * @since 2.0.0
     */
    public static function table_drop ( $table ) {
        /**
         * @var $wpdb wpdb
         */
        global $wpdb;

        $sql = "DROP TABLE `{$wpdb->prefix}" . self::$prefix . "{$table}`";

        return self::query( $sql );
    }

    /**
     * Reorder Items
     *
     * @param string $table Table name
     * @param string $weight_field
     * @param string $id_field
     * @param array $ids
     *
     * @return bool
     *
     * @uses PodsData::update
     *
     * @since 2.0.0
     */
    public function reorder ( $table, $weight_field, $id_field, $ids ) {
        $success = false;
        $ids = (array) $ids;

        list( $table, $weight_field, $id_field, $ids ) = $this->do_hook( 'reorder', array(
            $table,
            $weight_field,
            $id_field,
            $ids
        ) );

        if ( !empty( $ids ) ) {
            $success = true;

          …