PageRenderTime 455ms CodeModel.GetById 151ms app.highlight 130ms RepoModel.GetById 162ms app.codeStats 1ms

/lib/Zend/Db/Select.php

https://bitbucket.org/andrewjleavitt/magestudy
PHP | 1351 lines | 632 code | 127 blank | 592 comment | 127 complexity | 7060cba80236d7ad77474e344cbc00c8 MD5 | raw file
   1<?php
   2/**
   3 * Zend Framework
   4 *
   5 * LICENSE
   6 *
   7 * This source file is subject to the new BSD license that is bundled
   8 * with this package in the file LICENSE.txt.
   9 * It is also available through the world-wide-web at this URL:
  10 * http://framework.zend.com/license/new-bsd
  11 * If you did not receive a copy of the license and are unable to
  12 * obtain it through the world-wide-web, please send an email
  13 * to license@zend.com so we can send you a copy immediately.
  14 *
  15 * @category   Zend
  16 * @package    Zend_Db
  17 * @subpackage Select
  18 * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
  19 * @license    http://framework.zend.com/license/new-bsd     New BSD License
  20 * @version    $Id: Select.php 23254 2010-10-26 12:49:23Z matthew $
  21 */
  22
  23
  24/**
  25 * @see Zend_Db_Adapter_Abstract
  26 */
  27#require_once 'Zend/Db/Adapter/Abstract.php';
  28
  29/**
  30 * @see Zend_Db_Expr
  31 */
  32#require_once 'Zend/Db/Expr.php';
  33
  34
  35/**
  36 * Class for SQL SELECT generation and results.
  37 *
  38 * @category   Zend
  39 * @package    Zend_Db
  40 * @subpackage Select
  41 * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
  42 * @license    http://framework.zend.com/license/new-bsd     New BSD License
  43 */
  44class Zend_Db_Select
  45{
  46
  47    const DISTINCT       = 'distinct';
  48    const COLUMNS        = 'columns';
  49    const FROM           = 'from';
  50    const UNION          = 'union';
  51    const WHERE          = 'where';
  52    const GROUP          = 'group';
  53    const HAVING         = 'having';
  54    const ORDER          = 'order';
  55    const LIMIT_COUNT    = 'limitcount';
  56    const LIMIT_OFFSET   = 'limitoffset';
  57    const FOR_UPDATE     = 'forupdate';
  58
  59    const INNER_JOIN     = 'inner join';
  60    const LEFT_JOIN      = 'left join';
  61    const RIGHT_JOIN     = 'right join';
  62    const FULL_JOIN      = 'full join';
  63    const CROSS_JOIN     = 'cross join';
  64    const NATURAL_JOIN   = 'natural join';
  65
  66    const SQL_WILDCARD   = '*';
  67    const SQL_SELECT     = 'SELECT';
  68    const SQL_UNION      = 'UNION';
  69    const SQL_UNION_ALL  = 'UNION ALL';
  70    const SQL_FROM       = 'FROM';
  71    const SQL_WHERE      = 'WHERE';
  72    const SQL_DISTINCT   = 'DISTINCT';
  73    const SQL_GROUP_BY   = 'GROUP BY';
  74    const SQL_ORDER_BY   = 'ORDER BY';
  75    const SQL_HAVING     = 'HAVING';
  76    const SQL_FOR_UPDATE = 'FOR UPDATE';
  77    const SQL_AND        = 'AND';
  78    const SQL_AS         = 'AS';
  79    const SQL_OR         = 'OR';
  80    const SQL_ON         = 'ON';
  81    const SQL_ASC        = 'ASC';
  82    const SQL_DESC       = 'DESC';
  83
  84    /**
  85     * Bind variables for query
  86     *
  87     * @var array
  88     */
  89    protected $_bind = array();
  90
  91    /**
  92     * Zend_Db_Adapter_Abstract object.
  93     *
  94     * @var Zend_Db_Adapter_Abstract
  95     */
  96    protected $_adapter;
  97
  98    /**
  99     * The initial values for the $_parts array.
 100     * NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure
 101     * meximum compatibility with database adapters.
 102     *
 103     * @var array
 104     */
 105    protected static $_partsInit = array(
 106        self::DISTINCT     => false,
 107        self::COLUMNS      => array(),
 108        self::UNION        => array(),
 109        self::FROM         => array(),
 110        self::WHERE        => array(),
 111        self::GROUP        => array(),
 112        self::HAVING       => array(),
 113        self::ORDER        => array(),
 114        self::LIMIT_COUNT  => null,
 115        self::LIMIT_OFFSET => null,
 116        self::FOR_UPDATE   => false
 117    );
 118
 119    /**
 120     * Specify legal join types.
 121     *
 122     * @var array
 123     */
 124    protected static $_joinTypes = array(
 125        self::INNER_JOIN,
 126        self::LEFT_JOIN,
 127        self::RIGHT_JOIN,
 128        self::FULL_JOIN,
 129        self::CROSS_JOIN,
 130        self::NATURAL_JOIN,
 131    );
 132
 133    /**
 134     * Specify legal union types.
 135     *
 136     * @var array
 137     */
 138    protected static $_unionTypes = array(
 139        self::SQL_UNION,
 140        self::SQL_UNION_ALL
 141    );
 142
 143    /**
 144     * The component parts of a SELECT statement.
 145     * Initialized to the $_partsInit array in the constructor.
 146     *
 147     * @var array
 148     */
 149    protected $_parts = array();
 150
 151    /**
 152     * Tracks which columns are being select from each table and join.
 153     *
 154     * @var array
 155     */
 156    protected $_tableCols = array();
 157
 158    /**
 159     * Class constructor
 160     *
 161     * @param Zend_Db_Adapter_Abstract $adapter
 162     */
 163    public function __construct(Zend_Db_Adapter_Abstract $adapter)
 164    {
 165        $this->_adapter = $adapter;
 166        $this->_parts = self::$_partsInit;
 167    }
 168
 169    /**
 170     * Get bind variables
 171     *
 172     * @return array
 173     */
 174    public function getBind()
 175    {
 176        return $this->_bind;
 177    }
 178
 179    /**
 180     * Set bind variables
 181     *
 182     * @param mixed $bind
 183     * @return Zend_Db_Select
 184     */
 185    public function bind($bind)
 186    {
 187        $this->_bind = $bind;
 188
 189        return $this;
 190    }
 191
 192    /**
 193     * Makes the query SELECT DISTINCT.
 194     *
 195     * @param bool $flag Whether or not the SELECT is DISTINCT (default true).
 196     * @return Zend_Db_Select This Zend_Db_Select object.
 197     */
 198    public function distinct($flag = true)
 199    {
 200        $this->_parts[self::DISTINCT] = (bool) $flag;
 201        return $this;
 202    }
 203
 204    /**
 205     * Adds a FROM table and optional columns to the query.
 206     *
 207     * The first parameter $name can be a simple string, in which case the
 208     * correlation name is generated automatically.  If you want to specify
 209     * the correlation name, the first parameter must be an associative
 210     * array in which the key is the physical table name, and the value is
 211     * the correlation name.  For example, array('table' => 'alias').
 212     * The correlation name is prepended to all columns fetched for this
 213     * table.
 214     *
 215     * The second parameter can be a single string or Zend_Db_Expr object,
 216     * or else an array of strings or Zend_Db_Expr objects.
 217     *
 218     * The first parameter can be null or an empty string, in which case
 219     * no correlation name is generated or prepended to the columns named
 220     * in the second parameter.
 221     *
 222     * @param  array|string|Zend_Db_Expr $name The table name or an associative array relating table name to
 223     *                                         correlation name.
 224     * @param  array|string|Zend_Db_Expr $cols The columns to select from this table.
 225     * @param  string $schema The schema name to specify, if any.
 226     * @return Zend_Db_Select This Zend_Db_Select object.
 227     */
 228    public function from($name, $cols = '*', $schema = null)
 229    {
 230        return $this->_join(self::FROM, $name, null, $cols, $schema);
 231    }
 232
 233    /**
 234     * Specifies the columns used in the FROM clause.
 235     *
 236     * The parameter can be a single string or Zend_Db_Expr object,
 237     * or else an array of strings or Zend_Db_Expr objects.
 238     *
 239     * @param  array|string|Zend_Db_Expr $cols The columns to select from this table.
 240     * @param  string $correlationName Correlation name of target table. OPTIONAL
 241     * @return Zend_Db_Select This Zend_Db_Select object.
 242     */
 243    public function columns($cols = '*', $correlationName = null)
 244    {
 245        if ($correlationName === null && count($this->_parts[self::FROM])) {
 246            $correlationNameKeys = array_keys($this->_parts[self::FROM]);
 247            $correlationName = current($correlationNameKeys);
 248        }
 249
 250        if (!array_key_exists($correlationName, $this->_parts[self::FROM])) {
 251            /**
 252             * @see Zend_Db_Select_Exception
 253             */
 254            #require_once 'Zend/Db/Select/Exception.php';
 255            throw new Zend_Db_Select_Exception("No table has been specified for the FROM clause");
 256        }
 257
 258        $this->_tableCols($correlationName, $cols);
 259
 260        return $this;
 261    }
 262
 263    /**
 264     * Adds a UNION clause to the query.
 265     *
 266     * The first parameter has to be an array of Zend_Db_Select or
 267     * sql query strings.
 268     *
 269     * <code>
 270     * $sql1 = $db->select();
 271     * $sql2 = "SELECT ...";
 272     * $select = $db->select()
 273     *      ->union(array($sql1, $sql2))
 274     *      ->order("id");
 275     * </code>
 276     *
 277     * @param  array $select Array of select clauses for the union.
 278     * @return Zend_Db_Select This Zend_Db_Select object.
 279     */
 280    public function union($select = array(), $type = self::SQL_UNION)
 281    {
 282        if (!is_array($select)) {
 283            #require_once 'Zend/Db/Select/Exception.php';
 284            throw new Zend_Db_Select_Exception(
 285                "union() only accepts an array of Zend_Db_Select instances of sql query strings."
 286            );
 287        }
 288
 289        if (!in_array($type, self::$_unionTypes)) {
 290            #require_once 'Zend/Db/Select/Exception.php';
 291            throw new Zend_Db_Select_Exception("Invalid union type '{$type}'");
 292        }
 293
 294        foreach ($select as $target) {
 295            $this->_parts[self::UNION][] = array($target, $type);
 296        }
 297
 298        return $this;
 299    }
 300
 301    /**
 302     * Adds a JOIN table and columns to the query.
 303     *
 304     * The $name and $cols parameters follow the same logic
 305     * as described in the from() method.
 306     *
 307     * @param  array|string|Zend_Db_Expr $name The table name.
 308     * @param  string $cond Join on this condition.
 309     * @param  array|string $cols The columns to select from the joined table.
 310     * @param  string $schema The database name to specify, if any.
 311     * @return Zend_Db_Select This Zend_Db_Select object.
 312     */
 313    public function join($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
 314    {
 315        return $this->joinInner($name, $cond, $cols, $schema);
 316    }
 317
 318    /**
 319     * Add an INNER JOIN table and colums to the query
 320     * Rows in both tables are matched according to the expression
 321     * in the $cond argument.  The result set is comprised
 322     * of all cases where rows from the left table match
 323     * rows from the right table.
 324     *
 325     * The $name and $cols parameters follow the same logic
 326     * as described in the from() method.
 327     *
 328     * @param  array|string|Zend_Db_Expr $name The table name.
 329     * @param  string $cond Join on this condition.
 330     * @param  array|string $cols The columns to select from the joined table.
 331     * @param  string $schema The database name to specify, if any.
 332     * @return Zend_Db_Select This Zend_Db_Select object.
 333     */
 334    public function joinInner($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
 335    {
 336        return $this->_join(self::INNER_JOIN, $name, $cond, $cols, $schema);
 337    }
 338
 339    /**
 340     * Add a LEFT OUTER JOIN table and colums to the query
 341     * All rows from the left operand table are included,
 342     * matching rows from the right operand table included,
 343     * and the columns from the right operand table are filled
 344     * with NULLs if no row exists matching the left table.
 345     *
 346     * The $name and $cols parameters follow the same logic
 347     * as described in the from() method.
 348     *
 349     * @param  array|string|Zend_Db_Expr $name The table name.
 350     * @param  string $cond Join on this condition.
 351     * @param  array|string $cols The columns to select from the joined table.
 352     * @param  string $schema The database name to specify, if any.
 353     * @return Zend_Db_Select This Zend_Db_Select object.
 354     */
 355    public function joinLeft($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
 356    {
 357        return $this->_join(self::LEFT_JOIN, $name, $cond, $cols, $schema);
 358    }
 359
 360    /**
 361     * Add a RIGHT OUTER JOIN table and colums to the query.
 362     * Right outer join is the complement of left outer join.
 363     * All rows from the right operand table are included,
 364     * matching rows from the left operand table included,
 365     * and the columns from the left operand table are filled
 366     * with NULLs if no row exists matching the right table.
 367     *
 368     * The $name and $cols parameters follow the same logic
 369     * as described in the from() method.
 370     *
 371     * @param  array|string|Zend_Db_Expr $name The table name.
 372     * @param  string $cond Join on this condition.
 373     * @param  array|string $cols The columns to select from the joined table.
 374     * @param  string $schema The database name to specify, if any.
 375     * @return Zend_Db_Select This Zend_Db_Select object.
 376     */
 377    public function joinRight($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
 378    {
 379        return $this->_join(self::RIGHT_JOIN, $name, $cond, $cols, $schema);
 380    }
 381
 382    /**
 383     * Add a FULL OUTER JOIN table and colums to the query.
 384     * A full outer join is like combining a left outer join
 385     * and a right outer join.  All rows from both tables are
 386     * included, paired with each other on the same row of the
 387     * result set if they satisfy the join condition, and otherwise
 388     * paired with NULLs in place of columns from the other table.
 389     *
 390     * The $name and $cols parameters follow the same logic
 391     * as described in the from() method.
 392     *
 393     * @param  array|string|Zend_Db_Expr $name The table name.
 394     * @param  string $cond Join on this condition.
 395     * @param  array|string $cols The columns to select from the joined table.
 396     * @param  string $schema The database name to specify, if any.
 397     * @return Zend_Db_Select This Zend_Db_Select object.
 398     */
 399    public function joinFull($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
 400    {
 401        return $this->_join(self::FULL_JOIN, $name, $cond, $cols, $schema);
 402    }
 403
 404    /**
 405     * Add a CROSS JOIN table and colums to the query.
 406     * A cross join is a cartesian product; there is no join condition.
 407     *
 408     * The $name and $cols parameters follow the same logic
 409     * as described in the from() method.
 410     *
 411     * @param  array|string|Zend_Db_Expr $name The table name.
 412     * @param  array|string $cols The columns to select from the joined table.
 413     * @param  string $schema The database name to specify, if any.
 414     * @return Zend_Db_Select This Zend_Db_Select object.
 415     */
 416    public function joinCross($name, $cols = self::SQL_WILDCARD, $schema = null)
 417    {
 418        return $this->_join(self::CROSS_JOIN, $name, null, $cols, $schema);
 419    }
 420
 421    /**
 422     * Add a NATURAL JOIN table and colums to the query.
 423     * A natural join assumes an equi-join across any column(s)
 424     * that appear with the same name in both tables.
 425     * Only natural inner joins are supported by this API,
 426     * even though SQL permits natural outer joins as well.
 427     *
 428     * The $name and $cols parameters follow the same logic
 429     * as described in the from() method.
 430     *
 431     * @param  array|string|Zend_Db_Expr $name The table name.
 432     * @param  array|string $cols The columns to select from the joined table.
 433     * @param  string $schema The database name to specify, if any.
 434     * @return Zend_Db_Select This Zend_Db_Select object.
 435     */
 436    public function joinNatural($name, $cols = self::SQL_WILDCARD, $schema = null)
 437    {
 438        return $this->_join(self::NATURAL_JOIN, $name, null, $cols, $schema);
 439    }
 440
 441    /**
 442     * Adds a WHERE condition to the query by AND.
 443     *
 444     * If a value is passed as the second param, it will be quoted
 445     * and replaced into the condition wherever a question-mark
 446     * appears. Array values are quoted and comma-separated.
 447     *
 448     * <code>
 449     * // simplest but non-secure
 450     * $select->where("id = $id");
 451     *
 452     * // secure (ID is quoted but matched anyway)
 453     * $select->where('id = ?', $id);
 454     *
 455     * // alternatively, with named binding
 456     * $select->where('id = :id');
 457     * </code>
 458     *
 459     * Note that it is more correct to use named bindings in your
 460     * queries for values other than strings. When you use named
 461     * bindings, don't forget to pass the values when actually
 462     * making a query:
 463     *
 464     * <code>
 465     * $db->fetchAll($select, array('id' => 5));
 466     * </code>
 467     *
 468     * @param string   $cond  The WHERE condition.
 469     * @param mixed    $value OPTIONAL The value to quote into the condition.
 470     * @param int      $type  OPTIONAL The type of the given value
 471     * @return Zend_Db_Select This Zend_Db_Select object.
 472     */
 473    public function where($cond, $value = null, $type = null)
 474    {
 475        $this->_parts[self::WHERE][] = $this->_where($cond, $value, $type, true);
 476
 477        return $this;
 478    }
 479
 480    /**
 481     * Adds a WHERE condition to the query by OR.
 482     *
 483     * Otherwise identical to where().
 484     *
 485     * @param string   $cond  The WHERE condition.
 486     * @param mixed    $value OPTIONAL The value to quote into the condition.
 487     * @param int      $type  OPTIONAL The type of the given value
 488     * @return Zend_Db_Select This Zend_Db_Select object.
 489     *
 490     * @see where()
 491     */
 492    public function orWhere($cond, $value = null, $type = null)
 493    {
 494        $this->_parts[self::WHERE][] = $this->_where($cond, $value, $type, false);
 495
 496        return $this;
 497    }
 498
 499    /**
 500     * Adds grouping to the query.
 501     *
 502     * @param  array|string $spec The column(s) to group by.
 503     * @return Zend_Db_Select This Zend_Db_Select object.
 504     */
 505    public function group($spec)
 506    {
 507        if (!is_array($spec)) {
 508            $spec = array($spec);
 509        }
 510
 511        foreach ($spec as $val) {
 512            if (preg_match('/\(.*\)/', (string) $val)) {
 513                $val = new Zend_Db_Expr($val);
 514            }
 515            $this->_parts[self::GROUP][] = $val;
 516        }
 517
 518        return $this;
 519    }
 520
 521    /**
 522     * Adds a HAVING condition to the query by AND.
 523     *
 524     * If a value is passed as the second param, it will be quoted
 525     * and replaced into the condition wherever a question-mark
 526     * appears. See {@link where()} for an example
 527     *
 528     * @param string $cond The HAVING condition.
 529     * @param mixed    $value OPTIONAL The value to quote into the condition.
 530     * @param int      $type  OPTIONAL The type of the given value
 531     * @return Zend_Db_Select This Zend_Db_Select object.
 532     */
 533    public function having($cond, $value = null, $type = null)
 534    {
 535        if ($value !== null) {
 536            $cond = $this->_adapter->quoteInto($cond, $value, $type);
 537        }
 538
 539        if ($this->_parts[self::HAVING]) {
 540            $this->_parts[self::HAVING][] = self::SQL_AND . " ($cond)";
 541        } else {
 542            $this->_parts[self::HAVING][] = "($cond)";
 543        }
 544
 545        return $this;
 546    }
 547
 548    /**
 549     * Adds a HAVING condition to the query by OR.
 550     *
 551     * Otherwise identical to orHaving().
 552     *
 553     * @param string $cond The HAVING condition.
 554     * @param mixed    $value OPTIONAL The value to quote into the condition.
 555     * @param int      $type  OPTIONAL The type of the given value
 556     * @return Zend_Db_Select This Zend_Db_Select object.
 557     *
 558     * @see having()
 559     */
 560    public function orHaving($cond, $value = null, $type = null)
 561    {
 562        if ($value !== null) {
 563            $cond = $this->_adapter->quoteInto($cond, $value, $type);
 564        }
 565
 566        if ($this->_parts[self::HAVING]) {
 567            $this->_parts[self::HAVING][] = self::SQL_OR . " ($cond)";
 568        } else {
 569            $this->_parts[self::HAVING][] = "($cond)";
 570        }
 571
 572        return $this;
 573    }
 574
 575    /**
 576     * Adds a row order to the query.
 577     *
 578     * @param mixed $spec The column(s) and direction to order by.
 579     * @return Zend_Db_Select This Zend_Db_Select object.
 580     */
 581    public function order($spec)
 582    {
 583        if (!is_array($spec)) {
 584            $spec = array($spec);
 585        }
 586
 587        // force 'ASC' or 'DESC' on each order spec, default is ASC.
 588        foreach ($spec as $val) {
 589            if ($val instanceof Zend_Db_Expr) {
 590                $expr = $val->__toString();
 591                if (empty($expr)) {
 592                    continue;
 593                }
 594                $this->_parts[self::ORDER][] = $val;
 595            } else {
 596                if (empty($val)) {
 597                    continue;
 598                }
 599                $direction = self::SQL_ASC;
 600                if (preg_match('/(.*\W)(' . self::SQL_ASC . '|' . self::SQL_DESC . ')\b/si', $val, $matches)) {
 601                    $val = trim($matches[1]);
 602                    $direction = $matches[2];
 603                }
 604                if (preg_match('/\(.*\)/', $val)) {
 605                    $val = new Zend_Db_Expr($val);
 606                }
 607                $this->_parts[self::ORDER][] = array($val, $direction);
 608            }
 609        }
 610
 611        return $this;
 612    }
 613
 614    /**
 615     * Sets a limit count and offset to the query.
 616     *
 617     * @param int $count OPTIONAL The number of rows to return.
 618     * @param int $offset OPTIONAL Start returning after this many rows.
 619     * @return Zend_Db_Select This Zend_Db_Select object.
 620     */
 621    public function limit($count = null, $offset = null)
 622    {
 623        $this->_parts[self::LIMIT_COUNT]  = (int) $count;
 624        $this->_parts[self::LIMIT_OFFSET] = (int) $offset;
 625        return $this;
 626    }
 627
 628    /**
 629     * Sets the limit and count by page number.
 630     *
 631     * @param int $page Limit results to this page number.
 632     * @param int $rowCount Use this many rows per page.
 633     * @return Zend_Db_Select This Zend_Db_Select object.
 634     */
 635    public function limitPage($page, $rowCount)
 636    {
 637        $page     = ($page > 0)     ? $page     : 1;
 638        $rowCount = ($rowCount > 0) ? $rowCount : 1;
 639        $this->_parts[self::LIMIT_COUNT]  = (int) $rowCount;
 640        $this->_parts[self::LIMIT_OFFSET] = (int) $rowCount * ($page - 1);
 641        return $this;
 642    }
 643
 644    /**
 645     * Makes the query SELECT FOR UPDATE.
 646     *
 647     * @param bool $flag Whether or not the SELECT is FOR UPDATE (default true).
 648     * @return Zend_Db_Select This Zend_Db_Select object.
 649     */
 650    public function forUpdate($flag = true)
 651    {
 652        $this->_parts[self::FOR_UPDATE] = (bool) $flag;
 653        return $this;
 654    }
 655
 656    /**
 657     * Get part of the structured information for the currect query.
 658     *
 659     * @param string $part
 660     * @return mixed
 661     * @throws Zend_Db_Select_Exception
 662     */
 663    public function getPart($part)
 664    {
 665        $part = strtolower($part);
 666        if (!array_key_exists($part, $this->_parts)) {
 667            #require_once 'Zend/Db/Select/Exception.php';
 668            throw new Zend_Db_Select_Exception("Invalid Select part '$part'");
 669        }
 670        return $this->_parts[$part];
 671    }
 672
 673    /**
 674     * Executes the current select object and returns the result
 675     *
 676     * @param integer $fetchMode OPTIONAL
 677     * @param  mixed  $bind An array of data to bind to the placeholders.
 678     * @return PDO_Statement|Zend_Db_Statement
 679     */
 680    public function query($fetchMode = null, $bind = array())
 681    {
 682        if (!empty($bind)) {
 683            $this->bind($bind);
 684        }
 685
 686        $stmt = $this->_adapter->query($this);
 687        if ($fetchMode == null) {
 688            $fetchMode = $this->_adapter->getFetchMode();
 689        }
 690        $stmt->setFetchMode($fetchMode);
 691        return $stmt;
 692    }
 693
 694    /**
 695     * Converts this object to an SQL SELECT string.
 696     *
 697     * @return string|null This object as a SELECT string. (or null if a string cannot be produced.)
 698     */
 699    public function assemble()
 700    {
 701        $sql = self::SQL_SELECT;
 702        foreach (array_keys(self::$_partsInit) as $part) {
 703            $method = '_render' . ucfirst($part);
 704            if (method_exists($this, $method)) {
 705                $sql = $this->$method($sql);
 706            }
 707        }
 708        return $sql;
 709    }
 710
 711    /**
 712     * Clear parts of the Select object, or an individual part.
 713     *
 714     * @param string $part OPTIONAL
 715     * @return Zend_Db_Select
 716     */
 717    public function reset($part = null)
 718    {
 719        if ($part == null) {
 720            $this->_parts = self::$_partsInit;
 721        } else if (array_key_exists($part, self::$_partsInit)) {
 722            $this->_parts[$part] = self::$_partsInit[$part];
 723        }
 724        return $this;
 725    }
 726
 727    /**
 728     * Gets the Zend_Db_Adapter_Abstract for this
 729     * particular Zend_Db_Select object.
 730     *
 731     * @return Zend_Db_Adapter_Abstract
 732     */
 733    public function getAdapter()
 734    {
 735        return $this->_adapter;
 736    }
 737
 738    /**
 739     * Populate the {@link $_parts} 'join' key
 740     *
 741     * Does the dirty work of populating the join key.
 742     *
 743     * The $name and $cols parameters follow the same logic
 744     * as described in the from() method.
 745     *
 746     * @param  null|string $type Type of join; inner, left, and null are currently supported
 747     * @param  array|string|Zend_Db_Expr $name Table name
 748     * @param  string $cond Join on this condition
 749     * @param  array|string $cols The columns to select from the joined table
 750     * @param  string $schema The database name to specify, if any.
 751     * @return Zend_Db_Select This Zend_Db_Select object
 752     * @throws Zend_Db_Select_Exception
 753     */
 754    protected function _join($type, $name, $cond, $cols, $schema = null)
 755    {
 756        if (!in_array($type, self::$_joinTypes) && $type != self::FROM) {
 757            /**
 758             * @see Zend_Db_Select_Exception
 759             */
 760            #require_once 'Zend/Db/Select/Exception.php';
 761            throw new Zend_Db_Select_Exception("Invalid join type '$type'");
 762        }
 763
 764        if (count($this->_parts[self::UNION])) {
 765            #require_once 'Zend/Db/Select/Exception.php';
 766            throw new Zend_Db_Select_Exception("Invalid use of table with " . self::SQL_UNION);
 767        }
 768
 769        if (empty($name)) {
 770            $correlationName = $tableName = '';
 771        } else if (is_array($name)) {
 772            // Must be array($correlationName => $tableName) or array($ident, ...)
 773            foreach ($name as $_correlationName => $_tableName) {
 774                if (is_string($_correlationName)) {
 775                    // We assume the key is the correlation name and value is the table name
 776                    $tableName = $_tableName;
 777                    $correlationName = $_correlationName;
 778                } else {
 779                    // We assume just an array of identifiers, with no correlation name
 780                    $tableName = $_tableName;
 781                    $correlationName = $this->_uniqueCorrelation($tableName);
 782                }
 783                break;
 784            }
 785        } else if ($name instanceof Zend_Db_Expr|| $name instanceof Zend_Db_Select) {
 786            $tableName = $name;
 787            $correlationName = $this->_uniqueCorrelation('t');
 788        } else if (preg_match('/^(.+)\s+AS\s+(.+)$/i', $name, $m)) {
 789            $tableName = $m[1];
 790            $correlationName = $m[2];
 791        } else {
 792            $tableName = $name;
 793            $correlationName = $this->_uniqueCorrelation($tableName);
 794        }
 795
 796        // Schema from table name overrides schema argument
 797        if (!is_object($tableName) && false !== strpos($tableName, '.')) {
 798            list($schema, $tableName) = explode('.', $tableName);
 799        }
 800
 801        $lastFromCorrelationName = null;
 802        if (!empty($correlationName)) {
 803            if (array_key_exists($correlationName, $this->_parts[self::FROM])) {
 804                /**
 805                 * @see Zend_Db_Select_Exception
 806                 */
 807                #require_once 'Zend/Db/Select/Exception.php';
 808                throw new Zend_Db_Select_Exception("You cannot define a correlation name '$correlationName' more than once");
 809            }
 810
 811            if ($type == self::FROM) {
 812                // append this from after the last from joinType
 813                $tmpFromParts = $this->_parts[self::FROM];
 814                $this->_parts[self::FROM] = array();
 815                // move all the froms onto the stack
 816                while ($tmpFromParts) {
 817                    $currentCorrelationName = key($tmpFromParts);
 818                    if ($tmpFromParts[$currentCorrelationName]['joinType'] != self::FROM) {
 819                        break;
 820                    }
 821                    $lastFromCorrelationName = $currentCorrelationName;
 822                    $this->_parts[self::FROM][$currentCorrelationName] = array_shift($tmpFromParts);
 823                }
 824            } else {
 825                $tmpFromParts = array();
 826            }
 827            $this->_parts[self::FROM][$correlationName] = array(
 828                'joinType'      => $type,
 829                'schema'        => $schema,
 830                'tableName'     => $tableName,
 831                'joinCondition' => $cond
 832                );
 833            while ($tmpFromParts) {
 834                $currentCorrelationName = key($tmpFromParts);
 835                $this->_parts[self::FROM][$currentCorrelationName] = array_shift($tmpFromParts);
 836            }
 837        }
 838
 839        // add to the columns from this joined table
 840        if ($type == self::FROM && $lastFromCorrelationName == null) {
 841            $lastFromCorrelationName = true;
 842        }
 843        $this->_tableCols($correlationName, $cols, $lastFromCorrelationName);
 844
 845        return $this;
 846    }
 847
 848    /**
 849     * Handle JOIN... USING... syntax
 850     *
 851     * This is functionality identical to the existing JOIN methods, however
 852     * the join condition can be passed as a single column name. This method
 853     * then completes the ON condition by using the same field for the FROM
 854     * table and the JOIN table.
 855     *
 856     * <code>
 857     * $select = $db->select()->from('table1')
 858     *                        ->joinUsing('table2', 'column1');
 859     *
 860     * // SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2
 861     * </code>
 862     *
 863     * These joins are called by the developer simply by adding 'Using' to the
 864     * method name. E.g.
 865     * * joinUsing
 866     * * joinInnerUsing
 867     * * joinFullUsing
 868     * * joinRightUsing
 869     * * joinLeftUsing
 870     *
 871     * @return Zend_Db_Select This Zend_Db_Select object.
 872     */
 873    public function _joinUsing($type, $name, $cond, $cols = '*', $schema = null)
 874    {
 875        if (empty($this->_parts[self::FROM])) {
 876            #require_once 'Zend/Db/Select/Exception.php';
 877            throw new Zend_Db_Select_Exception("You can only perform a joinUsing after specifying a FROM table");
 878        }
 879
 880        $join  = $this->_adapter->quoteIdentifier(key($this->_parts[self::FROM]), true);
 881        $from  = $this->_adapter->quoteIdentifier($this->_uniqueCorrelation($name), true);
 882
 883        $cond1 = $from . '.' . $cond;
 884        $cond2 = $join . '.' . $cond;
 885        $cond  = $cond1 . ' = ' . $cond2;
 886
 887        return $this->_join($type, $name, $cond, $cols, $schema);
 888    }
 889
 890    /**
 891     * Generate a unique correlation name
 892     *
 893     * @param string|array $name A qualified identifier.
 894     * @return string A unique correlation name.
 895     */
 896    private function _uniqueCorrelation($name)
 897    {
 898        if (is_array($name)) {
 899            $c = end($name);
 900        } else {
 901            // Extract just the last name of a qualified table name
 902            $dot = strrpos($name,'.');
 903            $c = ($dot === false) ? $name : substr($name, $dot+1);
 904        }
 905        for ($i = 2; array_key_exists($c, $this->_parts[self::FROM]); ++$i) {
 906            $c = $name . '_' . (string) $i;
 907        }
 908        return $c;
 909    }
 910
 911    /**
 912     * Adds to the internal table-to-column mapping array.
 913     *
 914     * @param  string $tbl The table/join the columns come from.
 915     * @param  array|string $cols The list of columns; preferably as
 916     * an array, but possibly as a string containing one column.
 917     * @param  bool|string True if it should be prepended, a correlation name if it should be inserted
 918     * @return void
 919     */
 920    protected function _tableCols($correlationName, $cols, $afterCorrelationName = null)
 921    {
 922        if (!is_array($cols)) {
 923            $cols = array($cols);
 924        }
 925
 926        if ($correlationName == null) {
 927            $correlationName = '';
 928        }
 929
 930        $columnValues = array();
 931
 932        foreach (array_filter($cols) as $alias => $col) {
 933            $currentCorrelationName = $correlationName;
 934            if (is_string($col)) {
 935                // Check for a column matching "<column> AS <alias>" and extract the alias name
 936                if (preg_match('/^(.+)\s+' . self::SQL_AS . '\s+(.+)$/i', $col, $m)) {
 937                    $col = $m[1];
 938                    $alias = $m[2];
 939                }
 940                // Check for columns that look like functions and convert to Zend_Db_Expr
 941                if (preg_match('/\(.*\)/', $col)) {
 942                    $col = new Zend_Db_Expr($col);
 943                } elseif (preg_match('/(.+)\.(.+)/', $col, $m)) {
 944                    $currentCorrelationName = $m[1];
 945                    $col = $m[2];
 946                }
 947            }
 948            $columnValues[] = array($currentCorrelationName, $col, is_string($alias) ? $alias : null);
 949        }
 950
 951        if ($columnValues) {
 952
 953            // should we attempt to prepend or insert these values?
 954            if ($afterCorrelationName === true || is_string($afterCorrelationName)) {
 955                $tmpColumns = $this->_parts[self::COLUMNS];
 956                $this->_parts[self::COLUMNS] = array();
 957            } else {
 958                $tmpColumns = array();
 959            }
 960
 961            // find the correlation name to insert after
 962            if (is_string($afterCorrelationName)) {
 963                while ($tmpColumns) {
 964                    $this->_parts[self::COLUMNS][] = $currentColumn = array_shift($tmpColumns);
 965                    if ($currentColumn[0] == $afterCorrelationName) {
 966                        break;
 967                    }
 968                }
 969            }
 970
 971            // apply current values to current stack
 972            foreach ($columnValues as $columnValue) {
 973                array_push($this->_parts[self::COLUMNS], $columnValue);
 974            }
 975
 976            // finish ensuring that all previous values are applied (if they exist)
 977            while ($tmpColumns) {
 978                array_push($this->_parts[self::COLUMNS], array_shift($tmpColumns));
 979            }
 980        }
 981    }
 982
 983    /**
 984     * Internal function for creating the where clause
 985     *
 986     * @param string   $condition
 987     * @param mixed    $value  optional
 988     * @param string   $type   optional
 989     * @param boolean  $bool  true = AND, false = OR
 990     * @return string  clause
 991     */
 992    protected function _where($condition, $value = null, $type = null, $bool = true)
 993    {
 994        if (count($this->_parts[self::UNION])) {
 995            #require_once 'Zend/Db/Select/Exception.php';
 996            throw new Zend_Db_Select_Exception("Invalid use of where clause with " . self::SQL_UNION);
 997        }
 998
 999        if ($value !== null) {
1000            $condition = $this->_adapter->quoteInto($condition, $value, $type);
1001        }
1002
1003        $cond = "";
1004        if ($this->_parts[self::WHERE]) {
1005            if ($bool === true) {
1006                $cond = self::SQL_AND . ' ';
1007            } else {
1008                $cond = self::SQL_OR . ' ';
1009            }
1010        }
1011
1012        return $cond . "($condition)";
1013    }
1014
1015    /**
1016     * @return array
1017     */
1018    protected function _getDummyTable()
1019    {
1020        return array();
1021    }
1022
1023    /**
1024     * Return a quoted schema name
1025     *
1026     * @param string   $schema  The schema name OPTIONAL
1027     * @return string|null
1028     */
1029    protected function _getQuotedSchema($schema = null)
1030    {
1031        if ($schema === null) {
1032            return null;
1033        }
1034        return $this->_adapter->quoteIdentifier($schema, true) . '.';
1035    }
1036
1037    /**
1038     * Return a quoted table name
1039     *
1040     * @param string   $tableName        The table name
1041     * @param string   $correlationName  The correlation name OPTIONAL
1042     * @return string
1043     */
1044    protected function _getQuotedTable($tableName, $correlationName = null)
1045    {
1046        return $this->_adapter->quoteTableAs($tableName, $correlationName, true);
1047    }
1048
1049    /**
1050     * Render DISTINCT clause
1051     *
1052     * @param string   $sql SQL query
1053     * @return string
1054     */
1055    protected function _renderDistinct($sql)
1056    {
1057        if ($this->_parts[self::DISTINCT]) {
1058            $sql .= ' ' . self::SQL_DISTINCT;
1059        }
1060
1061        return $sql;
1062    }
1063
1064    /**
1065     * Render DISTINCT clause
1066     *
1067     * @param string   $sql SQL query
1068     * @return string|null
1069     */
1070    protected function _renderColumns($sql)
1071    {
1072        if (!count($this->_parts[self::COLUMNS])) {
1073            return null;
1074        }
1075
1076        $columns = array();
1077        foreach ($this->_parts[self::COLUMNS] as $columnEntry) {
1078            list($correlationName, $column, $alias) = $columnEntry;
1079            if ($column instanceof Zend_Db_Expr) {
1080                $columns[] = $this->_adapter->quoteColumnAs($column, $alias, true);
1081            } else {
1082                if ($column == self::SQL_WILDCARD) {
1083                    $column = new Zend_Db_Expr(self::SQL_WILDCARD);
1084                    $alias = null;
1085                }
1086                if (empty($correlationName)) {
1087                    $columns[] = $this->_adapter->quoteColumnAs($column, $alias, true);
1088                } else {
1089                    $columns[] = $this->_adapter->quoteColumnAs(array($correlationName, $column), $alias, true);
1090                }
1091            }
1092        }
1093
1094        return $sql .= ' ' . implode(', ', $columns);
1095    }
1096
1097    /**
1098     * Render FROM clause
1099     *
1100     * @param string   $sql SQL query
1101     * @return string
1102     */
1103    protected function _renderFrom($sql)
1104    {
1105        /*
1106         * If no table specified, use RDBMS-dependent solution
1107         * for table-less query.  e.g. DUAL in Oracle.
1108         */
1109        if (empty($this->_parts[self::FROM])) {
1110            $this->_parts[self::FROM] = $this->_getDummyTable();
1111        }
1112
1113        $from = array();
1114
1115        foreach ($this->_parts[self::FROM] as $correlationName => $table) {
1116            $tmp = '';
1117
1118            $joinType = ($table['joinType'] == self::FROM) ? self::INNER_JOIN : $table['joinType'];
1119
1120            // Add join clause (if applicable)
1121            if (! empty($from)) {
1122                $tmp .= ' ' . strtoupper($joinType) . ' ';
1123            }
1124
1125            $tmp .= $this->_getQuotedSchema($table['schema']);
1126            $tmp .= $this->_getQuotedTable($table['tableName'], $correlationName);
1127
1128            // Add join conditions (if applicable)
1129            if (!empty($from) && ! empty($table['joinCondition'])) {
1130                $tmp .= ' ' . self::SQL_ON . ' ' . $table['joinCondition'];
1131            }
1132
1133            // Add the table name and condition add to the list
1134            $from[] = $tmp;
1135        }
1136
1137        // Add the list of all joins
1138        if (!empty($from)) {
1139            $sql .= ' ' . self::SQL_FROM . ' ' . implode("\n", $from);
1140        }
1141
1142        return $sql;
1143    }
1144
1145    /**
1146     * Render UNION query
1147     *
1148     * @param string   $sql SQL query
1149     * @return string
1150     */
1151    protected function _renderUnion($sql)
1152    {
1153        if ($this->_parts[self::UNION]) {
1154            $parts = count($this->_parts[self::UNION]);
1155            foreach ($this->_parts[self::UNION] as $cnt => $union) {
1156                list($target, $type) = $union;
1157                if ($target instanceof Zend_Db_Select) {
1158                    $target = $target->assemble();
1159                }
1160                $sql .= $target;
1161                if ($cnt < $parts - 1) {
1162                    $sql .= ' ' . $type . ' ';
1163                }
1164            }
1165        }
1166
1167        return $sql;
1168    }
1169
1170    /**
1171     * Render WHERE clause
1172     *
1173     * @param string   $sql SQL query
1174     * @return string
1175     */
1176    protected function _renderWhere($sql)
1177    {
1178        if ($this->_parts[self::FROM] && $this->_parts[self::WHERE]) {
1179            $sql .= ' ' . self::SQL_WHERE . ' ' .  implode(' ', $this->_parts[self::WHERE]);
1180        }
1181
1182        return $sql;
1183    }
1184
1185    /**
1186     * Render GROUP clause
1187     *
1188     * @param string   $sql SQL query
1189     * @return string
1190     */
1191    protected function _renderGroup($sql)
1192    {
1193        if ($this->_parts[self::FROM] && $this->_parts[self::GROUP]) {
1194            $group = array();
1195            foreach ($this->_parts[self::GROUP] as $term) {
1196                $group[] = $this->_adapter->quoteIdentifier($term, true);
1197            }
1198            $sql .= ' ' . self::SQL_GROUP_BY . ' ' . implode(",\n\t", $group);
1199        }
1200
1201        return $sql;
1202    }
1203
1204    /**
1205     * Render HAVING clause
1206     *
1207     * @param string   $sql SQL query
1208     * @return string
1209     */
1210    protected function _renderHaving($sql)
1211    {
1212        if ($this->_parts[self::FROM] && $this->_parts[self::HAVING]) {
1213            $sql .= ' ' . self::SQL_HAVING . ' ' . implode(' ', $this->_parts[self::HAVING]);
1214        }
1215
1216        return $sql;
1217    }
1218
1219    /**
1220     * Render ORDER clause
1221     *
1222     * @param string   $sql SQL query
1223     * @return string
1224     */
1225    protected function _renderOrder($sql)
1226    {
1227        if ($this->_parts[self::ORDER]) {
1228            $order = array();
1229            foreach ($this->_parts[self::ORDER] as $term) {
1230                if (is_array($term)) {
1231                    if(is_numeric($term[0]) && strval(intval($term[0])) == $term[0]) {
1232                        $order[] = (int)trim($term[0]) . ' ' . $term[1];
1233                    } else {
1234                        $order[] = $this->_adapter->quoteIdentifier($term[0], true) . ' ' . $term[1];
1235                    }
1236                } else if (is_numeric($term) && strval(intval($term)) == $term) {
1237                    $order[] = (int)trim($term);
1238                } else {
1239                    $order[] = $this->_adapter->quoteIdentifier($term, true);
1240                }
1241            }
1242            $sql .= ' ' . self::SQL_ORDER_BY . ' ' . implode(', ', $order);
1243        }
1244
1245        return $sql;
1246    }
1247
1248    /**
1249     * Render LIMIT OFFSET clause
1250     *
1251     * @param string   $sql SQL query
1252     * @return string
1253     */
1254    protected function _renderLimitoffset($sql)
1255    {
1256        $count = 0;
1257        $offset = 0;
1258
1259        if (!empty($this->_parts[self::LIMIT_OFFSET])) {
1260            $offset = (int) $this->_parts[self::LIMIT_OFFSET];
1261            $count = PHP_INT_MAX;
1262        }
1263
1264        if (!empty($this->_parts[self::LIMIT_COUNT])) {
1265            $count = (int) $this->_parts[self::LIMIT_COUNT];
1266        }
1267
1268        /*
1269         * Add limits clause
1270         */
1271        if ($count > 0) {
1272            $sql = trim($this->_adapter->limit($sql, $count, $offset));
1273        }
1274
1275        return $sql;
1276    }
1277
1278    /**
1279     * Render FOR UPDATE clause
1280     *
1281     * @param string   $sql SQL query
1282     * @return string
1283     */
1284    protected function _renderForupdate($sql)
1285    {
1286        if ($this->_parts[self::FOR_UPDATE]) {
1287            $sql .= ' ' . self::SQL_FOR_UPDATE;
1288        }
1289
1290        return $sql;
1291    }
1292
1293    /**
1294     * Turn magic function calls into non-magic function calls
1295     * for joinUsing syntax
1296     *
1297     * @param string $method
1298     * @param array $args OPTIONAL Zend_Db_Table_Select query modifier
1299     * @return Zend_Db_Select
1300     * @throws Zend_Db_Select_Exception If an invalid method is called.
1301     */
1302    public function __call($method, array $args)
1303    {
1304        $matches = array();
1305
1306        /**
1307         * Recognize methods for Has-Many cases:
1308         * findParent<Class>()
1309         * findParent<Class>By<Rule>()
1310         * Use the non-greedy pattern repeat modifier e.g. \w+?
1311         */
1312        if (preg_match('/^join([a-zA-Z]*?)Using$/', $method, $matches)) {
1313            $type = strtolower($matches[1]);
1314            if ($type) {
1315                $type .= ' join';
1316                if (!in_array($type, self::$_joinTypes)) {
1317                    #require_once 'Zend/Db/Select/Exception.php';
1318                    throw new Zend_Db_Select_Exception("Unrecognized method '$method()'");
1319                }
1320                if (in_array($type, array(self::CROSS_JOIN, self::NATURAL_JOIN))) {
1321                    #require_once 'Zend/Db/Select/Exception.php';
1322                    throw new Zend_Db_Select_Exception("Cannot perform a joinUsing with method '$method()'");
1323                }
1324            } else {
1325                $type = self::INNER_JOIN;
1326            }
1327            array_unshift($args, $type);
1328            return call_user_func_array(array($this, '_joinUsing'), $args);
1329        }
1330
1331        #require_once 'Zend/Db/Select/Exception.php';
1332        throw new Zend_Db_Select_Exception("Unrecognized method '$method()'");
1333    }
1334
1335    /**
1336     * Implements magic method.
1337     *
1338     * @return string This object as a SELECT string.
1339     */
1340    public function __toString()
1341    {
1342        try {
1343            $sql = $this->assemble();
1344        } catch (Exception $e) {
1345            trigger_error($e->getMessage(), E_USER_WARNING);
1346            $sql = '';
1347        }
1348        return (string)$sql;
1349    }
1350
1351}