[#DC-815] Model's default sorting breaks subqueries

[DC-815] Model's default sorting breaks subqueries Created: 11/Aug/10 Updated: 14/Mar/11
Status:	Open
Project:	Doctrine 1
Component/s:	Query, Record
Affects Version/s:	1.2.2
Fix Version/s:	None

Type:

Bug

Priority:

Critical

Reporter:

Jacek Jędrzejewski

Assignee:

Guilherme Blanco

Resolution:

Unresolved

Votes:

Labels:

None

Attachments:

DC9999TestCase.php

Description

It works except subqueries. Main table's order is added to subquery and vice versa. SQL query looks like this:

{{
SELECT t.id AS t_id FROM ticket_d_c9999_record t WHERE (t.id = (SELECT MAX(t2.id) AS t20 FROM ticket_d_c9999__record t2 ORDER BY t.id ASC, t2.id ASC)) ORDER BY t.id ASC, t2.id ASC
}}

Query fails because there is >>no such column "t.id"<<.

I include a testcase when it is all visible.

BTW. there is nothing about that feature (and relation orderBy) in docs. It is only in UPGRADE file.

Comments

Comment by Jacek Jędrzejewski [ 25/Aug/10 ]

Anyone?

Comment by Alan Betteridge [ 10/Mar/11 ]

Am having the same issue!

Eventually found the orderBy option on both the model and on relationships and was over joyed as I'd been trying to find a way of doing this, but it didn't work!!

Found the patch http://www.doctrine-project.org/jira/browse/DC-651 which solved the first problem I encountered but now I'm getting this.

Comment by Alan Betteridge [ 14/Mar/11 ]

Had a look at the code and tried only setting the orderBy if the current component is actually referenced in the from sql part of the query.

Looking at what is selected from, $this->_sqlParts['from'], it appears that the main table ($map['table']->getTableName()) and $sqlAlias is include with a zero index and joined tables are keyed by their $alias (or at least the content of these variables within the loop).

From this I believe I could detect if the current entry in the loop was from a table that was in the current "FROM" part of the query by looking for the table name and alias as an entry or the current alias as a key in the "FROM" array.

Within my sub query the orderBy valeus would still get applied but only in the subquery and not in the main query where they were included before, out of scope.

With patch DC-651 applied my code in Query.php at line 1315 noew looks as follows:

                // Note: Only include orderBy values for tables we're actually selecting from (both the root table or
                // tables referenced from it)
                if (in_array("{$map['table']->getTableName()} {$sqlAlias}", $this->_sqlParts['from']) || array_key_exists($alias, $this->_sqlParts['from'])) {
                    if (isset($map['relation'])) {
                        if (isset($map['ref'])) {
                            $orderBy = $map['relation']['refTable']->processOrderBy($sqlAlias, $map['relation']['orderBy'], true);
                            if ($map['relation']['orderBy'] && $orderBy == $map['relation']['orderBy']) {
                                $orderBy = $map['relation']->getOrderByStatement($sqlAlias, true);
                            }
                        } else {
                            $orderBy = $map['relation']->getOrderByStatement($sqlAlias, true);
                            if ($orderBy == $map['relation']['orderBy']) {
                                $orderBy = null;
                            }
                        }
                    } else {
                        $orderBy = $map['table']->getOrderByStatement($sqlAlias, true);
                    }
                } else {
                        $orderBy = null;
                }

Am I correct in my assumptions?

Generated at Wed Jun 19 20:24:21 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.

[DC-815] Model's default sorting breaks subqueries Created: 11/Aug/10 Updated: 14/Mar/11