Doctrine 2 - ORM

Patch for "orderBy" attribute for @ManyToMany

Details

  • Type: Sub-task Sub-task
  • Status: Resolved Resolved
  • Priority: Minor Minor
  • Resolution: Invalid
  • Affects Version/s: 2.0-ALPHA4
  • Fix Version/s: None
  • Component/s: ORM
  • Security Level: All
  • Labels:
    None

Description

There are lots of scenarios where a many to many collection has semantics in regard to the order of the entities. Here is a prove of concept patch that implementing an attribute "orderBy" for the ManyToMany annotation. The same can be implemented for OneToMany using the same pattern easily. Here are some excerpts from the test-case:

class RoutingRoute
{
    /**
     * @Id
     * @generatedValue(strategy="AUTO")
     * @column(type="integer")
     */
    public $id;

    /**
     * @ManyToMany(targetEntity="Doctrine\Tests\Models\Routing\RoutingLeg", cascade={"all"}, orderBy="departureDate ASC")
     * @JoinTable(name="RoutingRouteLegs",
     *     joinColumns={@JoinColumn(name="route_id", referencedColumnName="id")},
     *     inverseJoinColumns={@JoinColumn(name="leg_id", referencedColumnName="id", unique=true)}
     * )
     */
    public $legs;

    public function testOrderedCollection()
    {
        $route = new RoutingRoute();

        $leg1 = new RoutingLeg();
        $leg1->fromLocation = $this->locations['Berlin'];
        $leg1->toLocation   = $this->locations['Bonn'];
        $leg1->departureDate = new \DateTime("now");
        $leg1->arrivalDate = new \DateTime("now +5 hours");

        $leg2 = new RoutingLeg();
        $leg2->fromLocation = $this->locations['Bonn'];
        $leg2->toLocation   = $this->locations['Brasilia'];
        $leg2->departureDate = new \DateTime("now +6 hours");
        $leg2->arrivalDate = new \DateTime("now +24 hours");

        $route->legs[] = $leg2;
        $route->legs[] = $leg1;

        $this->_em->persist($route);
        $this->_em->flush();
        $routeId = $route->id;
        $this->_em->clear();

        $route = $this->_em->find('Doctrine\Tests\Models\Routing\RoutingRoute', $routeId);

        $this->assertEquals(2, count($route->legs));
        $this->assertEquals("Berlin", $route->legs[0]->fromLocation->getName());
        $this->assertEquals("Bonn", $route->legs[1]->fromLocation->getName());
    }

Issue Links

is referenced by

Improvement - An improvement or enhancement to an existing feature or task. DDC-195 Ordering of associations

  • Major - Major loss of function.
  • Resolved - A resolution has been taken, and it is awaiting verification by reporter. From here issues are either reopened, or are closed.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Benjamin Eberlei added a comment -

Discussion in IRC had the following results:

  • Snippet should be SQL to allow for more flexibility
  • Snippet should always be appended to any existing Order By statements, thereby only affecting the emitation of the collection elemets and not any previously requested order
  • If the DQL revereses the order previously this should be overwritten, very simple:
1. @orderBy("b.start_year ASC, b.end_year DESC")
2. SELECT a, b FROM a JOIN b ORDER BY b.startYear DESC
1+2 => ORDER BY b.start_year DESC, b.start_year ASC, b.end_year DESC
Show
Benjamin Eberlei added a comment - Discussion in IRC had the following results: Snippet should be SQL to allow for more flexibility Snippet should always be appended to any existing Order By statements, thereby only affecting the emitation of the collection elemets and not any previously requested order If the DQL revereses the order previously this should be overwritten, very simple: 1. @orderBy( "b.start_year ASC, b.end_year DESC" ) 2. SELECT a, b FROM a JOIN b ORDER BY b.startYear DESC 1+2 => ORDER BY b.start_year DESC, b.start_year ASC, b.end_year DESC
Hide
Permalink
Benjamin Eberlei added a comment -

Implementation Details:

  • The Table alias is set by replacing a magic %alias% string. This is ugly but the most simple solution. The next viable solution would mean implementing a parser for SQL, which is too complex.
  • In SqlWalker::walkJoinVariableDeclaration() - If an ordered collection valued association is joined that is also present in $_selectedClasses (i.e. fetch joined) set a flag in a new array $_orderCollections for this dql alias => sql snippet (Replace %alias% with the table alias of the fetch joined colleciton)
  • In SqlWalker::walkOrderByClause - After the user defined ORDER BY Items, loop over the order collections and append the appropriate sql snippets to the already generated ORDER BY stuff

Question now:

How do i add an AST\OrderBy node if none is requested in the DQL?

Or should we just always add an OrderBy node to the AST?

Show
Benjamin Eberlei added a comment - Implementation Details: The Table alias is set by replacing a magic %alias% string. This is ugly but the most simple solution. The next viable solution would mean implementing a parser for SQL, which is too complex. In SqlWalker::walkJoinVariableDeclaration() - If an ordered collection valued association is joined that is also present in $_selectedClasses (i.e. fetch joined) set a flag in a new array $_orderCollections for this dql alias => sql snippet (Replace %alias% with the table alias of the fetch joined colleciton) In SqlWalker::walkOrderByClause - After the user defined ORDER BY Items, loop over the order collections and append the appropriate sql snippets to the already generated ORDER BY stuff Question now: How do i add an AST\OrderBy node if none is requested in the DQL? Or should we just always add an OrderBy node to the AST?
Hide
Permalink
Benjamin Eberlei added a comment -

I'll close this one and attach some more prominent parts of this feature as sub-tasks to the parent issue.

Show
Benjamin Eberlei added a comment - I'll close this one and attach some more prominent parts of this feature as sub-tasks to the parent issue.

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Benjamin Eberlei
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: