MODX Revolution PHP Cross Reference Content Management Systems

Source: /core/xpdo/om/xpdoquery.class.php - 913 lines - 38875 bytes - Summary - Text - Print

Description: A class for constructing complex SQL statements using a model-aware API.

   1  <?php
   2  /*
   3   * Copyright 2010-2013 by MODX, LLC.
   4   *
   5   * This file is part of xPDO.
   6   *
   7   * xPDO is free software; you can redistribute it and/or modify it under the
   8   * terms of the GNU General Public License as published by the Free Software
   9   * Foundation; either version 2 of the License, or (at your option) any later
  10   * version.
  11   *
  12   * xPDO is distributed in the hope that it will be useful, but WITHOUT ANY
  13   * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  14   * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  15   *
  16   * You should have received a copy of the GNU General Public License along with
  17   * xPDO; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
  18   * Suite 330, Boston, MA 02111-1307 USA
  19   */
  20  
  21  /**
  22   * A class for constructing complex SQL statements using a model-aware API.
  23   *
  24   * @package xpdo
  25   * @subpackage om
  26   */
  27  
  28  /**
  29   * An xPDOCriteria derivative with methods for constructing complex statements.
  30   *
  31   * @abstract
  32   * @package xpdo
  33   * @subpackage om
  34   */
  35  abstract class xPDOQuery extends xPDOCriteria {
  36      const SQL_AND = 'AND';
  37      const SQL_OR = 'OR';
  38      const SQL_JOIN_CROSS = 'JOIN';
  39      const SQL_JOIN_LEFT = 'LEFT JOIN';
  40      const SQL_JOIN_RIGHT = 'RIGHT JOIN';
  41      const SQL_JOIN_NATURAL_LEFT = 'NATURAL LEFT JOIN';
  42      const SQL_JOIN_NATURAL_RIGHT = 'NATURAL RIGHT JOIN';
  43      const SQL_JOIN_STRAIGHT = 'STRAIGHT_JOIN';
  44  
  45      /**
  46       * An array of symbols and keywords indicative of SQL operators.
  47       *
  48       * @var array
  49       * @todo Refactor this to separate xPDOQuery operators from db-specific conditional statement identifiers.
  50       */
  51      protected $_operators= array (
  52          '=',
  53          '!=',
  54          '<',
  55          '<=',
  56          '>',
  57          '>=',
  58          '<=>',
  59          ' LIKE ',
  60          ' IS NULL',
  61          ' IS NOT NULL',
  62          ' BETWEEN ',
  63          ' IN ',
  64          ' IN(',
  65          ' NOT(',
  66          ' NOT (',
  67          ' NOT IN ',
  68          ' NOT IN(',
  69          ' EXISTS (',
  70          ' EXISTS(',
  71          ' NOT EXISTS (',
  72          ' NOT EXISTS(',
  73          ' COALESCE(',
  74          ' GREATEST(',
  75          ' INTERVAL(',
  76          ' LEAST(',
  77          'MATCH(',
  78          'MATCH (',
  79          'MAX(',
  80          'MIN(',
  81          'AVG('
  82      );
  83      protected $_quotable= array ('string', 'password', 'date', 'datetime', 'timestamp', 'time');
  84      protected $_class= null;
  85      protected $_alias= null;
  86      protected $_tableClass = null;
  87      public $graph= array ();
  88      public $query= array (
  89          'command' => 'SELECT',
  90          'distinct' => '',
  91          'columns' => '',
  92          'from' => array (
  93              'tables' => array (),
  94              'joins' => array (),
  95          ),
  96          'set' => array (),
  97          'where' => array (),
  98          'groupby' => array (),
  99          'having' => array (),
 100          'orderby' => array (),
 101          'offset' => '',
 102          'limit' => '',
 103      );
 104  
 105      public function __construct(& $xpdo, $class, $criteria= null) {
 106          parent :: __construct($xpdo);
 107          if ($class= $this->xpdo->loadClass($class)) {
 108              $this->_class= $class;
 109              $this->_alias= $class;
 110              $this->_tableClass = $this->xpdo->getTableClass($this->_class);
 111              $this->query['from']['tables'][0]= array (
 112                  'table' => $this->xpdo->getTableName($this->_class),
 113                  'alias' => & $this->_alias
 114              );
 115              if ($criteria !== null) {
 116                  if (is_object($criteria)) {
 117                      $this->wrap($criteria);
 118                  }
 119                  else {
 120                      $this->where($criteria);
 121                  }
 122              }
 123          }
 124      }
 125  
 126      public function getClass() {
 127          return $this->_class;
 128      }
 129  
 130      public function getAlias() {
 131          return $this->_alias;
 132      }
 133  
 134      public function getTableClass() {
 135          return $this->_tableClass;
 136      }
 137  
 138      /**
 139       * Set the type of SQL command you want to build.
 140       *
 141       * The default is SELECT, though it also supports DELETE and UPDATE.
 142       *
 143       * @param string $command The type of SQL statement represented by this object.  Default is 'SELECT'.
 144       * @return xPDOQuery Returns the current object for convenience.
 145       */
 146      public function command($command= 'SELECT') {
 147          $command= strtoupper(trim($command));
 148          if (preg_match('/(SELECT|UPDATE|DELETE)/', $command)) {
 149              $this->query['command']= $command;
 150              if (in_array($command, array('DELETE','UPDATE'))) $this->_alias= $this->xpdo->getTableName($this->_class);
 151          }
 152          return $this;
 153      }
 154  
 155      /**
 156       * Set the DISTINCT attribute of the query.
 157       *
 158       * @param null|boolean $on Defines how to set the distinct attribute:
 159       *  - null (default) indicates the distinct attribute should be toggled
 160       *  - any other value is treated as a boolean, i.e. true to set DISTINCT, false to unset
 161       * @return xPDOQuery Returns the current object for convenience.
 162       */
 163      public function distinct($on = null) {
 164          if ($on === null) {
 165              if (empty($this->query['distinct']) || $this->query['distinct'] !== 'DISTINCT') {
 166                  $this->query['distinct']= 'DISTINCT';
 167              } else {
 168                  $this->query['distinct']= '';
 169              }
 170          } else {
 171              $this->query['distinct']= $on == true ? 'DISTINCT' : '';
 172          }
 173          return $this;
 174      }
 175  
 176      /**
 177       * Sets a SQL alias for the table represented by the main class.
 178       *
 179       * @param string $alias An alias for the main table for the SQL statement.
 180       * @return xPDOQuery Returns the current object for convenience.
 181       */
 182      public function setClassAlias($alias= '') {
 183          $this->_alias= $alias;
 184          return $this;
 185      }
 186  
 187      /**
 188       * Specify columns to return from the SQL query.
 189       *
 190       * @param string $columns Columns to return from the query.
 191       * @return xPDOQuery Returns the current object for convenience.
 192       */
 193      public function select($columns= '*') {
 194          if (!is_array($columns)) {
 195              $columns= trim($columns);
 196              if ($columns == '*' || $columns === $this->_alias . '.*' || $columns === $this->xpdo->escape($this->_alias) . '.*') {
 197                  $columns= $this->xpdo->getSelectColumns($this->_class, $this->_alias, $this->_alias . '_');
 198              }
 199              $columns= explode(',', $columns);
 200              foreach ($columns as $colKey => $column) $columns[$colKey] = trim($column);
 201          }
 202          if (is_array ($columns)) {
 203              if (!is_array($this->query['columns'])) {
 204                  $this->query['columns']= $columns;
 205              } else {
 206                  $this->query['columns']= array_merge($this->query['columns'], $columns);
 207              }
 208          }
 209          return $this;
 210      }
 211  
 212      /**
 213       * Specify the SET clause(s) for a SQL UPDATE query.
 214       *
 215       * @param array $values An associative array of fields and the values to set them to.
 216       * @return xPDOQuery Returns a reference to the current instance for convenience.
 217       */
 218      public function set(array $values) {
 219          $fieldMeta= $this->xpdo->getFieldMeta($this->_class);
 220          $fieldAliases= $this->xpdo->getFieldAliases($this->_class);
 221          reset($values);
 222          while (list($key, $value) = each($values)) {
 223              $type= null;
 224              if (!array_key_exists($key, $fieldMeta)) {
 225                  if (array_key_exists($key, $fieldAliases)) {
 226                      $key = $fieldAliases[$key];
 227                  } else {
 228                      continue;
 229                  }
 230              }
 231              if (array_key_exists($key, $fieldMeta)) {
 232                  if ($value === null) {
 233                      $type= PDO::PARAM_NULL;
 234                  }
 235                  elseif (!in_array($fieldMeta[$key]['phptype'], $this->_quotable)) {
 236                      $type= PDO::PARAM_INT;
 237                  }
 238                  elseif (strpos($value, '(') === false && !$this->isConditionalClause($value)) {
 239                      $type= PDO::PARAM_STR;
 240                  }
 241                  $this->query['set'][$key]= array('value' => $value, 'type' => $type);
 242              }
 243          }
 244          return $this;
 245      }
 246  
 247      /**
 248       * Join a table represented by the specified class.
 249       *
 250       * @param string $class The classname (or relation alias for aggregates and
 251       * composites) of representing the table to be joined.
 252       * @param string $alias An optional alias to represent the joined table in
 253       * the constructed query.
 254       * @param string $type The type of join to perform.  See the xPDOQuery::SQL_JOIN
 255       * constants.
 256       * @param mixed $conditions Conditions of the join specified in any xPDO
 257       * compatible criteria object or expression.
 258       * @param string $conjunction A conjunction to be applied to the condition
 259       * or conditions supplied.
 260       * @param array $binding Optional bindings to accompany the conditions.
 261       * @param int $condGroup An optional identifier for adding the conditions
 262       * to a specific set of conjoined expressions.
 263       * @return xPDOQuery Returns the current object for convenience.
 264       */
 265      public function join($class, $alias= '', $type= xPDOQuery::SQL_JOIN_CROSS, $conditions= array (), $conjunction= xPDOQuery::SQL_AND, $binding= null, $condGroup= 0) {
 266          if ($this->xpdo->loadClass($class)) {
 267              $alias= $alias ? $alias : $class;
 268              $target= & $this->query['from']['joins'];
 269              $targetIdx= count($target);
 270              $target[$targetIdx]= array (
 271                  'table' => $this->xpdo->getTableName($class),
 272                  'class' => $class,
 273                  'alias' => $alias,
 274                  'type' => $type,
 275                  'conditions' => array ()
 276              );
 277              if (empty ($conditions)) {
 278                  $fkMeta= $this->xpdo->getFKDefinition($this->_class, $alias);
 279                  if ($fkMeta) {
 280                      $parentAlias= isset ($this->_alias) ? $this->_alias : $this->_class;
 281                      $local= $fkMeta['local'];
 282                      $foreign= $fkMeta['foreign'];
 283                      $conditions= $this->xpdo->escape($parentAlias) . '.' . $this->xpdo->escape($local) . ' =  ' . $this->xpdo->escape($alias) . '.' . $this->xpdo->escape($foreign);
 284                      if (isset($fkMeta['criteria']['local'])) {
 285                          $localCriteria = array();
 286                          if (is_array($fkMeta['criteria']['local'])) {
 287                              foreach ($fkMeta['criteria']['local'] as $critKey => $critVal) {
 288                                  if (is_numeric($critKey)) {
 289                                      $localCriteria[] = $critVal;
 290                                  } else {
 291                                      $localCriteria["{$this->_class}.{$critKey}"] = $critVal;
 292                                  }
 293                              }
 294                          }
 295                          if (!empty($localCriteria)) {
 296                              $conditions = array($localCriteria, $conditions);
 297                          }
 298                          $foreignCriteria = array();
 299                          if (is_array($fkMeta['criteria']['foreign'])) {
 300                              foreach ($fkMeta['criteria']['foreign'] as $critKey => $critVal) {
 301                                  if (is_numeric($critKey)) {
 302                                      $foreignCriteria[] = $critVal;
 303                                  } else {
 304                                      $foreignCriteria["{$parentAlias}.{$critKey}"] = $critVal;
 305                                  }
 306                              }
 307                          }
 308                          if (!empty($foreignCriteria)) {
 309                              $conditions = array($foreignCriteria, $conditions);
 310                          }
 311                      }
 312                  }
 313              }
 314              $this->condition($target[$targetIdx]['conditions'], $conditions, $conjunction, $binding, $condGroup);
 315          }
 316          return $this;
 317      }
 318  
 319      public function innerJoin($class, $alias= '', $conditions= array (), $conjunction= xPDOQuery::SQL_AND, $binding= null, $condGroup= 0) {
 320          return $this->join($class, $alias, xPDOQuery::SQL_JOIN_CROSS, $conditions, $conjunction, $binding, $condGroup);
 321      }
 322  
 323      public function leftJoin($class, $alias= '', $conditions= array (), $conjunction= xPDOQuery::SQL_AND, $binding= null, $condGroup= 0) {
 324          return $this->join($class, $alias, xPDOQuery::SQL_JOIN_LEFT, $conditions, $conjunction, $binding, $condGroup);
 325      }
 326  
 327      public function rightJoin($class, $alias= '', $conditions= array (), $conjunction= xPDOQuery::SQL_AND, $binding= null, $condGroup= 0) {
 328          return $this->join($class, $alias, xPDOQuery::SQL_JOIN_RIGHT, $conditions, $conjunction, $binding, $condGroup);
 329      }
 330  
 331      /**
 332       * Add a FROM clause to the query.
 333       *
 334       * @param string $class The class representing the table to add.
 335       * @param string $alias An optional alias for the class.
 336       * @return xPDOQuery Returns the instance.
 337       */
 338      public function from($class, $alias= '') {
 339          if ($class= $this->xpdo->loadClass($class)) {
 340              $alias= $alias ? $alias : $class;
 341              $this->query['from']['tables'][]= array (
 342                  'table' => $this->xpdo->getTableName($class),
 343                  'alias' => $alias
 344              );
 345          }
 346          return $this;
 347      }
 348  
 349      /**
 350       * Add a condition to the query.
 351       *
 352       * @param string $target The target clause for the condition.
 353       * @param mixed $conditions A valid xPDO criteria expression.
 354       * @param string $conjunction The conjunction to use when appending this condition, i.e., AND or OR.
 355       * @param mixed $binding A value or PDO binding representation of a value for the condition.
 356       * @param integer $condGroup A numeric identifier for associating conditions into groups.
 357       * @return xPDOQuery Returns the instance.
 358       */
 359      public function condition(& $target, $conditions= '1', $conjunction= xPDOQuery::SQL_AND, $binding= null, $condGroup= 0) {
 360          $condGroup= intval($condGroup);
 361          if (!isset ($target[$condGroup])) $target[$condGroup]= array ();
 362          try {
 363              $target[$condGroup][] = $this->parseConditions($conditions, $conjunction);
 364          } catch (xPDOException $e) {
 365              $this->xpdo->log(xPDO::LOG_LEVEL_ERROR, $e->getMessage());
 366              $this->where("2=1");
 367          }
 368          return $this;
 369      }
 370  
 371      /**
 372       * Add a WHERE condition to the query.
 373       *
 374       * @param mixed $conditions A valid xPDO criteria expression.
 375       * @param string $conjunction The conjunction to use when appending this condition, i.e., AND or OR.
 376       * @param mixed $binding A value or PDO binding representation of a value for the condition.
 377       * @param integer $condGroup A numeric identifier for associating conditions into groups.
 378       * @return xPDOQuery Returns the instance.
 379       */
 380      public function where($conditions= '', $conjunction= xPDOQuery::SQL_AND, $binding= null, $condGroup= 0) {
 381          $this->condition($this->query['where'], $conditions, $conjunction, $binding, $condGroup);
 382          return $this;
 383      }
 384  
 385      public function andCondition($conditions, $binding= null, $group= 0) {
 386          $this->where($conditions, xPDOQuery::SQL_AND, $binding, $group);
 387          return $this;
 388      }
 389      public function orCondition($conditions, $binding= null, $group= 0) {
 390          $this->where($conditions, xPDOQuery::SQL_OR, $binding, $group);
 391          return $this;
 392      }
 393  
 394      /**
 395       * Add an ORDER BY clause to the query.
 396       *
 397       * @param string $column Column identifier to sort by.
 398       * @param string $direction The direction to sort by, ASC or DESC.
 399       * @return xPDOQuery Returns the instance.
 400       */
 401      public function sortby($column, $direction= 'ASC') {
 402          $this->query['sortby'][]= array ('column' => $column, 'direction' => $direction);
 403          return $this;
 404      }
 405  
 406      /**
 407       * Add an GROUP BY clause to the query.
 408       *
 409       * @param string $column Column identifier to group by.
 410       * @param string $direction The direction to sort by, ASC or DESC.
 411       * @return xPDOQuery Returns the instance.
 412       */
 413      public function groupby($column, $direction= '') {
 414          $this->query['groupby'][]= array ('column' => $column, 'direction' => $direction);
 415          return $this;
 416      }
 417  
 418      public function having($conditions) {
 419          try {
 420              $this->query['having'][] = $this->parseConditions((array)$conditions);
 421          } catch (xPDOException $e) {
 422              $this->xpdo->log(xPDO::LOG_LEVEL_ERROR, $e->getMessage());
 423              $this->where("2=1");
 424          }
 425          return $this;
 426      }
 427  
 428      /**
 429       * Add a LIMIT/OFFSET clause to the query.
 430       *
 431       * @param integer $limit The number of records to return.
 432       * @param integer $offset The location in the result set to start from.
 433       * @return xPDOQuery Returns the instance.
 434       */
 435      public function limit($limit, $offset= 0) {
 436          $this->query['limit']= $limit;
 437          $this->query['offset']= $offset;
 438          return $this;
 439      }
 440  
 441      /**
 442       * Bind an object graph to the query.
 443       *
 444       * @param mixed $graph An array or JSON graph of related objects.
 445       * @return xPDOQuery Returns the instance.
 446       */
 447      public function bindGraph($graph) {
 448          if (is_string($graph)) {
 449              $graph= $this->xpdo->fromJSON($graph);
 450          }
 451          if (is_array ($graph)) {
 452              if ($this->graph !== $graph) {
 453                  $this->graph= $graph;
 454                  $this->select($this->xpdo->getSelectColumns($this->_class, $this->_alias, $this->_alias . '_'));
 455                  foreach ($this->graph as $relationAlias => $subRelations) {
 456                      $this->bindGraphNode($this->_class, $this->_alias, $relationAlias, $subRelations);
 457                  }
 458                  if ($pk= $this->xpdo->getPK($this->_class)) {
 459                      if (is_array ($pk)) {
 460                          foreach ($pk as $key) {
 461                              $this->sortby($this->xpdo->escape($this->_alias) . '.' . $this->xpdo->escape($key), 'ASC');
 462                          }
 463                      } else {
 464                          $this->sortby($this->xpdo->escape($this->_alias) . '.' . $this->xpdo->escape($pk), 'ASC');
 465                      }
 466                  }
 467              }
 468          }
 469          return $this;
 470      }
 471  
 472      /**
 473       * Bind the node of an object graph to the query.
 474       *
 475       * @param string $parentClass The class representing the relation parent.
 476       * @param string $parentAlias The alias the class is assuming.
 477       * @param string $classAlias The class representing the related graph node.
 478       * @param array $relations Child relations of the current graph node.
 479       */
 480      public function bindGraphNode($parentClass, $parentAlias, $classAlias, $relations) {
 481          if ($fkMeta= $this->xpdo->getFKDefinition($parentClass, $classAlias)) {
 482              $class= $fkMeta['class'];
 483              $local= $fkMeta['local'];
 484              $foreign= $fkMeta['foreign'];
 485              $this->select($this->xpdo->getSelectColumns($class, $classAlias, $classAlias . '_'));
 486              $expression= $this->xpdo->escape($parentAlias) . '.' . $this->xpdo->escape($local) . ' = ' .  $this->xpdo->escape($classAlias) . '.' . $this->xpdo->escape($foreign);
 487              if (isset($fkMeta['criteria']['local'])) {
 488                  $localCriteria = array();
 489                  if (is_array($fkMeta['criteria']['local'])) {
 490                      foreach ($fkMeta['criteria']['local'] as $critKey => $critVal) {
 491                          if (is_numeric($critKey)) {
 492                              $localCriteria[] = $critVal;
 493                          } else {
 494                              $localCriteria["{$classAlias}.{$critKey}"] = $critVal;
 495                          }
 496                      }
 497                  }
 498                  if (!empty($localCriteria)) {
 499                      $expression = array($localCriteria, $expression);
 500                  }
 501                  $foreignCriteria = array();
 502                  if (is_array($fkMeta['criteria']['foreign'])) {
 503                      foreach ($fkMeta['criteria']['foreign'] as $critKey => $critVal) {
 504                          if (is_numeric($critKey)) {
 505                              $foreignCriteria[] = $critVal;
 506                          } else {
 507                              $foreignCriteria["{$parentAlias}.{$critKey}"] = $critVal;
 508                          }
 509                      }
 510                  }
 511                  if (!empty($foreignCriteria)) {
 512                      $expression = array($foreignCriteria, $expression);
 513                  }
 514              }
 515              $this->leftJoin($class, $classAlias, $expression);
 516              if (!empty ($relations)) {
 517                  foreach ($relations as $relationAlias => $subRelations) {
 518                      $this->bindGraphNode($class, $classAlias, $relationAlias, $subRelations);
 519                  }
 520              }
 521          }
 522      }
 523  
 524      /**
 525       * Hydrates a graph of related objects from a single result set.
 526       *
 527       * @param array|PDOStatement $rows A collection of result set rows or an
 528       * executed PDOStatement to fetch rows from to hydrating the graph.
 529       * @param bool $cacheFlag Indicates if the objects should be cached and
 530       * optionally, by specifying an integer value, for how many seconds.
 531       * @return array A collection of objects with all related objects from the
 532       * graph pre-populated.
 533       */
 534      public function hydrateGraph($rows, $cacheFlag = true) {
 535          $instances= array ();
 536          $collectionCaching = $this->xpdo->getOption(xPDO::OPT_CACHE_DB_COLLECTIONS, array(), 1);
 537          if (is_object($rows)) {
 538              if ($cacheFlag && $this->xpdo->_cacheEnabled && $collectionCaching > 0) {
 539                  $cacheRows = array();
 540              }
 541              while ($row = $rows->fetch(PDO::FETCH_ASSOC)) {
 542                  $this->hydrateGraphParent($instances, $row);
 543                  if ($cacheFlag && $this->xpdo->_cacheEnabled && $collectionCaching > 0) {
 544                      $cacheRows[]= $row;
 545                  }
 546              }
 547              if ($cacheFlag && $this->xpdo->_cacheEnabled && $collectionCaching > 0) {
 548                  $this->xpdo->toCache($this, $cacheRows, $cacheFlag);
 549              }
 550          } elseif (is_array($rows)) {
 551              foreach ($rows as $row) {
 552                  $this->hydrateGraphParent($instances, $row);
 553              }
 554          }
 555          return $instances;
 556      }
 557  
 558      public function hydrateGraphParent(& $instances, $row) {
 559          $hydrated = false;
 560          $instance = $this->xpdo->call($this->getClass(), '_loadInstance', array(& $this->xpdo, $this->getClass(), $this->getAlias(), $row));
 561          if (is_object($instance)) {
 562              $pk= $instance->getPrimaryKey();
 563              if (is_array($pk)) $pk= implode('-', $pk);
 564              if (isset ($instances[$pk])) {
 565                  $instance= & $instances[$pk];
 566              }
 567              foreach ($this->graph as $relationAlias => $subRelations) {
 568                  $this->hydrateGraphNode($row, $instance, $relationAlias, $subRelations);
 569              }
 570              $instances[$pk]= $instance;
 571              $hydrated = true;
 572          }
 573          return $hydrated;
 574      }
 575  
 576      /**
 577       * Hydrates a node of the object graph.
 578       *
 579       * @param array $row The result set representing the current node.
 580       * @param xPDOObject $instance The xPDOObject instance to be hydrated from the node.
 581       * @param string $alias The alias identifying the object in the parent relationship.
 582       * @param array $relations Child relations of the current node.
 583       */
 584      public function hydrateGraphNode(& $row, & $instance, $alias, $relations) {
 585          $relObj= null;
 586          if ($relationMeta= $instance->getFKDefinition($alias)) {
 587              if ($row[$alias.'_'.$relationMeta['foreign']] != null) {
 588                  $relObj = $this->xpdo->call($relationMeta['class'], '_loadInstance', array(& $this->xpdo, $relationMeta['class'], $alias, $row));
 589                  if ($relObj) {
 590                      if (strtolower($relationMeta['cardinality']) == 'many') {
 591                          $instance->addMany($relObj, $alias);
 592                      } else {
 593                          $instance->addOne($relObj, $alias);
 594                      }
 595                  }
 596              }
 597          }
 598          if (!empty ($relations) && is_object($relObj)) {
 599              while (list($relationAlias, $subRelations)= each($relations)) {
 600                  if (is_array($subRelations) && !empty($subRelations)) {
 601                      foreach ($subRelations as $subRelation) {
 602                          $this->hydrateGraphNode($row, $relObj, $relationAlias, $subRelation);
 603                      }
 604                  } else {
 605                      $this->hydrateGraphNode($row, $relObj, $relationAlias, null);
 606                  }
 607              }
 608          }
 609      }
 610  
 611      /**
 612       * Constructs the SQL query from the xPDOQuery definition.
 613       *
 614       * @return boolean Returns true if a SQL statement was successfully constructed.
 615       */
 616      abstract public function construct();
 617  
 618      /**
 619       * Prepares the xPDOQuery for execution.
 620       *
 621       * @return PDOStatement The PDOStatement representing the prepared query.
 622       */
 623      public function prepare($bindings= array (), $byValue= true, $cacheFlag= null) {
 624          $this->stmt= null;
 625          if ($this->construct() && $this->stmt= $this->xpdo->prepare($this->sql)) {
 626              $this->bind($bindings, $byValue, $cacheFlag);
 627          }
 628          return $this->stmt;
 629      }
 630  
 631      /**
 632       * Parses an xPDO condition expression into one or more xPDOQueryConditions.
 633       *
 634       * @param mixed $conditions A valid xPDO condition expression.
 635       * @param string $conjunction The optional conjunction for the condition( s ).
 636       * @return array||xPDOQueryCondition An xPDOQueryCondition or array of xPDOQueryConditions.
 637       */
 638      public function parseConditions($conditions, $conjunction = xPDOQuery::SQL_AND) {
 639          $result= array ();
 640          $pk= $this->xpdo->getPK($this->_class);
 641          $pktype= $this->xpdo->getPKType($this->_class);
 642          $fieldMeta= $this->xpdo->getFieldMeta($this->_class, true);
 643          $fieldAliases= $this->xpdo->getFieldAliases($this->_class);
 644          $command= strtoupper($this->query['command']);
 645          $alias= $command == 'SELECT' ? $this->_class : $this->xpdo->getTableName($this->_class, false);
 646          $alias= trim($alias, $this->xpdo->_escapeCharOpen . $this->xpdo->_escapeCharClose);
 647          if (is_array($conditions)) {
 648              if (isset($conditions[0]) && is_scalar($conditions[0]) && !$this->isConditionalClause($conditions[0]) && is_array($pk) && count($conditions) == count($pk)) {
 649                  $iteration= 0;
 650                  foreach ($pk as $k) {
 651                      if (!isset ($conditions[$iteration])) {
 652                          $conditions[$iteration]= null;
 653                      }
 654                      $isString= in_array($fieldMeta[$k]['phptype'], $this->_quotable);
 655                      $field= array();
 656                      $field['sql']= $this->xpdo->escape($alias) . '.' . $this->xpdo->escape($k) . " = ?";
 657                      $field['binding']= array (
 658                          'value' => $conditions[$iteration],
 659                          'type' => $isString ? PDO::PARAM_STR : PDO::PARAM_INT,
 660                          'length' => 0
 661                      );
 662                      $field['conjunction']= $conjunction;
 663                      $result[$iteration]= new xPDOQueryCondition($field);
 664                      $iteration++;
 665                  }
 666              } else {
 667                  reset($conditions);
 668                  while (list ($key, $val)= each($conditions)) {
 669                      if (is_int($key)) {
 670                          if (is_array($val)) {
 671                              $result[]= $this->parseConditions($val, $conjunction);
 672                              continue;
 673                          } elseif ($this->isConditionalClause($val)) {
 674                              $result[]= new xPDOQueryCondition(array('sql' => $val, 'binding' => null, 'conjunction' => $conjunction));
 675                              continue;
 676                          } else {
 677                              $this->xpdo->log(xPDO::LOG_LEVEL_ERROR, "Error parsing condition with key {$key}: " . print_r($val, true));
 678                              continue;
 679                          }
 680                      } elseif (is_scalar($val) || is_array($val) || $val === null) {
 681                          $alias= $command == 'SELECT' ? $this->_class : trim($this->xpdo->getTableName($this->_class, false), $this->xpdo->_escapeCharOpen . $this->xpdo->_escapeCharClose);
 682                          $operator= '=';
 683                          $conj = $conjunction;
 684                          $key_operator= explode(':', $key);
 685                          if ($key_operator && count($key_operator) === 2) {
 686                              $key= $key_operator[0];
 687                              $operator= $key_operator[1];
 688                          }
 689                          elseif ($key_operator && count($key_operator) === 3) {
 690                              $conj= $key_operator[0];
 691                              $key= $key_operator[1];
 692                              $operator= $key_operator[2];
 693                          }
 694                          if (strpos($key, '.') !== false) {
 695                              $key_parts= explode('.', $key);
 696                              $alias= trim($key_parts[0], " {$this->xpdo->_escapeCharOpen}{$this->xpdo->_escapeCharClose}");
 697                              $key= $key_parts[1];
 698                          }
 699                          if (!array_key_exists($key, $fieldMeta)) {
 700                              if (array_key_exists($key, $fieldAliases)) {
 701                                  $key= $fieldAliases[$key];
 702                              } elseif ($this->isConditionalClause($key)) {
 703                                  continue;
 704                              }
 705                          }
 706                          if (!empty($key)) {
 707                              if ($val === null) {
 708                                  $type= PDO::PARAM_NULL;
 709                                  if (!in_array($operator, array('IS', 'IS NOT'))) {
 710                                      $operator= $operator === '!=' ? 'IS NOT' : 'IS';
 711                                  }
 712                              }
 713                              elseif (isset($fieldMeta[$key]) && !in_array($fieldMeta[$key]['phptype'], $this->_quotable)) {
 714                                  $type= PDO::PARAM_INT;
 715                              }
 716                              else {
 717                                  $type= PDO::PARAM_STR;
 718                              }
 719                              if (in_array(strtoupper($operator), array('IN', 'NOT IN')) && is_array($val)) {
 720                                  $vals = array();
 721                                  foreach ($val as $v) {
 722                                      if ($v === null) {
 723                                          $vals[] = null;
 724                                      } else {
 725                                          switch ($type) {
 726                                              case PDO::PARAM_INT:
 727                                                  $vals[] = (integer) $v;
 728                                                  break;
 729                                              case PDO::PARAM_STR:
 730                                                  $vals[] = $this->xpdo->quote($v);
 731                                                  break;
 732                                              default:
 733                                                  $this->xpdo->log(xPDO::LOG_LEVEL_ERROR, "Error parsing {$operator} condition with key {$key}: " . print_r($v, true));
 734                                                  break;
 735                                          }
 736                                      }
 737                                  }
 738                                  if (empty($vals)) {
 739                                      $this->xpdo->log(xPDO::LOG_LEVEL_ERROR, "Encountered empty {$operator} condition with key {$key}");
 740                                  }
 741                                  $val = "(" . implode(',', $vals) . ")";
 742                                  $sql = "{$this->xpdo->escape($alias)}.{$this->xpdo->escape($key)} {$operator} {$val}";
 743                                  $result[]= new xPDOQueryCondition(array('sql' => $sql, 'binding' => null, 'conjunction' => $conj));
 744                                  continue;
 745                              }
 746                              $field= array ();
 747                              $field['sql']= $this->xpdo->escape($alias) . '.' . $this->xpdo->escape($key) . ' ' . $operator . ' ?';
 748                              $field['binding']= array (
 749                                  'value' => $val,
 750                                  'type' => $type,
 751                                  'length' => 0
 752                              );
 753                              $field['conjunction']= $conj;
 754                              $result[]= new xPDOQueryCondition($field);
 755                          } else {
 756                              throw new xPDOException("Invalid query expression");
 757                          }
 758                      }
 759                  }
 760              }
 761          }
 762          elseif ($this->isConditionalClause($conditions)) {
 763              $result= new xPDOQueryCondition(array(
 764                  'sql' => $conditions
 765                  ,'binding' => null
 766                  ,'conjunction' => $conjunction
 767              ));
 768          }
 769          elseif (($pktype == 'integer' && is_numeric($conditions)) || ($pktype == 'string' && is_string($conditions) && $this->isValidClause($conditions))) {
 770              if ($pktype == 'integer') {
 771                  $param_type= PDO::PARAM_INT;
 772              } else {
 773                  $param_type= PDO::PARAM_STR;
 774              }
 775              $field['sql']= $this->xpdo->escape($alias) . '.' . $this->xpdo->escape($pk) . ' = ?';
 776              $field['binding']= array ('value' => $conditions, 'type' => $param_type, 'length' => 0);
 777              $field['conjunction']= $conjunction;
 778              $result = new xPDOQueryCondition($field);
 779          }
 780          return $result;
 781      }
 782  
 783      /**
 784       * Determines if a string contains a conditional operator.
 785       *
 786       * @param string $string The string to evaluate.
 787       * @return boolean True if the string is a complete conditional SQL clause.
 788       */
 789      public function isConditionalClause($string) {
 790          $matched= false;
 791          if (is_string($string)) {
 792              if (!$this->isValidClause($string)) {
 793                  throw new xPDOException("SQL injection attempt detected: {$string}");
 794              }
 795              foreach ($this->_operators as $operator) {
 796                  if (strpos(strtoupper($string), $operator) !== false) {
 797                      $matched= true;
 798                      break;
 799                  }
 800              }
 801          }
 802          return $matched;
 803      }
 804  
 805      protected function isValidClause($clause) {
 806          $output = rtrim($clause, ' ;');
 807          $output = preg_replace("/\\\\'.*?\\\\'/", '{mask}', $output);
 808          $output = preg_replace('/\\".*?\\"/', '{mask}', $output);
 809          $output = preg_replace("/'.*?'/", '{mask}', $output);
 810          $output = preg_replace('/".*?"/', '{mask}', $output);
 811          return strpos($output, ';') === false && strpos(strtolower($output), 'union') === false;
 812      }
 813  
 814      /**
 815       * Builds conditional clauses from xPDO condition expressions.
 816       *
 817       * @param array|xPDOQueryCondition $conditions An array of conditions or an xPDOQueryCondition instance.
 818       * @param string $conjunction Either xPDOQuery:SQL_AND or xPDOQuery::SQL_OR
 819       * @param boolean $isFirst Indicates if this is the first condition in an array.
 820       * @return string The generated SQL clause.
 821       */
 822      public function buildConditionalClause($conditions, & $conjunction = xPDOQuery::SQL_AND, $isFirst = true) {
 823          $clause= '';
 824          if (is_array($conditions)) {
 825              $groups= count($conditions);
 826              $currentGroup= 1;
 827              $first = true;
 828              $origConjunction = $conjunction;
 829              $groupConjunction = $conjunction;
 830              foreach ($conditions as $groupKey => $group) {
 831                  $groupClause = '';
 832                  $groupClause.= $this->buildConditionalClause($group, $groupConjunction, $first);
 833                  if ($first) {
 834                      $conjunction = $groupConjunction;
 835                  }
 836                  if (!empty($groupClause)) $clause.= $groupClause;
 837                  $currentGroup++;
 838                  $first = false;
 839              }
 840              $conjunction = $origConjunction;
 841              if ($groups > 1 && !empty($clause)) {
 842                  $clause = " ( {$clause} ) ";
 843              }
 844              if (!$isFirst && !empty($clause)) {
 845                  $clause = ' ' . $groupConjunction . ' ' . $clause;
 846              }
 847          } elseif (is_object($conditions) && $conditions instanceof xPDOQueryCondition) {
 848              if ($isFirst) {
 849                  $conjunction = $conditions->conjunction;
 850              } else {
 851                  $clause.= ' ' . $conditions->conjunction . ' ';
 852              }
 853              $clause.= $conditions->sql;
 854              if (!empty ($conditions->binding)) {
 855                  $this->bindings[]= $conditions->binding;
 856              }
 857          }
 858          if ($this->xpdo->getDebug() === true) {
 859              $this->xpdo->log(xPDO::LOG_LEVEL_DEBUG, "Returning clause:\n{$clause}\nfrom conditions:\n" . print_r($conditions, 1));
 860          }
 861          return $clause;
 862      }
 863  
 864      /**
 865       * Wrap an existing xPDOCriteria into this xPDOQuery instance.
 866       *
 867       * @param xPDOCriteria $criteria
 868       */
 869      public function wrap($criteria) {
 870          if ($criteria instanceof xPDOQuery) {
 871              $this->_class= $criteria->_class;
 872              $this->_alias= $criteria->_alias;
 873              $this->graph= $criteria->graph;
 874              $this->query= $criteria->query;
 875          }
 876          $this->sql= $criteria->sql;
 877          $this->stmt= $criteria->stmt;
 878          $this->bindings= $criteria->bindings;
 879          $this->cacheFlag= $criteria->cacheFlag;
 880      }
 881  }
 882  
 883  /**
 884   * Abstracts individual query conditions used in xPDOQuery instances.
 885   *
 886   * @package xpdo
 887   * @subpackage om
 888   */
 889  class xPDOQueryCondition {
 890      /**
 891       * @var string The SQL string for the condition.
 892       */
 893      public $sql = '';
 894      /**
 895       * @var array An array of value/parameter bindings for the condition.
 896       */
 897      public $binding = array();
 898      /**
 899       * @var string The conjunction identifying how the condition is related to the previous condition(s).
 900       */
 901      public $conjunction = xPDOQuery::SQL_AND;
 902  
 903      /**
 904       * The constructor for creating an xPDOQueryCondition instance.
 905       *
 906       * @param array $properties An array of properties representing the condition.
 907       */
 908      public function __construct(array $properties) {
 909          if (isset($properties['sql'])) $this->sql = $properties['sql'];
 910          if (isset($properties['binding'])) $this->binding = $properties['binding'];
 911          if (isset($properties['conjunction'])) $this->conjunction = $properties['conjunction'];
 912      }
 913  }

title

Description

title

Description

title

Description

title

title

Body