1<?php 2/* 3 * $Id$ 4 * 5 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 6 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 7 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 8 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT 9 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 10 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 11 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 12 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 13 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 14 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 15 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 16 * 17 * This software consists of voluntary contributions made by many individuals 18 * and is licensed under the LGPL. For more information, see 19 * <http://www.doctrine-project.org>. 20 */ 21 22/** 23 * Doctrine_Query 24 * A Doctrine_Query object represents a DQL query. It is used to query databases for 25 * data in an object-oriented fashion. A DQL query understands relations and inheritance 26 * and is dbms independant. 27 * 28 * @package Doctrine 29 * @subpackage Query 30 * @license http://www.opensource.org/licenses/lgpl-license.php LGPL 31 * @link www.doctrine-project.org 32 * @since 1.0 33 * @version $Revision$ 34 * @author Konsta Vesterinen <kvesteri@cc.hut.fi> 35 * @todo Proposal: This class does far too much. It should have only 1 task: Collecting 36 * the DQL query parts and the query parameters (the query state and caching options/methods 37 * can remain here, too). 38 * The actual SQL construction could be done by a separate object (Doctrine_Query_SqlBuilder?) 39 * whose task it is to convert DQL into SQL. 40 * Furthermore the SqlBuilder? can then use other objects (Doctrine_Query_Tokenizer?), 41 * (Doctrine_Query_Parser(s)?) to accomplish his work. Doctrine_Query does not need 42 * to know the tokenizer/parsers. There could be extending 43 * implementations of SqlBuilder? that cover the specific SQL dialects. 44 * This would release Doctrine_Connection and the Doctrine_Connection_xxx classes 45 * from this tedious task. 46 * This would also largely reduce the currently huge interface of Doctrine_Query(_Abstract) 47 * and better hide all these transformation internals from the public Query API. 48 * 49 * @internal The lifecycle of a Query object is the following: 50 * After construction the query object is empty. Through using the fluent 51 * query interface the user fills the query object with DQL parts and query parameters. 52 * These get collected in {@link $_dqlParts} and {@link $_params}, respectively. 53 * When the query is executed the first time, or when {@link getSqlQuery()} 54 * is called the first time, the collected DQL parts get parsed and the resulting 55 * connection-driver specific SQL is generated. The generated SQL parts are 56 * stored in {@link $_sqlParts} and the final resulting SQL query is stored in 57 * {@link $_sql}. 58 */ 59class Doctrine_Query extends Doctrine_Query_Abstract implements Countable 60{ 61 /** 62 * @var array The DQL keywords. 63 */ 64 protected static $_keywords = array('ALL', 65 'AND', 66 'ANY', 67 'AS', 68 'ASC', 69 'AVG', 70 'BETWEEN', 71 'BIT_LENGTH', 72 'BY', 73 'CHARACTER_LENGTH', 74 'CHAR_LENGTH', 75 'CURRENT_DATE', 76 'CURRENT_TIME', 77 'CURRENT_TIMESTAMP', 78 'DELETE', 79 'DESC', 80 'DISTINCT', 81 'EMPTY', 82 'EXISTS', 83 'FALSE', 84 'FETCH', 85 'FROM', 86 'GROUP', 87 'HAVING', 88 'IN', 89 'INDEXBY', 90 'INNER', 91 'IS', 92 'JOIN', 93 'LEFT', 94 'LIKE', 95 'LOWER', 96 'MEMBER', 97 'MOD', 98 'NEW', 99 'NOT', 100 'NULL', 101 'OBJECT', 102 'OF', 103 'OR', 104 'ORDER', 105 'OUTER', 106 'POSITION', 107 'SELECT', 108 'SOME', 109 'TRIM', 110 'TRUE', 111 'UNKNOWN', 112 'UPDATE', 113 'WHERE'); 114 115 /** 116 * @var array 117 */ 118 protected $_subqueryAliases = array(); 119 120 /** 121 * @var array $_aggregateAliasMap an array containing all aggregate aliases, keys as dql aliases 122 * and values as sql aliases 123 */ 124 protected $_aggregateAliasMap = array(); 125 126 /** 127 * @var array 128 */ 129 protected $_pendingAggregates = array(); 130 131 /** 132 * @param boolean $needsSubquery 133 */ 134 protected $_needsSubquery = false; 135 136 /** 137 * @param boolean $isSubquery whether or not this query object is a subquery of another 138 * query object 139 */ 140 protected $_isSubquery; 141 142 /** 143 * @var array $_neededTables an array containing the needed table aliases 144 */ 145 protected $_neededTables = array(); 146 147 /** 148 * @var array $pendingSubqueries SELECT part subqueries, these are called pending subqueries since 149 * they cannot be parsed directly (some queries might be correlated) 150 */ 151 protected $_pendingSubqueries = array(); 152 153 /** 154 * @var array $_pendingFields an array of pending fields (fields waiting to be parsed) 155 */ 156 protected $_pendingFields = array(); 157 158 /** 159 * @var array $_parsers an array of parser objects, each DQL query part has its own parser 160 */ 161 protected $_parsers = array(); 162 163 /** 164 * @var array $_pendingJoinConditions an array containing pending joins 165 */ 166 protected $_pendingJoinConditions = array(); 167 168 /** 169 * @var array 170 */ 171 protected $_expressionMap = array(); 172 173 /** 174 * @var string $_sql cached SQL query 175 */ 176 protected $_sql; 177 178 /** 179 * create 180 * returns a new Doctrine_Query object 181 * 182 * @param Doctrine_Connection $conn optional connection parameter 183 * @param string $class Query class to instantiate 184 * @return Doctrine_Query 185 */ 186 public static function create($conn = null, $class = null) 187 { 188 if ( ! $class) { 189 $class = Doctrine_Manager::getInstance() 190 ->getAttribute(Doctrine_Core::ATTR_QUERY_CLASS); 191 } 192 return new $class($conn); 193 } 194 195 /** 196 * Clears all the sql parts. 197 */ 198 protected function clear() 199 { 200 $this->_preQueried = false; 201 $this->_pendingJoinConditions = array(); 202 $this->_state = self::STATE_DIRTY; 203 } 204 205 /** 206 * Resets the query to the state just after it has been instantiated. 207 */ 208 public function reset() 209 { 210 $this->_subqueryAliases = array(); 211 $this->_aggregateAliasMap = array(); 212 $this->_pendingAggregates = array(); 213 $this->_pendingSubqueries = array(); 214 $this->_pendingFields = array(); 215 $this->_neededTables = array(); 216 $this->_expressionMap = array(); 217 $this->_subqueryAliases = array(); 218 $this->_needsSubquery = false; 219 $this->_isLimitSubqueryUsed = false; 220 } 221 222 /** 223 * createSubquery 224 * creates a subquery 225 * 226 * @return Doctrine_Hydrate 227 */ 228 public function createSubquery() 229 { 230 $class = get_class($this); 231 $obj = new $class(); 232 233 // copy the aliases to the subquery 234 $obj->copySubqueryInfo($this); 235 236 // this prevents the 'id' being selected, re ticket #307 237 $obj->isSubquery(true); 238 239 return $obj; 240 } 241 242 /** 243 * addPendingJoinCondition 244 * 245 * @param string $componentAlias component alias 246 * @param string $joinCondition dql join condition 247 * @return Doctrine_Query this object 248 */ 249 public function addPendingJoinCondition($componentAlias, $joinCondition) 250 { 251 if ( ! isset($this->_pendingJoinConditions[$componentAlias])) { 252 $this->_pendingJoinConditions[$componentAlias] = array(); 253 } 254 255 $this->_pendingJoinConditions[$componentAlias][] = $joinCondition; 256 } 257 258 /** 259 * fetchArray 260 * Convenience method to execute using array fetching as hydration mode. 261 * 262 * @param string $params 263 * @return array 264 */ 265 public function fetchArray($params = array()) 266 { 267 return $this->execute($params, Doctrine_Core::HYDRATE_ARRAY); 268 } 269 270 /** 271 * fetchOne 272 * Convenience method to execute the query and return the first item 273 * of the collection. 274 * 275 * @param array $params Query parameters 276 * @param int $hydrationMode Hydration mode: see Doctrine_Core::HYDRATE_* constants 277 * 278 * @return array|Doctrine_Record|false Array or Doctrine_Collection, depending on hydration mode. False if no result. 279 */ 280 public function fetchOne($params = array(), $hydrationMode = null) 281 { 282 $collection = $this->execute($params, $hydrationMode); 283 284 if (is_scalar($collection)) { 285 return $collection; 286 } 287 288 if ($collection === null || count($collection) === 0) { 289 return false; 290 } 291 292 if ($collection instanceof Doctrine_Collection) { 293 return $collection->getFirst(); 294 } else if (is_array($collection)) { 295 return array_shift($collection); 296 } 297 298 return false; 299 } 300 301 /** 302 * isSubquery 303 * if $bool parameter is set this method sets the value of 304 * Doctrine_Query::$isSubquery. If this value is set to true 305 * the query object will not load the primary key fields of the selected 306 * components. 307 * 308 * If null is given as the first parameter this method retrieves the current 309 * value of Doctrine_Query::$isSubquery. 310 * 311 * @param boolean $bool whether or not this query acts as a subquery 312 * @return Doctrine_Query|bool 313 */ 314 public function isSubquery($bool = null) 315 { 316 if ($bool === null) { 317 return $this->_isSubquery; 318 } 319 320 $this->_isSubquery = (bool) $bool; 321 return $this; 322 } 323 324 /** 325 * getSqlAggregateAlias 326 * 327 * @param string $dqlAlias the dql alias of an aggregate value 328 * @return string 329 */ 330 public function getSqlAggregateAlias($dqlAlias) 331 { 332 if (isset($this->_aggregateAliasMap[$dqlAlias])) { 333 // mark the expression as used 334 $this->_expressionMap[$dqlAlias][1] = true; 335 336 return $this->_aggregateAliasMap[$dqlAlias]; 337 } else if ( ! empty($this->_pendingAggregates)) { 338 $this->processPendingAggregates(); 339 340 return $this->getSqlAggregateAlias($dqlAlias); 341 } else if( ! ($this->_conn->getAttribute(Doctrine_Core::ATTR_PORTABILITY) & Doctrine_Core::PORTABILITY_EXPR)){ 342 return $dqlAlias; 343 } else { 344 throw new Doctrine_Query_Exception('Unknown aggregate alias: ' . $dqlAlias); 345 } 346 } 347 348 /** 349 * Check if a dql alias has a sql aggregate alias 350 * 351 * @param string $dqlAlias 352 * @return boolean 353 */ 354 public function hasSqlAggregateAlias($dqlAlias) 355 { 356 try { 357 $this->getSqlAggregateAlias($dqlAlias); 358 return true; 359 } catch (Exception $e) { 360 return false; 361 } 362 } 363 364 /** 365 * Adjust the processed param index for "foo.bar IN ?" support 366 * 367 */ 368 public function adjustProcessedParam($index) 369 { 370 // Retrieve all params 371 $params = $this->getInternalParams(); 372 373 // Retrieve already processed values 374 $first = array_slice($params, 0, $index); 375 $last = array_slice($params, $index, count($params) - $index); 376 377 // Include array as values splicing the params array 378 array_splice($last, 0, 1, $last[0]); 379 380 // Put all param values into a single index 381 $this->_execParams = array_merge($first, $last); 382 } 383 384 /** 385 * Retrieves a specific DQL query part. 386 * 387 * @see Doctrine_Query_Abstract::$_dqlParts 388 * <code> 389 * var_dump($q->getDqlPart('where')); 390 * // array(2) { [0] => string(8) 'name = ?' [1] => string(8) 'date > ?' } 391 * </code> 392 * @param string $queryPart the name of the query part; can be: 393 * array from, containing strings; 394 * array select, containg string; 395 * boolean forUpdate; 396 * array set; 397 * array join; 398 * array where; 399 * array groupby; 400 * array having; 401 * array orderby, containing strings such as 'id ASC'; 402 * array limit, containing numerics; 403 * array offset, containing numerics; 404 * @return array 405 */ 406 public function getDqlPart($queryPart) 407 { 408 if ( ! isset($this->_dqlParts[$queryPart])) { 409 throw new Doctrine_Query_Exception('Unknown query part ' . $queryPart); 410 } 411 412 return $this->_dqlParts[$queryPart]; 413 } 414 415 /** 416 * contains 417 * 418 * Method to check if a arbitrary piece of dql exists 419 * 420 * @param string $dql Arbitrary piece of dql to check for 421 * @return boolean 422 */ 423 public function contains($dql) 424 { 425 return stripos($this->getDql(), $dql) === false ? false : true; 426 } 427 428 /** 429 * processPendingFields 430 * the fields in SELECT clause cannot be parsed until the components 431 * in FROM clause are parsed, hence this method is called everytime a 432 * specific component is being parsed. For instance, the wildcard '*' 433 * is expanded in the list of columns. 434 * 435 * @throws Doctrine_Query_Exception if unknown component alias has been given 436 * @param string $componentAlias the alias of the component 437 * @return string SQL code 438 * @todo Description: What is a 'pending field' (and are there non-pending fields, too)? 439 * What is 'processed'? (Meaning: What information is gathered & stored away) 440 */ 441 public function processPendingFields($componentAlias) 442 { 443 $tableAlias = $this->getSqlTableAlias($componentAlias); 444 $table = $this->_queryComponents[$componentAlias]['table']; 445 446 if ( ! isset($this->_pendingFields[$componentAlias])) { 447 if ($this->_hydrator->getHydrationMode() != Doctrine_Core::HYDRATE_NONE) { 448 if ( ! $this->_isSubquery && $componentAlias == $this->getRootAlias()) { 449 throw new Doctrine_Query_Exception("The root class of the query (alias $componentAlias) " 450 . " must have at least one field selected."); 451 } 452 } 453 return; 454 } 455 456 // At this point we know the component is FETCHED (either it's the base class of 457 // the query (FROM xyz) or its a "fetch join"). 458 459 // Check that the parent join (if there is one), is a "fetch join", too. 460 if ( ! $this->isSubquery() && isset($this->_queryComponents[$componentAlias]['parent'])) { 461 $parentAlias = $this->_queryComponents[$componentAlias]['parent']; 462 if (is_string($parentAlias) && ! isset($this->_pendingFields[$parentAlias]) 463 && $this->_hydrator->getHydrationMode() != Doctrine_Core::HYDRATE_NONE 464 && $this->_hydrator->getHydrationMode() != Doctrine_Core::HYDRATE_SCALAR 465 && $this->_hydrator->getHydrationMode() != Doctrine_Core::HYDRATE_SINGLE_SCALAR) { 466 throw new Doctrine_Query_Exception("The left side of the join between " 467 . "the aliases '$parentAlias' and '$componentAlias' must have at least" 468 . " the primary key field(s) selected."); 469 } 470 } 471 472 $fields = $this->_pendingFields[$componentAlias]; 473 474 // check for wildcards 475 if (in_array('*', $fields)) { 476 $fields = $table->getFieldNames(); 477 } else { 478 $driverClassName = $this->_hydrator->getHydratorDriverClassName(); 479 // only auto-add the primary key fields if this query object is not 480 // a subquery of another query object or we're using a child of the Object Graph 481 // hydrator 482 if ( ! $this->_isSubquery && is_subclass_of($driverClassName, 'Doctrine_Hydrator_Graph')) { 483 $fields = array_unique(array_merge((array) $table->getIdentifier(), $fields)); 484 } 485 } 486 487 $sql = array(); 488 foreach ($fields as $fieldAlias => $fieldName) { 489 $columnName = $table->getColumnName($fieldName); 490 if (($owner = $table->getColumnOwner($columnName)) !== null && 491 $owner !== $table->getComponentName()) { 492 493 $parent = $this->_conn->getTable($owner); 494 $columnName = $parent->getColumnName($fieldName); 495 $parentAlias = $this->getSqlTableAlias($componentAlias . '.' . $parent->getComponentName()); 496 $sql[] = $this->_conn->quoteIdentifier($parentAlias) . '.' . $this->_conn->quoteIdentifier($columnName) 497 . ' AS ' 498 . $this->_conn->quoteIdentifier($tableAlias . '__' . $columnName); 499 } else { 500 // Fix for http://www.doctrine-project.org/jira/browse/DC-585 501 // Take the field alias if available 502 if (isset($this->_aggregateAliasMap[$fieldAlias])) { 503 $aliasSql = $this->_aggregateAliasMap[$fieldAlias]; 504 } else { 505 $columnName = $table->getColumnName($fieldName); 506 $aliasSql = $this->_conn->quoteIdentifier($tableAlias . '__' . $columnName); 507 } 508 $sql[] = $this->_conn->quoteIdentifier($tableAlias) . '.' . $this->_conn->quoteIdentifier($columnName) 509 . ' AS ' 510 . $aliasSql; 511 } 512 } 513 514 $this->_neededTables[] = $tableAlias; 515 516 return implode(', ', $sql); 517 } 518 519 /** 520 * Parses a nested field 521 * <code> 522 * $q->parseSelectField('u.Phonenumber.value'); 523 * </code> 524 * 525 * @param string $field 526 * @throws Doctrine_Query_Exception if unknown component alias has been given 527 * @return string SQL fragment 528 * @todo Description: Explain what this method does. Is there a relation to parseSelect()? 529 * This method is not used from any class or testcase in the Doctrine package. 530 * 531 */ 532 public function parseSelectField($field) 533 { 534 $terms = explode('.', $field); 535 536 if (isset($terms[1])) { 537 $componentAlias = $terms[0]; 538 $field = $terms[1]; 539 } else { 540 reset($this->_queryComponents); 541 $componentAlias = key($this->_queryComponents); 542 $fields = $terms[0]; 543 } 544 545 $tableAlias = $this->getSqlTableAlias($componentAlias); 546 $table = $this->_queryComponents[$componentAlias]['table']; 547 548 549 // check for wildcards 550 if ($field === '*') { 551 $sql = array(); 552 553 foreach ($table->getColumnNames() as $field) { 554 $sql[] = $this->parseSelectField($componentAlias . '.' . $field); 555 } 556 557 return implode(', ', $sql); 558 } else { 559 $name = $table->getColumnName($field); 560 561 $this->_neededTables[] = $tableAlias; 562 563 return $this->_conn->quoteIdentifier($tableAlias . '.' . $name) 564 . ' AS ' 565 . $this->_conn->quoteIdentifier($tableAlias . '__' . $name); 566 } 567 } 568 569 /** 570 * getExpressionOwner 571 * returns the component alias for owner of given expression 572 * 573 * @param string $expr expression from which to get to owner from 574 * @return string the component alias 575 * @todo Description: What does it mean if a component is an 'owner' of an expression? 576 * What kind of 'expression' are we talking about here? 577 */ 578 public function getExpressionOwner($expr) 579 { 580 if (strtoupper(substr(trim($expr, '( '), 0, 6)) !== 'SELECT') { 581 // Fix for http://www.doctrine-project.org/jira/browse/DC-754 582 $expr = preg_replace('/([\'\"])[^\1]*\1/', '', $expr); 583 preg_match_all("/[a-z_][a-z0-9_]*\.[a-z_][a-z0-9_]*[\.[a-z0-9]+]*/i", $expr, $matches); 584 585 $match = current($matches); 586 587 if (isset($match[0])) { 588 $terms = explode('.', $match[0]); 589 590 return $terms[0]; 591 } 592 } 593 return $this->getRootAlias(); 594 595 } 596 597 /** 598 * parseSelect 599 * parses the query select part and 600 * adds selected fields to pendingFields array 601 * 602 * @param string $dql 603 * @todo Description: What information is extracted (and then stored)? 604 */ 605 public function parseSelect($dql) 606 { 607 $refs = $this->_tokenizer->sqlExplode($dql, ','); 608 609 $pos = strpos(trim($refs[0]), ' '); 610 $first = substr($refs[0], 0, $pos); 611 612 // check for DISTINCT keyword 613 if ($first === 'DISTINCT') { 614 $this->_sqlParts['distinct'] = true; 615 616 $refs[0] = substr($refs[0], ++$pos); 617 } 618 619 $parsedComponents = array(); 620 621 foreach ($refs as $reference) { 622 $reference = trim($reference); 623 624 if (empty($reference)) { 625 continue; 626 } 627 628 $terms = $this->_tokenizer->sqlExplode($reference, ' '); 629 $pos = strpos($terms[0], '('); 630 631 if (count($terms) > 1 || $pos !== false) { 632 $expression = array_shift($terms); 633 $alias = array_pop($terms); 634 635 if ( ! $alias) { 636 $alias = substr($expression, 0, $pos); 637 } 638 639 // Fix for http://www.doctrine-project.org/jira/browse/DC-706 640 if ($pos !== false && substr($expression, 0, 1) !== "'" && substr($expression, 0, $pos) == '') { 641 $_queryComponents = $this->_queryComponents; 642 reset($_queryComponents); 643 $componentAlias = key($_queryComponents); 644 } else { 645 $componentAlias = $this->getExpressionOwner($expression); 646 } 647 648 $expression = $this->parseClause($expression); 649 650 $tableAlias = $this->getSqlTableAlias($componentAlias); 651 652 $index = count($this->_aggregateAliasMap); 653 654 $sqlAlias = $this->_conn->quoteIdentifier($tableAlias . '__' . $index); 655 656 $this->_sqlParts['select'][] = $expression . ' AS ' . $sqlAlias; 657 658 $this->_aggregateAliasMap[$alias] = $sqlAlias; 659 $this->_expressionMap[$alias][0] = $expression; 660 661 $this->_queryComponents[$componentAlias]['agg'][$index] = $alias; 662 663 $this->_neededTables[] = $tableAlias; 664 665 // Fix for http://www.doctrine-project.org/jira/browse/DC-585 666 // Add selected columns to pending fields 667 if (preg_match('/^([^\(]+)\.(\'?)(.*?)(\'?)$/', $expression, $field)) { 668 $this->_pendingFields[$componentAlias][$alias] = $field[3]; 669 } 670 671 } else { 672 $e = explode('.', $terms[0]); 673 674 if (isset($e[1])) { 675 $componentAlias = $e[0]; 676 $field = $e[1]; 677 } else { 678 reset($this->_queryComponents); 679 $componentAlias = key($this->_queryComponents); 680 $field = $e[0]; 681 } 682 683 $this->_pendingFields[$componentAlias][] = $field; 684 } 685 } 686 } 687 688 /** 689 * parseClause 690 * parses given DQL clause 691 * 692 * this method handles five tasks: 693 * 694 * 1. Converts all DQL functions to their native SQL equivalents 695 * 2. Converts all component references to their table alias equivalents 696 * 3. Converts all field names to actual column names 697 * 4. Quotes all identifiers 698 * 5. Parses nested clauses and subqueries recursively 699 * 700 * @return string SQL string 701 * @todo Description: What is a 'dql clause' (and what not)? 702 * Refactor: Too long & nesting level 703 */ 704 public function parseClause($clause) 705 { 706 $clause = $this->_conn->dataDict->parseBoolean(trim($clause)); 707 708 if (is_numeric($clause)) { 709 return $clause; 710 } 711 712 $terms = $this->_tokenizer->clauseExplode($clause, array(' ', '+', '-', '*', '/', '<', '>', '=', '>=', '<=', '&', '|')); 713 $str = ''; 714 715 foreach ($terms as $term) { 716 $pos = strpos($term[0], '('); 717 718 if ($pos !== false && substr($term[0], 0, 1) !== "'") { 719 $name = substr($term[0], 0, $pos); 720 721 $term[0] = $this->parseFunctionExpression($term[0]); 722 } else { 723 if (substr($term[0], 0, 1) !== "'" && substr($term[0], -1) !== "'") { 724 if (strpos($term[0], '.') !== false) { 725 if ( ! is_numeric($term[0])) { 726 $e = explode('.', $term[0]); 727 728 $field = array_pop($e); 729 730 if ($this->getType() === Doctrine_Query::SELECT) { 731 $componentAlias = implode('.', $e); 732 733 if (empty($componentAlias)) { 734 $componentAlias = $this->getRootAlias(); 735 } 736 737 $this->load($componentAlias); 738 739 // check the existence of the component alias 740 if ( ! isset($this->_queryComponents[$componentAlias])) { 741 throw new Doctrine_Query_Exception('Unknown component alias ' . $componentAlias); 742 } 743 744 $table = $this->_queryComponents[$componentAlias]['table']; 745 746 $def = $table->getDefinitionOf($field); 747 748 // get the actual field name from alias 749 $field = $table->getColumnName($field); 750 751 // check column existence 752 if ( ! $def) { 753 throw new Doctrine_Query_Exception('Unknown column ' . $field); 754 } 755 756 if (isset($def['owner'])) { 757 $componentAlias = $componentAlias . '.' . $def['owner']; 758 } 759 760 $tableAlias = $this->getSqlTableAlias($componentAlias); 761 762 // build sql expression 763 $term[0] = $this->_conn->quoteIdentifier($tableAlias) 764 . '.' 765 . $this->_conn->quoteIdentifier($field); 766 } else { 767 // build sql expression 768 $field = $this->getRoot()->getColumnName($field); 769 $term[0] = $this->_conn->quoteIdentifier($field); 770 } 771 } 772 } else { 773 if ( ! empty($term[0]) && ! in_array(strtoupper($term[0]), self::$_keywords) && 774 ! is_numeric($term[0]) && $term[0] !== '?' && substr($term[0], 0, 1) !== ':') { 775 776 $componentAlias = $this->getRootAlias(); 777 778 $found = false; 779 780 if ($componentAlias !== false && $componentAlias !== null) { 781 $table = $this->_queryComponents[$componentAlias]['table']; 782 783 // check column existence 784 if ($table->hasField($term[0])) { 785 $found = true; 786 787 $def = $table->getDefinitionOf($term[0]); 788 789 // get the actual column name from field name 790 $term[0] = $table->getColumnName($term[0]); 791 792 793 if (isset($def['owner'])) { 794 $componentAlias = $componentAlias . '.' . $def['owner']; 795 } 796 797 $tableAlias = $this->getSqlTableAlias($componentAlias); 798 799 if ($this->getType() === Doctrine_Query::SELECT) { 800 // build sql expression 801 $term[0] = $this->_conn->quoteIdentifier($tableAlias) 802 . '.' 803 . $this->_conn->quoteIdentifier($term[0]); 804 } else { 805 // build sql expression 806 $term[0] = $this->_conn->quoteIdentifier($term[0]); 807 } 808 } else { 809 $found = false; 810 } 811 } 812 813 if ( ! $found) { 814 $term[0] = $this->getSqlAggregateAlias($term[0]); 815 } 816 } 817 } 818 } 819 } 820 821 $str .= $term[0] . $term[1]; 822 } 823 return $str; 824 } 825 826 public function parseIdentifierReference($expr) 827 { 828 829 } 830 831 public function parseFunctionExpression($expr, $parseCallback = null) 832 { 833 $pos = strpos($expr, '('); 834 $name = substr($expr, 0, $pos); 835 836 if ($name === '') { 837 return $this->parseSubquery($expr); 838 } 839 840 $argStr = substr($expr, ($pos + 1), -1); 841 $args = array(); 842 // parse args 843 844 foreach ($this->_tokenizer->sqlExplode($argStr, ',') as $arg) { 845 $args[] = $parseCallback ? call_user_func_array($parseCallback, array($arg)) : $this->parseClause($arg); 846 } 847 848 // convert DQL function to its RDBMS specific equivalent 849 try { 850 $expr = call_user_func_array(array($this->_conn->expression, $name), $args); 851 } catch (Doctrine_Expression_Exception $e) { 852 throw new Doctrine_Query_Exception('Unknown function ' . $name . '.'); 853 } 854 855 return $expr; 856 } 857 858 859 public function parseSubquery($subquery) 860 { 861 $trimmed = trim($this->_tokenizer->bracketTrim($subquery)); 862 863 // check for possible subqueries 864 if (substr($trimmed, 0, 4) == 'FROM' || substr($trimmed, 0, 6) == 'SELECT') { 865 // parse subquery 866 $q = $this->createSubquery()->parseDqlQuery($trimmed); 867 $trimmed = $q->getSqlQuery(); 868 $q->free(); 869 } else if (substr($trimmed, 0, 4) == 'SQL:') { 870 $trimmed = substr($trimmed, 4); 871 } else { 872 $e = $this->_tokenizer->sqlExplode($trimmed, ','); 873 874 $value = array(); 875 $index = false; 876 877 foreach ($e as $part) { 878 $value[] = $this->parseClause($part); 879 } 880 881 $trimmed = implode(', ', $value); 882 } 883 884 return '(' . $trimmed . ')'; 885 } 886 887 888 /** 889 * processPendingSubqueries 890 * processes pending subqueries 891 * 892 * subqueries can only be processed when the query is fully constructed 893 * since some subqueries may be correlated 894 * 895 * @return void 896 * @todo Better description. i.e. What is a 'pending subquery'? What does 'processed' mean? 897 * (parsed? sql is constructed? some information is gathered?) 898 */ 899 public function processPendingSubqueries() 900 { 901 foreach ($this->_pendingSubqueries as $value) { 902 list($dql, $alias) = $value; 903 904 $subquery = $this->createSubquery(); 905 906 $sql = $subquery->parseDqlQuery($dql, false)->getQuery(); 907 $subquery->free(); 908 909 reset($this->_queryComponents); 910 $componentAlias = key($this->_queryComponents); 911 $tableAlias = $this->getSqlTableAlias($componentAlias); 912 913 $sqlAlias = $tableAlias . '__' . count($this->_aggregateAliasMap); 914 915 $this->_sqlParts['select'][] = '(' . $sql . ') AS ' . $this->_conn->quoteIdentifier($sqlAlias); 916 917 $this->_aggregateAliasMap[$alias] = $sqlAlias; 918 $this->_queryComponents[$componentAlias]['agg'][] = $alias; 919 } 920 $this->_pendingSubqueries = array(); 921 } 922 923 /** 924 * processPendingAggregates 925 * processes pending aggregate values for given component alias 926 * 927 * @return void 928 * @todo Better description. i.e. What is a 'pending aggregate'? What does 'processed' mean? 929 */ 930 public function processPendingAggregates() 931 { 932 // iterate trhough all aggregates 933 foreach ($this->_pendingAggregates as $aggregate) { 934 list ($expression, $components, $alias) = $aggregate; 935 936 $tableAliases = array(); 937 938 // iterate through the component references within the aggregate function 939 if ( ! empty ($components)) { 940 foreach ($components as $component) { 941 942 if (is_numeric($component)) { 943 continue; 944 } 945 946 $e = explode('.', $component); 947 948 $field = array_pop($e); 949 $componentAlias = implode('.', $e); 950 951 // check the existence of the component alias 952 if ( ! isset($this->_queryComponents[$componentAlias])) { 953 throw new Doctrine_Query_Exception('Unknown component alias ' . $componentAlias); 954 } 955 956 $table = $this->_queryComponents[$componentAlias]['table']; 957 958 $field = $table->getColumnName($field); 959 960 // check column existence 961 if ( ! $table->hasColumn($field)) { 962 throw new Doctrine_Query_Exception('Unknown column ' . $field); 963 } 964 965 $sqlTableAlias = $this->getSqlTableAlias($componentAlias); 966 967 $tableAliases[$sqlTableAlias] = true; 968 969 // build sql expression 970 971 $identifier = $this->_conn->quoteIdentifier($sqlTableAlias . '.' . $field); 972 $expression = str_replace($component, $identifier, $expression); 973 } 974 } 975 976 if (count($tableAliases) !== 1) { 977 $componentAlias = reset($this->_tableAliasMap); 978 $tableAlias = key($this->_tableAliasMap); 979 } 980 981 $index = count($this->_aggregateAliasMap); 982 $sqlAlias = $this->_conn->quoteIdentifier($tableAlias . '__' . $index); 983 984 $this->_sqlParts['select'][] = $expression . ' AS ' . $sqlAlias; 985 986 $this->_aggregateAliasMap[$alias] = $sqlAlias; 987 $this->_expressionMap[$alias][0] = $expression; 988 989 $this->_queryComponents[$componentAlias]['agg'][$index] = $alias; 990 991 $this->_neededTables[] = $tableAlias; 992 } 993 // reset the state 994 $this->_pendingAggregates = array(); 995 } 996 997 /** 998 * _buildSqlQueryBase 999 * returns the base of the generated sql query 1000 * On mysql driver special strategy has to be used for DELETE statements 1001 * (where is this special strategy??) 1002 * 1003 * @return string the base of the generated sql query 1004 */ 1005 protected function _buildSqlQueryBase() 1006 { 1007 switch ($this->_type) { 1008 case self::DELETE: 1009 $q = 'DELETE FROM '; 1010 break; 1011 case self::UPDATE: 1012 $q = 'UPDATE '; 1013 break; 1014 case self::SELECT: 1015 $distinct = ($this->_sqlParts['distinct']) ? 'DISTINCT ' : ''; 1016 $q = 'SELECT ' . $distinct . implode(', ', $this->_sqlParts['select']) . ' FROM '; 1017 break; 1018 } 1019 return $q; 1020 } 1021 1022 /** 1023 * _buildSqlFromPart 1024 * builds the from part of the query and returns it 1025 * 1026 * @return string the query sql from part 1027 */ 1028 protected function _buildSqlFromPart($ignorePending = false) 1029 { 1030 $q = ''; 1031 1032 foreach ($this->_sqlParts['from'] as $k => $part) { 1033 $e = explode(' ', $part); 1034 1035 if ($k === 0) { 1036 if ( ! $ignorePending && $this->_type == self::SELECT) { 1037 // We may still have pending conditions 1038 $alias = count($e) > 1 1039 ? $this->getComponentAlias($e[1]) 1040 : null; 1041 $where = $this->_processPendingJoinConditions($alias); 1042 1043 // apply inheritance to WHERE part 1044 if ( ! empty($where)) { 1045 if (count($this->_sqlParts['where']) > 0) { 1046 $this->_sqlParts['where'][] = 'AND'; 1047 } 1048 1049 if (substr($where, 0, 1) === '(' && substr($where, -1) === ')') { 1050 $this->_sqlParts['where'][] = $where; 1051 } else { 1052 $this->_sqlParts['where'][] = '(' . $where . ')'; 1053 } 1054 } 1055 } 1056 1057 $q .= $part; 1058 1059 continue; 1060 } 1061 1062 // preserve LEFT JOINs only if needed 1063 // Check if it's JOIN, if not add a comma separator instead of space 1064 if ( ! preg_match('/\bJOIN\b/i', $part) && ! isset($this->_pendingJoinConditions[$k])) { 1065 $q .= ', ' . $part; 1066 } else { 1067 if (substr($part, 0, 9) === 'LEFT JOIN') { 1068 $aliases = array_merge($this->_subqueryAliases, 1069 array_keys($this->_neededTables)); 1070 1071 if ( ! in_array($e[3], $aliases) && ! in_array($e[2], $aliases) && ! empty($this->_pendingFields)) { 1072 continue; 1073 } 1074 1075 } 1076 1077 if ( ! $ignorePending && isset($this->_pendingJoinConditions[$k])) { 1078 if (strpos($part, ' ON ') !== false) { 1079 $part .= ' AND '; 1080 } else { 1081 $part .= ' ON '; 1082 } 1083 1084 $part .= $this->_processPendingJoinConditions($k); 1085 } 1086 1087 $componentAlias = $this->getComponentAlias($e[3]); 1088 $string = $this->getInheritanceCondition($componentAlias); 1089 1090 if ($string) { 1091 $part = $part . ' AND ' . $string; 1092 } 1093 $q .= ' ' . $part; 1094 } 1095 1096 $this->_sqlParts['from'][$k] = $part; 1097 } 1098 return $q; 1099 } 1100 1101 /** 1102 * Processes the pending join conditions, used for dynamically add conditions 1103 * to root component/joined components without interfering in the main dql 1104 * handling. 1105 * 1106 * @param string $alias Component Alias 1107 * @return Processed pending conditions 1108 */ 1109 protected function _processPendingJoinConditions($alias) 1110 { 1111 $parts = array(); 1112 1113 if ($alias !== null && isset($this->_pendingJoinConditions[$alias])) { 1114 $parser = new Doctrine_Query_JoinCondition($this, $this->_tokenizer); 1115 1116 foreach ($this->_pendingJoinConditions[$alias] as $joinCondition) { 1117 $parts[] = $parser->parse($joinCondition); 1118 } 1119 1120 // FIX #1860 and #1876: Cannot unset them, otherwise query cannot be reused later 1121 //unset($this->_pendingJoinConditions[$alias]); 1122 } 1123 1124 return (count($parts) > 0 ? '(' . implode(') AND (', $parts) . ')' : ''); 1125 } 1126 1127 /** 1128 * builds the sql query from the given parameters and applies things such as 1129 * column aggregation inheritance and limit subqueries if needed 1130 * 1131 * @param array $params an array of prepared statement params (needed only in mysql driver 1132 * when limit subquery algorithm is used) 1133 * @param bool $limitSubquery Whether or not to try and apply the limit subquery algorithm 1134 * @return string the built sql query 1135 */ 1136 public function getSqlQuery($params = array(), $limitSubquery = true) 1137 { 1138 // Assign building/execution specific params 1139 $this->_params['exec'] = $params; 1140 1141 // Initialize prepared parameters array 1142 $this->_execParams = $this->getFlattenedParams(); 1143 1144 if ($this->_state !== self::STATE_DIRTY) { 1145 $this->fixArrayParameterValues($this->getInternalParams()); 1146 1147 // Return compiled SQL 1148 return $this->_sql; 1149 } 1150 return $this->buildSqlQuery($limitSubquery); 1151 } 1152 1153 /** 1154 * Build the SQL query from the DQL 1155 * 1156 * @param bool $limitSubquery Whether or not to try and apply the limit subquery algorithm 1157 * @return string $sql The generated SQL string 1158 */ 1159 public function buildSqlQuery($limitSubquery = true) 1160 { 1161 // reset the state 1162 if ( ! $this->isSubquery()) { 1163 $this->_queryComponents = array(); 1164 $this->_pendingAggregates = array(); 1165 $this->_aggregateAliasMap = array(); 1166 } 1167 1168 $this->reset(); 1169 1170 // invoke the preQuery hook 1171 $this->_preQuery(); 1172 1173 // process the DQL parts => generate the SQL parts. 1174 // this will also populate the $_queryComponents. 1175 foreach ($this->_dqlParts as $queryPartName => $queryParts) { 1176 // If we are parsing FROM clause, we'll need to diff the queryComponents later 1177 if ($queryPartName == 'from') { 1178 // Pick queryComponents before processing 1179 $queryComponentsBefore = $this->getQueryComponents(); 1180 } 1181 1182 // FIX #1667: _sqlParts are cleaned inside _processDqlQueryPart. 1183 if ($queryPartName != 'forUpdate') { 1184 $this->_processDqlQueryPart($queryPartName, $queryParts); 1185 } 1186 1187 // We need to define the root alias 1188 if ($queryPartName == 'from') { 1189 // Pick queryComponents aftr processing 1190 $queryComponentsAfter = $this->getQueryComponents(); 1191 1192 // Root alias is the key of difference of query components 1193 $diffQueryComponents = array_diff_key($queryComponentsAfter, $queryComponentsBefore); 1194 $this->_rootAlias = key($diffQueryComponents); 1195 } 1196 } 1197 $this->_state = self::STATE_CLEAN; 1198 1199 // Proceed with the generated SQL 1200 if (empty($this->_sqlParts['from'])) { 1201 return false; 1202 } 1203 1204 $needsSubQuery = false; 1205 $subquery = ''; 1206 $map = $this->getRootDeclaration(); 1207 $table = $map['table']; 1208 $rootAlias = $this->getRootAlias(); 1209 1210 if ( ! empty($this->_sqlParts['limit']) && $this->_needsSubquery && 1211 $table->getAttribute(Doctrine_Core::ATTR_QUERY_LIMIT) == Doctrine_Core::LIMIT_RECORDS) { 1212 // We do not need a limit-subquery if DISTINCT is used 1213 // and the selected fields are either from the root component or from a localKey relation (hasOne) 1214 // (i.e. DQL: SELECT DISTINCT u.id FROM User u LEFT JOIN u.phonenumbers LIMIT 5). 1215 if(!$this->_sqlParts['distinct']) { 1216 $this->_isLimitSubqueryUsed = true; 1217 $needsSubQuery = true; 1218 } else { 1219 foreach( array_keys($this->_pendingFields) as $alias){ 1220 //no subquery for root fields 1221 if($alias == $this->getRootAlias()){ 1222 continue; 1223 } 1224 1225 //no subquery for ONE relations 1226 if(isset($this->_queryComponents[$alias]['relation']) && 1227 $this->_queryComponents[$alias]['relation']->getType() == Doctrine_Relation::ONE){ 1228 continue; 1229 } 1230 1231 $this->_isLimitSubqueryUsed = true; 1232 $needsSubQuery = true; 1233 } 1234 } 1235 } 1236 1237 $sql = array(); 1238 1239 if ( ! empty($this->_pendingFields)) { 1240 foreach ($this->_queryComponents as $alias => $map) { 1241 $fieldSql = $this->processPendingFields($alias); 1242 if ( ! empty($fieldSql)) { 1243 $sql[] = $fieldSql; 1244 } 1245 } 1246 } 1247 1248 if ( ! empty($sql)) { 1249 array_unshift($this->_sqlParts['select'], implode(', ', $sql)); 1250 } 1251 1252 $this->_pendingFields = array(); 1253 1254 // build the basic query 1255 $q = $this->_buildSqlQueryBase(); 1256 $q .= $this->_buildSqlFromPart(); 1257 1258 if ( ! empty($this->_sqlParts['set'])) { 1259 $q .= ' SET ' . implode(', ', $this->_sqlParts['set']); 1260 } 1261 1262 $string = $this->getInheritanceCondition($this->getRootAlias()); 1263 1264 // apply inheritance to WHERE part 1265 if ( ! empty($string)) { 1266 if (count($this->_sqlParts['where']) > 0) { 1267 $this->_sqlParts['where'][] = 'AND'; 1268 } 1269 1270 if (substr($string, 0, 1) === '(' && substr($string, -1) === ')') { 1271 $this->_sqlParts['where'][] = $string; 1272 } else { 1273 $this->_sqlParts['where'][] = '(' . $string . ')'; 1274 } 1275 } 1276 1277 $modifyLimit = true; 1278 $limitSubquerySql = ''; 1279 1280 if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery) { 1281 $subquery = $this->getLimitSubquery(); 1282 1283 // what about composite keys? 1284 $idColumnName = $table->getColumnName($table->getIdentifier()); 1285 1286 switch (strtolower($this->_conn->getDriverName())) { 1287 case 'mysql': 1288 $this->useQueryCache(false); 1289 1290 // mysql doesn't support LIMIT in subqueries 1291 $list = $this->_conn->execute($subquery, $this->_execParams)->fetchAll(Doctrine_Core::FETCH_COLUMN); 1292 foreach ($list as &$v) { 1293 $v = $this->_conn->quote($v); 1294 } 1295 $subquery = implode(', ', $list); 1296 1297 break; 1298 1299 case 'pgsql': 1300 $subqueryAlias = $this->_conn->quoteIdentifier('doctrine_subquery_alias'); 1301 1302 // pgsql needs special nested LIMIT subquery 1303 $subquery = 'SELECT ' . $subqueryAlias . '.' . $this->_conn->quoteIdentifier($idColumnName) 1304 . ' FROM (' . $subquery . ') AS ' . $subqueryAlias; 1305 1306 break; 1307 } 1308 1309 $field = $this->getSqlTableAlias($rootAlias) . '.' . $idColumnName; 1310 1311 // FIX #1868: If not ID under MySQL is found to be restricted, restrict pk column for null 1312 // (which will lead to a return of 0 items) 1313 $limitSubquerySql = $this->_conn->quoteIdentifier($field) 1314 . (( ! empty($subquery)) ? ' IN (' . $subquery . ')' : ' IS NULL') 1315 . ((count($this->_sqlParts['where']) > 0) ? ' AND ' : ''); 1316 1317 $modifyLimit = false; 1318 } 1319 1320 // FIX #DC-26: Include limitSubquerySql as major relevance in conditions 1321 $emptyWhere = empty($this->_sqlParts['where']); 1322 1323 if ( ! ($emptyWhere && $limitSubquerySql == '')) { 1324 $where = implode(' ', $this->_sqlParts['where']); 1325 $where = ($where == '' || (substr($where, 0, 1) === '(' && substr($where, -1) === ')')) 1326 ? $where : '(' . $where . ')'; 1327 1328 $q .= ' WHERE ' . $limitSubquerySql . $where; 1329 // . (($limitSubquerySql == '' && count($this->_sqlParts['where']) == 1) ? substr($where, 1, -1) : $where); 1330 } 1331 1332 // Fix the orderbys so we only have one orderby per value 1333 foreach ($this->_sqlParts['orderby'] as $k => $orderBy) { 1334 $e = explode(', ', $orderBy); 1335 unset($this->_sqlParts['orderby'][$k]); 1336 foreach ($e as $v) { 1337 $this->_sqlParts['orderby'][] = $v; 1338 } 1339 } 1340 1341 // Add the default orderBy statements defined in the relationships and table classes 1342 // Only do this for SELECT queries 1343 if ($this->_type === self::SELECT) { 1344 foreach ($this->_queryComponents as $alias => $map) { 1345 $sqlAlias = $this->getSqlTableAlias($alias); 1346 if (isset($map['relation'])) { 1347 $orderBy = $map['relation']->getOrderByStatement($sqlAlias, true); 1348 if ($orderBy == $map['relation']['orderBy']) { 1349 if (isset($map['ref'])) { 1350 $orderBy = $map['relation']['refTable']->processOrderBy($sqlAlias, $map['relation']['orderBy'], true); 1351 } else { 1352 $orderBy = null; 1353 } 1354 } 1355 } else { 1356 $orderBy = $map['table']->getOrderByStatement($sqlAlias, true); 1357 } 1358 1359 if ($orderBy) { 1360 $e = explode(',', $orderBy); 1361 foreach ($e as $v) { 1362 $v = trim($v); 1363 if ( ! in_array($v, $this->_sqlParts['orderby'])) { 1364 $this->_sqlParts['orderby'][] = $v; 1365 } 1366 } 1367 } 1368 } 1369 } 1370 1371 $q .= ( ! empty($this->_sqlParts['groupby'])) ? ' GROUP BY ' . implode(', ', $this->_sqlParts['groupby']) : ''; 1372 $q .= ( ! empty($this->_sqlParts['having'])) ? ' HAVING ' . implode(' AND ', $this->_sqlParts['having']): ''; 1373 $q .= ( ! empty($this->_sqlParts['orderby'])) ? ' ORDER BY ' . implode(', ', $this->_sqlParts['orderby']) : ''; 1374 1375 if ($modifyLimit) { 1376 $q = $this->_conn->modifyLimitQuery($q, $this->_sqlParts['limit'], $this->_sqlParts['offset'], false, false, $this); 1377 } 1378 1379 $q .= $this->_sqlParts['forUpdate'] === true ? ' FOR UPDATE ' : ''; 1380 1381 $this->_sql = $q; 1382 1383 $this->clear(); 1384 1385 return $q; 1386 } 1387 1388 /** 1389 * getLimitSubquery 1390 * this is method is used by the record limit algorithm 1391 * 1392 * when fetching one-to-many, many-to-many associated data with LIMIT clause 1393 * an additional subquery is needed for limiting the number of returned records instead 1394 * of limiting the number of sql result set rows 1395 * 1396 * @return string the limit subquery 1397 * @todo A little refactor to make the method easier to understand & maybe shorter? 1398 */ 1399 public function getLimitSubquery() 1400 { 1401 $map = reset($this->_queryComponents); 1402 $table = $map['table']; 1403 $componentAlias = key($this->_queryComponents); 1404 1405 // get short alias 1406 $alias = $this->getSqlTableAlias($componentAlias); 1407 // what about composite keys? 1408 $primaryKey = $alias . '.' . $table->getColumnName($table->getIdentifier()); 1409 1410 $driverName = $this->_conn->getAttribute(Doctrine_Core::ATTR_DRIVER_NAME); 1411 1412 // initialize the base of the subquery 1413 if (($driverName == 'oracle' || $driverName == 'oci' || $driverName == 'oci8') && $this->_isOrderedByJoinedColumn()) { 1414 $subquery = 'SELECT '; 1415 } else { 1416 $subquery = 'SELECT DISTINCT '; 1417 } 1418 $subquery .= $this->_conn->quoteIdentifier($primaryKey); 1419 1420 // pgsql & oracle need the order by fields to be preserved in select clause 1421 if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'oci8' || $driverName == 'mssql' || $driverName == 'odbc') { 1422 foreach ($this->_sqlParts['orderby'] as $part) { 1423 // Remove identifier quoting if it exists 1424 $e = $this->_tokenizer->bracketExplode($part, ' '); 1425 foreach ($e as $f) { 1426 if ($f == 0 || $f % 2 == 0) { 1427 $partOriginal = str_replace(',', '', trim($f)); 1428 $e = explode('.', $partOriginal); 1429 foreach ($e as &$v) { 1430 $v = trim($v, '[]`"'); 1431 } 1432 $part = trim(implode('.', $e)); 1433 1434 if (strpos($part, '.') === false) { 1435 continue; 1436 } 1437 1438 // don't add functions 1439 if (strpos($part, '(') !== false) { 1440 continue; 1441 } 1442 1443 // don't add primarykey column (its already in the select clause) 1444 if ($part !== $primaryKey) { 1445 $subquery .= ', ' . $partOriginal; 1446 } 1447 } 1448 } 1449 } 1450 } 1451 1452 $orderby = $this->_sqlParts['orderby']; 1453 $having = $this->_sqlParts['having']; 1454 if ($driverName == 'mysql' || $driverName == 'pgsql') { 1455 foreach ($this->_expressionMap as $dqlAlias => $expr) { 1456 if (isset($expr[1])) { 1457 $subquery .= ', ' . $expr[0] . ' AS ' . $this->_aggregateAliasMap[$dqlAlias]; 1458 } 1459 } 1460 } else { 1461 foreach ($this->_expressionMap as $dqlAlias => $expr) { 1462 if (isset($expr[1])) { 1463 foreach ($having as $k => $v) { 1464 $having[$k] = str_replace($this->_aggregateAliasMap[$dqlAlias], $expr[0], $v); 1465 } 1466 foreach ($orderby as $k => $v) { 1467 $e = explode(' ', $v); 1468 if ($e[0] == $this->_aggregateAliasMap[$dqlAlias]) { 1469 $orderby[$k] = $expr[0]; 1470 } 1471 } 1472 } 1473 } 1474 } 1475 1476 // Add having fields that got stripped out of select 1477 preg_match_all('/`[a-z0-9_]+`\.`[a-z0-9_]+`/i', implode(' ', $having), $matches, PREG_PATTERN_ORDER); 1478 if (count($matches[0]) > 0) { 1479 $subquery .= ', ' . implode(', ', array_unique($matches[0])); 1480 } 1481 1482 $subquery .= ' FROM'; 1483 1484 foreach ($this->_sqlParts['from'] as $part) { 1485 // preserve LEFT JOINs only if needed 1486 if (substr($part, 0, 9) === 'LEFT JOIN') { 1487 $e = explode(' ', $part); 1488 // Fix for http://www.doctrine-project.org/jira/browse/DC-706 1489 // Fix for http://www.doctrine-project.org/jira/browse/DC-594 1490 if (empty($this->_sqlParts['orderby']) && empty($this->_sqlParts['where']) && empty($this->_sqlParts['having']) && empty($this->_sqlParts['groupby'])) { 1491 continue; 1492 } 1493 } 1494 1495 $subquery .= ' ' . $part; 1496 } 1497 1498 // all conditions must be preserved in subquery 1499 $subquery .= ( ! empty($this->_sqlParts['where']))? ' WHERE ' . implode(' ', $this->_sqlParts['where']) : ''; 1500 $subquery .= ( ! empty($this->_sqlParts['groupby']))? ' GROUP BY ' . implode(', ', $this->_sqlParts['groupby']) : ''; 1501 $subquery .= ( ! empty($having))? ' HAVING ' . implode(' AND ', $having) : ''; 1502 $subquery .= ( ! empty($orderby))? ' ORDER BY ' . implode(', ', $orderby) : ''; 1503 1504 if (($driverName == 'oracle' || $driverName == 'oci' || $driverName == 'oci8') && $this->_isOrderedByJoinedColumn()) { 1505 // When using "ORDER BY x.foo" where x.foo is a column of a joined table, 1506 // we may get duplicate primary keys because all columns in ORDER BY must appear 1507 // in the SELECT list when using DISTINCT. Hence we need to filter out the 1508 // primary keys with an additional DISTINCT subquery. 1509 // #1038 1510 $quotedIdentifierColumnName = $this->_conn->quoteIdentifier($table->getColumnName($table->getIdentifier())); 1511 $subquery = 'SELECT doctrine_subquery_alias.' . $quotedIdentifierColumnName 1512 . ' FROM (' . $subquery . ') doctrine_subquery_alias' 1513 . ' GROUP BY doctrine_subquery_alias.' . $quotedIdentifierColumnName 1514 . ' ORDER BY MIN(ROWNUM)'; 1515 } 1516 1517 // add driver specific limit clause 1518 $subquery = $this->_conn->modifyLimitSubquery($table, $subquery, $this->_sqlParts['limit'], $this->_sqlParts['offset']); 1519 1520 $parts = $this->_tokenizer->quoteExplode($subquery, ' ', "'", "'"); 1521 1522 foreach ($parts as $k => $part) { 1523 if (strpos($part, ' ') !== false) { 1524 continue; 1525 } 1526 1527 $part = str_replace(array('"', "'", '`'), "", $part); 1528 1529 // Fix DC-645, Table aliases ending with ')' where not replaced properly 1530 preg_match('/^(\(?)(.*?)(\)?)$/', $part, $matches); 1531 if ($this->hasSqlTableAlias($matches[2])) { 1532 $parts[$k] = $matches[1].$this->_conn->quoteIdentifier($this->generateNewSqlTableAlias($matches[2])).$matches[3]; 1533 continue; 1534 } 1535 1536 if (strpos($part, '.') === false) { 1537 continue; 1538 } 1539 1540 preg_match_all("/[a-zA-Z0-9_]+\.[a-z0-9_]+/i", $part, $m); 1541 1542 foreach ($m[0] as $match) { 1543 $e = explode('.', $match); 1544 1545 // Rebuild the original part without the newly generate alias and with quoting reapplied 1546 $e2 = array(); 1547 foreach ($e as $k2 => $v2) { 1548 $e2[$k2] = $this->_conn->quoteIdentifier($v2); 1549 } 1550 $match = implode('.', $e2); 1551 1552 // Generate new table alias 1553 $e[0] = $this->generateNewSqlTableAlias($e[0]); 1554 1555 // Requote the part with the newly generated alias 1556 foreach ($e as $k2 => $v2) { 1557 $e[$k2] = $this->_conn->quoteIdentifier($v2); 1558 } 1559 1560 $replace = implode('.' , $e); 1561 1562 // Replace the original part with the new part with new sql table alias 1563 $parts[$k] = str_replace($match, $replace, $parts[$k]); 1564 } 1565 } 1566 1567 if ($driverName == 'mysql' || $driverName == 'pgsql') { 1568 foreach ($parts as $k => $part) { 1569 if (strpos($part, "'") !== false) { 1570 continue; 1571 } 1572 if (strpos($part, '__') == false) { 1573 continue; 1574 } 1575 1576 preg_match_all("/[a-zA-Z0-9_]+\_\_[a-z0-9_]+/i", $part, $m); 1577 1578 foreach ($m[0] as $match) { 1579 $e = explode('__', $match); 1580 $e[0] = $this->generateNewSqlTableAlias($e[0]); 1581 1582 $parts[$k] = str_replace($match, implode('__', $e), $parts[$k]); 1583 } 1584 } 1585 } 1586 1587 $subquery = implode(' ', $parts); 1588 return $subquery; 1589 } 1590 1591 /** 1592 * Checks whether the query has an ORDER BY on a column of a joined table. 1593 * This information is needed in special scenarios like the limit-offset when its 1594 * used with an Oracle database. 1595 * 1596 * @return boolean TRUE if the query is ordered by a joined column, FALSE otherwise. 1597 */ 1598 private function _isOrderedByJoinedColumn() { 1599 if ( ! $this->_queryComponents) { 1600 throw new Doctrine_Query_Exception("The query is in an invalid state for this " 1601 . "operation. It must have been fully parsed first."); 1602 } 1603 $componentAlias = key($this->_queryComponents); 1604 $mainTableAlias = $this->getSqlTableAlias($componentAlias); 1605 foreach ($this->_sqlParts['orderby'] as $part) { 1606 $part = trim($part); 1607 $e = $this->_tokenizer->bracketExplode($part, ' '); 1608 $part = trim($e[0]); 1609 if (strpos($part, '.') === false) { 1610 continue; 1611 } 1612 list($tableAlias, $columnName) = explode('.', $part); 1613 if ($tableAlias != $mainTableAlias) { 1614 return true; 1615 } 1616 } 1617 return false; 1618 } 1619 1620 /** 1621 * DQL PARSER 1622 * parses a DQL query 1623 * first splits the query in parts and then uses individual 1624 * parsers for each part 1625 * 1626 * @param string $query DQL query 1627 * @param boolean $clear whether or not to clear the aliases 1628 * @throws Doctrine_Query_Exception if some generic parsing error occurs 1629 * @return Doctrine_Query 1630 */ 1631 public function parseDqlQuery($query, $clear = true) 1632 { 1633 if ($clear) { 1634 $this->clear(); 1635 } 1636 1637 $query = trim($query); 1638 $query = str_replace("\r", "\n", str_replace("\r\n", "\n", $query)); 1639 $query = str_replace("\n", ' ', $query); 1640 1641 $parts = $this->_tokenizer->tokenizeQuery($query); 1642 1643 foreach ($parts as $partName => $subParts) { 1644 $subParts = trim($subParts); 1645 $partName = strtolower($partName); 1646 switch ($partName) { 1647 case 'create': 1648 $this->_type = self::CREATE; 1649 break; 1650 case 'insert': 1651 $this->_type = self::INSERT; 1652 break; 1653 case 'delete': 1654 $this->_type = self::DELETE; 1655 break; 1656 case 'select': 1657 $this->_type = self::SELECT; 1658 $this->_addDqlQueryPart($partName, $subParts); 1659 break; 1660 case 'update': 1661 $this->_type = self::UPDATE; 1662 $partName = 'from'; 1663 case 'from': 1664 $this->_addDqlQueryPart($partName, $subParts); 1665 break; 1666 case 'set': 1667 $this->_addDqlQueryPart($partName, $subParts, true); 1668 break; 1669 case 'group': 1670 case 'order': 1671 $partName .= 'by'; 1672 case 'where': 1673 case 'having': 1674 case 'limit': 1675 case 'offset': 1676 $this->_addDqlQueryPart($partName, $subParts); 1677 break; 1678 } 1679 } 1680 1681 return $this; 1682 } 1683 1684 /** 1685 * @todo Describe & refactor... too long and nested. 1686 * @param string $path component alias 1687 * @param boolean $loadFields 1688 */ 1689 public function load($path, $loadFields = true) 1690 { 1691 if (isset($this->_queryComponents[$path])) { 1692 return $this->_queryComponents[$path]; 1693 } 1694 1695 $e = $this->_tokenizer->quoteExplode($path, ' INDEXBY '); 1696 1697 $mapWith = null; 1698 if (count($e) > 1) { 1699 $mapWith = trim($e[1]); 1700 1701 $path = $e[0]; 1702 } 1703 1704 // parse custom join conditions 1705 $e = explode(' ON ', str_ireplace(' on ', ' ON ', $path)); 1706 1707 $joinCondition = ''; 1708 1709 if (count($e) > 1) { 1710 $joinCondition = substr($path, strlen($e[0]) + 4, strlen($e[1])); 1711 $path = substr($path, 0, strlen($e[0])); 1712 1713 $overrideJoin = true; 1714 } else { 1715 $e = explode(' WITH ', str_ireplace(' with ', ' WITH ', $path)); 1716 1717 if (count($e) > 1) { 1718 $joinCondition = substr($path, strlen($e[0]) + 6, strlen($e[1])); 1719 $path = substr($path, 0, strlen($e[0])); 1720 } 1721 1722 $overrideJoin = false; 1723 } 1724 1725 $tmp = explode(' ', $path); 1726 $componentAlias = $originalAlias = (count($tmp) > 1) ? end($tmp) : null; 1727 1728 $e = preg_split("/[.:]/", $tmp[0], -1); 1729 1730 $fullPath = $tmp[0]; 1731 $prevPath = ''; 1732 $fullLength = strlen($fullPath); 1733 1734 if (isset($this->_queryComponents[$e[0]])) { 1735 $table = $this->_queryComponents[$e[0]]['table']; 1736 $componentAlias = $e[0]; 1737 1738 $prevPath = $parent = array_shift($e); 1739 } 1740 1741 foreach ($e as $key => $name) { 1742 // get length of the previous path 1743 $length = strlen($prevPath); 1744 1745 // build the current component path 1746 $prevPath = ($prevPath) ? $prevPath . '.' . $name : $name; 1747 1748 $delimeter = substr($fullPath, $length, 1); 1749 1750 // if an alias is not given use the current path as an alias identifier 1751 if (strlen($prevPath) === $fullLength && isset($originalAlias)) { 1752 $componentAlias = $originalAlias; 1753 } else { 1754 $componentAlias = $prevPath; 1755 } 1756 1757 // if the current alias already exists, skip it 1758 if (isset($this->_queryComponents[$componentAlias])) { 1759 throw new Doctrine_Query_Exception("Duplicate alias '$componentAlias' in query."); 1760 } 1761 1762 if ( ! isset($table)) { 1763 // process the root of the path 1764 1765 $table = $this->loadRoot($name, $componentAlias); 1766 } else { 1767 $join = ($delimeter == ':') ? 'INNER JOIN ' : 'LEFT JOIN '; 1768 1769 $relation = $table->getRelation($name); 1770 $localTable = $table; 1771 1772 $table = $relation->getTable(); 1773 $this->_queryComponents[$componentAlias] = array('table' => $table, 1774 'parent' => $parent, 1775 'relation' => $relation, 1776 'map' => null); 1777 // Fix for http://www.doctrine-project.org/jira/browse/DC-701 1778 if ( ! $relation->isOneToOne() && ! $this->disableLimitSubquery) { 1779 $this->_needsSubquery = true; 1780 } 1781 1782 $localAlias = $this->getSqlTableAlias($parent, $localTable->getTableName()); 1783 $foreignAlias = $this->getSqlTableAlias($componentAlias, $relation->getTable()->getTableName()); 1784 1785 $foreignSql = $this->_conn->quoteIdentifier($relation->getTable()->getTableName()) 1786 . ' ' 1787 . $this->_conn->quoteIdentifier($foreignAlias); 1788 1789 $map = $relation->getTable()->inheritanceMap; 1790 1791 if ( ! $loadFields || ! empty($map) || $joinCondition) { 1792 $this->_subqueryAliases[] = $foreignAlias; 1793 } 1794 1795 if ($relation instanceof Doctrine_Relation_Association) { 1796 $asf = $relation->getAssociationTable(); 1797 1798 $assocTableName = $asf->getTableName(); 1799 1800 if ( ! $loadFields || ! empty($map) || $joinCondition) { 1801 $this->_subqueryAliases[] = $assocTableName; 1802 } 1803 1804 $assocPath = $prevPath . '.' . $asf->getComponentName() . ' ' . $componentAlias; 1805 1806 $this->_queryComponents[$assocPath] = array( 1807 'parent' => $prevPath, 1808 'relation' => $relation, 1809 'table' => $asf, 1810 'ref' => true); 1811 1812 $assocAlias = $this->getSqlTableAlias($assocPath, $asf->getTableName()); 1813 1814 $queryPart = $join 1815 . $this->_conn->quoteIdentifier($assocTableName) 1816 . ' ' 1817 . $this->_conn->quoteIdentifier($assocAlias); 1818 1819 $queryPart .= ' ON (' . $this->_conn->quoteIdentifier($localAlias 1820 . '.' 1821 . $localTable->getColumnName($localTable->getIdentifier())) // what about composite keys? 1822 . ' = ' 1823 . $this->_conn->quoteIdentifier($assocAlias . '.' . $relation->getLocalRefColumnName()); 1824 1825 if ($relation->isEqual()) { 1826 // equal nest relation needs additional condition 1827 $queryPart .= ' OR ' 1828 . $this->_conn->quoteIdentifier($localAlias 1829 . '.' 1830 . $table->getColumnName($table->getIdentifier())) 1831 . ' = ' 1832 . $this->_conn->quoteIdentifier($assocAlias . '.' . $relation->getForeignRefColumnName()); 1833 } 1834 1835 $queryPart .= ')'; 1836 1837 $this->_sqlParts['from'][] = $queryPart; 1838 1839 $queryPart = $join . $foreignSql; 1840 1841 if ( ! $overrideJoin) { 1842 $queryPart .= $this->buildAssociativeRelationSql($relation, $assocAlias, $foreignAlias, $localAlias); 1843 } 1844 } else { 1845 $queryPart = $this->buildSimpleRelationSql($relation, $foreignAlias, $localAlias, $overrideJoin, $join); 1846 } 1847 1848 $queryPart .= $this->buildInheritanceJoinSql($table->getComponentName(), $componentAlias); 1849 $this->_sqlParts['from'][$componentAlias] = $queryPart; 1850 1851 if ( ! empty($joinCondition)) { 1852 $this->addPendingJoinCondition($componentAlias, $joinCondition); 1853 } 1854 } 1855 1856 if ($loadFields) { 1857 $restoreState = false; 1858 1859 // load fields if necessary 1860 if ($loadFields && empty($this->_dqlParts['select'])) { 1861 $this->_pendingFields[$componentAlias] = array('*'); 1862 } 1863 } 1864 1865 $parent = $prevPath; 1866 } 1867 1868 $table = $this->_queryComponents[$componentAlias]['table']; 1869 1870 return $this->buildIndexBy($componentAlias, $mapWith); 1871 } 1872 1873 protected function buildSimpleRelationSql(Doctrine_Relation $relation, $foreignAlias, $localAlias, $overrideJoin, $join) 1874 { 1875 $queryPart = $join . $this->_conn->quoteIdentifier($relation->getTable()->getTableName()) 1876 . ' ' 1877 . $this->_conn->quoteIdentifier($foreignAlias); 1878 1879 if ( ! $overrideJoin) { 1880 $queryPart .= ' ON ' 1881 . $this->_conn->quoteIdentifier($localAlias . '.' . $relation->getLocalColumnName()) 1882 . ' = ' 1883 . $this->_conn->quoteIdentifier($foreignAlias . '.' . $relation->getForeignColumnName()); 1884 } 1885 1886 return $queryPart; 1887 } 1888 1889 protected function buildIndexBy($componentAlias, $mapWith = null) 1890 { 1891 $table = $this->_queryComponents[$componentAlias]['table']; 1892 1893 $indexBy = null; 1894 $column = false; 1895 1896 if (isset($mapWith)) { 1897 $terms = explode('.', $mapWith); 1898 1899 if (count($terms) == 1) { 1900 $indexBy = $terms[0]; 1901 } else if (count($terms) == 2) { 1902 $column = true; 1903 $indexBy = $terms[1]; 1904 } 1905 } else if ($table->getBoundQueryPart('indexBy') !== null) { 1906 $indexBy = $table->getBoundQueryPart('indexBy'); 1907 } 1908 1909 if ($indexBy !== null) { 1910 if ( $column && ! $table->hasColumn($table->getColumnName($indexBy))) { 1911 throw new Doctrine_Query_Exception("Couldn't use key mapping. Column " . $indexBy . " does not exist."); 1912 } 1913 1914 $this->_queryComponents[$componentAlias]['map'] = $indexBy; 1915 } 1916 1917 return $this->_queryComponents[$componentAlias]; 1918 } 1919 1920 1921 protected function buildAssociativeRelationSql(Doctrine_Relation $relation, $assocAlias, $foreignAlias, $localAlias) 1922 { 1923 $table = $relation->getTable(); 1924 1925 $queryPart = ' ON '; 1926 1927 if ($relation->isEqual()) { 1928 $queryPart .= '('; 1929 } 1930 1931 $localIdentifier = $table->getColumnName($table->getIdentifier()); 1932 1933 $queryPart .= $this->_conn->quoteIdentifier($foreignAlias . '.' . $localIdentifier) 1934 . ' = ' 1935 . $this->_conn->quoteIdentifier($assocAlias . '.' . $relation->getForeignRefColumnName()); 1936 1937 if ($relation->isEqual()) { 1938 $queryPart .= ' OR ' 1939 . $this->_conn->quoteIdentifier($foreignAlias . '.' . $localIdentifier) 1940 . ' = ' 1941 . $this->_conn->quoteIdentifier($assocAlias . '.' . $relation->getLocalRefColumnName()) 1942 . ') AND ' 1943 . $this->_conn->quoteIdentifier($foreignAlias . '.' . $localIdentifier) 1944 . ' != ' 1945 . $this->_conn->quoteIdentifier($localAlias . '.' . $localIdentifier); 1946 } 1947 1948 return $queryPart; 1949 } 1950 1951 /** 1952 * loadRoot 1953 * 1954 * @param string $name 1955 * @param string $componentAlias 1956 * @return Doctrine_Table 1957 * @todo DESCRIBE ME! 1958 * @todo this method is called only in Doctrine_Query class. Shouldn't be private or protected? 1959 */ 1960 public function loadRoot($name, $componentAlias) 1961 { 1962 // get the connection for the component 1963 $manager = Doctrine_Manager::getInstance(); 1964 if ( ! $this->_passedConn && $manager->hasConnectionForComponent($name)) { 1965 $this->_conn = $manager->getConnectionForComponent($name); 1966 } 1967 1968 $table = $this->_conn->getTable($name); 1969 $tableName = $table->getTableName(); 1970 1971 // get the short alias for this table 1972 $tableAlias = $this->getSqlTableAlias($componentAlias, $tableName); 1973 // quote table name 1974 $queryPart = $this->_conn->quoteIdentifier($tableName); 1975 1976 if ($this->_type === self::SELECT) { 1977 $queryPart .= ' ' . $this->_conn->quoteIdentifier($tableAlias); 1978 } 1979 1980 $this->_tableAliasMap[$tableAlias] = $componentAlias; 1981 1982 $queryPart .= $this->buildInheritanceJoinSql($name, $componentAlias); 1983 1984 $this->_sqlParts['from'][] = $queryPart; 1985 1986 $this->_queryComponents[$componentAlias] = array('table' => $table, 'map' => null); 1987 1988 return $table; 1989 } 1990 1991 /** 1992 * @todo DESCRIBE ME! 1993 * @param string $name component class name 1994 * @param string $componentAlias alias of the component in the dql 1995 * @return string query part 1996 */ 1997 public function buildInheritanceJoinSql($name, $componentAlias) 1998 { 1999 // get the connection for the component 2000 $manager = Doctrine_Manager::getInstance(); 2001 if ( ! $this->_passedConn && $manager->hasConnectionForComponent($name)) { 2002 $this->_conn = $manager->getConnectionForComponent($name); 2003 } 2004 2005 $table = $this->_conn->getTable($name); 2006 $tableName = $table->getTableName(); 2007 2008 // get the short alias for this table 2009 $tableAlias = $this->getSqlTableAlias($componentAlias, $tableName); 2010 2011 $queryPart = ''; 2012 2013 foreach ($table->getOption('joinedParents') as $parent) { 2014 $parentTable = $this->_conn->getTable($parent); 2015 2016 $parentAlias = $componentAlias . '.' . $parent; 2017 2018 // get the short alias for the parent table 2019 $parentTableAlias = $this->getSqlTableAlias($parentAlias, $parentTable->getTableName()); 2020 2021 $queryPart .= ' LEFT JOIN ' . $this->_conn->quoteIdentifier($parentTable->getTableName()) 2022 . ' ' . $this->_conn->quoteIdentifier($parentTableAlias) . ' ON '; 2023 2024 //Doctrine_Core::dump($table->getIdentifier()); 2025 foreach ((array) $table->getIdentifier() as $identifier) { 2026 $column = $table->getColumnName($identifier); 2027 2028 $queryPart .= $this->_conn->quoteIdentifier($tableAlias) 2029 . '.' . $this->_conn->quoteIdentifier($column) 2030 . ' = ' . $this->_conn->quoteIdentifier($parentTableAlias) 2031 . '.' . $this->_conn->quoteIdentifier($column); 2032 } 2033 } 2034 2035 return $queryPart; 2036 } 2037 2038 /** 2039 * Get count sql query for this Doctrine_Query instance. 2040 * 2041 * This method is used in Doctrine_Query::count() for returning an integer 2042 * for the number of records which will be returned when executed. 2043 * 2044 * @return string $q 2045 */ 2046 public function getCountSqlQuery() 2047 { 2048 // triggers dql parsing/processing 2049 $this->getSqlQuery(array(), false); // this is ugly 2050 2051 // initialize temporary variables 2052 $where = $this->_sqlParts['where']; 2053 $having = $this->_sqlParts['having']; 2054 $groupby = $this->_sqlParts['groupby']; 2055 2056 $rootAlias = $this->getRootAlias(); 2057 $tableAlias = $this->getSqlTableAlias($rootAlias); 2058 2059 // Build the query base 2060 $q = 'SELECT COUNT(*) AS ' . $this->_conn->quoteIdentifier('num_results') . ' FROM '; 2061 2062 // Build the from clause 2063 $from = $this->_buildSqlFromPart(true); 2064 2065 // Build the where clause 2066 $where = ( ! empty($where)) ? ' WHERE ' . implode(' ', $where) : ''; 2067 2068 // Build the group by clause 2069 $groupby = ( ! empty($groupby)) ? ' GROUP BY ' . implode(', ', $groupby) : ''; 2070 2071 // Build the having clause 2072 $having = ( ! empty($having)) ? ' HAVING ' . implode(' AND ', $having) : ''; 2073 2074 // Building the from clause and finishing query 2075 if (count($this->_queryComponents) == 1 && empty($having)) { 2076 $q .= $from . $where . $groupby . $having; 2077 } else { 2078 // Subselect fields will contain only the pk of root entity 2079 $ta = $this->_conn->quoteIdentifier($tableAlias); 2080 2081 $map = $this->getRootDeclaration(); 2082 $idColumnNames = $map['table']->getIdentifierColumnNames(); 2083 2084 $pkFields = $ta . '.' . implode(', ' . $ta . '.', $this->_conn->quoteMultipleIdentifier($idColumnNames)); 2085 2086 // We need to do some magic in select fields if the query contain anything in having clause 2087 $selectFields = $pkFields; 2088 2089 if ( ! empty($having)) { 2090 // For each field defined in select clause 2091 foreach ($this->_sqlParts['select'] as $field) { 2092 // We only include aggregate expressions to count query 2093 // This is needed because HAVING clause will use field aliases 2094 if (strpos($field, '(') !== false) { 2095 $selectFields .= ', ' . $field; 2096 } 2097 } 2098 // Add having fields that got stripped out of select 2099 preg_match_all('/`[a-z0-9_]+`\.`[a-z0-9_]+`/i', $having, $matches, PREG_PATTERN_ORDER); 2100 if (count($matches[0]) > 0) { 2101 $selectFields .= ', ' . implode(', ', array_unique($matches[0])); 2102 } 2103 } 2104 2105 // If we do not have a custom group by, apply the default one 2106 if (empty($groupby)) { 2107 $groupby = ' GROUP BY ' . $pkFields; 2108 } 2109 2110 $q .= '(SELECT ' . $selectFields . ' FROM ' . $from . $where . $groupby . $having . ') ' 2111 . $this->_conn->quoteIdentifier('dctrn_count_query'); 2112 } 2113 2114 return $q; 2115 } 2116 2117 /** 2118 * Fetches the count of the query. 2119 * 2120 * This method executes the main query without all the 2121 * selected fields, ORDER BY part, LIMIT part and OFFSET part. 2122 * 2123 * Example: 2124 * Main query: 2125 * SELECT u.*, p.phonenumber FROM User u 2126 * LEFT JOIN u.Phonenumber p 2127 * WHERE p.phonenumber = '123 123' LIMIT 10 2128 * 2129 * The modified DQL query: 2130 * SELECT COUNT(DISTINCT u.id) FROM User u 2131 * LEFT JOIN u.Phonenumber p 2132 * WHERE p.phonenumber = '123 123' 2133 * 2134 * @param array $params an array of prepared statement parameters 2135 * @return integer the count of this query 2136 */ 2137 public function count($params = array()) 2138 { 2139 $q = $this->getCountSqlQuery(); 2140 $params = $this->getCountQueryParams($params); 2141 $params = $this->_conn->convertBooleans($params); 2142 2143 if ($this->_resultCache) { 2144 $conn = $this->getConnection(); 2145 $cacheDriver = $this->getResultCacheDriver(); 2146 $hash = $this->getResultCacheHash($params).'_count'; 2147 $cached = ($this->_expireResultCache) ? false : $cacheDriver->fetch($hash); 2148 2149 if ($cached === false) { 2150 // cache miss 2151 $results = $this->getConnection()->fetchAll($q, $params); 2152 $cacheDriver->save($hash, serialize($results), $this->getResultCacheLifeSpan()); 2153 } else { 2154 $results = unserialize($cached); 2155 } 2156 } else { 2157 $results = $this->getConnection()->fetchAll($q, $params); 2158 } 2159 2160 if (count($results) > 1) { 2161 $count = count($results); 2162 } else { 2163 if (isset($results[0])) { 2164 $results[0] = array_change_key_case($results[0], CASE_LOWER); 2165 $count = $results[0]['num_results']; 2166 } else { 2167 $count = 0; 2168 } 2169 } 2170 2171 return (int) $count; 2172 } 2173 2174 /** 2175 * Queries the database with DQL (Doctrine Query Language). 2176 * 2177 * This methods parses a Dql query and builds the query parts. 2178 * 2179 * @param string $query Dql query 2180 * @param array $params prepared statement parameters 2181 * @param int $hydrationMode Doctrine_Core::HYDRATE_ARRAY or Doctrine_Core::HYDRATE_RECORD 2182 * @see Doctrine_Core::FETCH_* constants 2183 * @return mixed 2184 */ 2185 public function query($query, $params = array(), $hydrationMode = null) 2186 { 2187 $this->parseDqlQuery($query); 2188 return $this->execute($params, $hydrationMode); 2189 } 2190 2191 /** 2192 * Copies a Doctrine_Query object. 2193 * 2194 * @return Doctrine_Query Copy of the Doctrine_Query instance. 2195 */ 2196 public function copy(Doctrine_Query $query = null) 2197 { 2198 if ( ! $query) { 2199 $query = $this; 2200 } 2201 2202 $new = clone $query; 2203 2204 return $new; 2205 } 2206 2207 /** 2208 * Magic method called after cloning process. 2209 * 2210 * @return void 2211 */ 2212 public function __clone() 2213 { 2214 $this->_parsers = array(); 2215 $this->_hydrator = clone $this->_hydrator; 2216 2217 // Subqueries share some information from the parent so it can intermingle 2218 // with the dql of the main query. So when a subquery is cloned we need to 2219 // kill those references or it causes problems 2220 if ($this->isSubquery()) { 2221 $this->_killReference('_params'); 2222 $this->_killReference('_tableAliasMap'); 2223 $this->_killReference('_queryComponents'); 2224 } 2225 } 2226 2227 /** 2228 * Kill the reference for the passed class property. 2229 * This method simply copies the value to a temporary variable and then unsets 2230 * the reference and re-assigns the old value but not by reference 2231 * 2232 * @param string $key 2233 */ 2234 protected function _killReference($key) 2235 { 2236 $tmp = $this->$key; 2237 unset($this->$key); 2238 $this->$key = $tmp; 2239 } 2240 2241 /** 2242 * Frees the resources used by the query object. It especially breaks a 2243 * cyclic reference between the query object and it's parsers. This enables 2244 * PHP's current GC to reclaim the memory. 2245 * This method can therefore be used to reduce memory usage when creating 2246 * a lot of query objects during a request. 2247 * 2248 * @return Doctrine_Query this object 2249 */ 2250 public function free() 2251 { 2252 $this->reset(); 2253 $this->_parsers = array(); 2254 $this->_dqlParts = array(); 2255 } 2256} 2257