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