Doctrine 1
  1. Doctrine 1
  2. DC-815

Model's default sorting breaks subqueries

    Details

    • Type: Bug Bug
    • Status: 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. DC9999TestCase.php
        1.0 kB
        Jacek Jędrzejewski

        Activity

        Hide
        Jacek Jędrzejewski added a comment -

        Anyone?

        Show
        Jacek Jędrzejewski added a comment - Anyone?
        Hide
        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
        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
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: