Doctrine 1

Model's default sorting breaks subqueries

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Critical Critical
  • Resolution: Unresolved
  • Affects Version/s: 1.2.2
  • Fix Version/s: None
  • Component/s: Query, Record
  • Labels:
    None

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.

  1. File
    DC9999TestCase.php
    11/Aug/10 7:31 AM
    1.0 kB
    Jacek Jędrzejewski

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Jacek Jędrzejewski added a comment -

Anyone?

Show
Jacek Jędrzejewski added a comment - Anyone?
Hide
Permalink
Alan Betteridge added a comment -

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.

Show
Alan Betteridge added a comment - 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.
Hide
Permalink
Alan Betteridge added a comment -

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?

Show
Alan Betteridge added a comment - 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?

People

  • Assignee:
    Guilherme Blanco
    Reporter:
    Jacek Jędrzejewski
Vote (1)
Watch (1)

Dates

  • Created:
    Updated: