Persist order of collections

Roman, I'd like to do this one as I have currently a use case for this. Do you have any idea of how to do this? What I'm wondering is whether it is possible to implement this without user intervention. (This would simply mean "store the entities as they were added"). But this would need another column in DB that we'd have to add within oneToMany / manyToMany relationships, but in this case one could save a serialized array holding "entityId => position" key / value pairs.

Afterwards, one could easily rebuild / reorder the collection via $collection->set($entity, $order[$entity->identifier]);

If you've got another thought about this, please don't hesitate to point me into the right direction!

this won't be implemented until 2.1, since its a pretty complex feature.

Changes are probably required in:

1. CollectionPersister - Add a new collection persister that takes the position into account
2. SchemaTool - Add a 'col_position' column to either the many-to-many or the one-to-many tables.
3. EntityPersister - Use and extend current order-by support to make the sorting happen

You can implement this already though with some performance hit in update scenarios. If you use the ORDER BY support and implement an API around your entity that abstracts those changes and always sets a "position" field on the many entity that is supposed to be sorted.

I don't think we necessarily need a new collection persister. Simply adjusting the ManyToManyPersister to be able to deal with it might be sufficient.

For OneToMany, that is always persisted from the "many" side, thus there is no collection persister, we would need to adjust the normal persisters.

They key element for the user should be a new annotation (or corresponding xml/yaml element) @OrderColumn. By default the order should not be persistent, only when an @OrderColumn annotation is present. The name of the order column can have a default, i.e. "position". Thus this enhancement of persisting the order should be fully backwards compatible.

On another note, the getInsertDiff/getDeleteDiff methods of PersistentCollection should already be "ready" for this. That is, when an element in the collection changed only its position, this is already tracked as a change. However the ManyToManyPersister issues no "UPDATE" queries, it simply deletes and inserts. A position change may be more effectively persisted with an UPDATE.

From a mailinglist entry, required check/changepoints:

1. ClassMetadata of Many-To-Many associations have to be extended to publish the required datastructure to the ORM.
2. All Metadata Mapping Drivers have to be extended
3. Persisters\ManyToManyCollectionPersister has to be extended to save the key in the many to many table if desired by the user.
4. Schema-Tool has to be extended to create the additional column.
5. PersistentCollection has to be extended so that lazy loading of collections with additional key works.
6. Array- and ObjectHydrator have to be extended to allow fetch join of collections with key column.
7. Discuss wheather to support this for One-To-Many also with the key-column on the many side. This is much more tricky internally though.

Push back to 2.x, we will have support for DDC-250 first and for this at a later release.

Hi there,
I'm looking for this feature.

Benjamin Eberlei said that : "You can implement this already", but I don't understand the "how to".

Also,
The problem should be solve if RDBMS had a "natural" order. An order based on item position inside table.

To get this feature without any change on Doctrine, I have remplace the PK defined by the target & mapped field identifier. The new PK is a new field with type "integer" and with auto-increment enable.
In this configuration, Doctrine use the "natural" order of the RDBMS. And I can change order of my item inside Collection and persist it.

It's an very bad solution, but It work before an official support.

Waiting for advices, and solutions,

Thomas.

Answering to Benjamin Eberlei on the "7. Discuss wheather to support this for One-To-Many also with the key-column on the many side. This is much more tricky internally though.".

I think that for One-To-Many relations, if user want to store the collection order, Doctrine can store the One-To-Many as Many-To-Many with a "model" limitation.
In that case, if storing order collection for Many-To-Many work, it should work for One-To-Many.

What do you think about it ?

I think that it must be possible to have two keys ordering : the order isn't obligatory reversible.

For exemple with user and group :

• You can order groups for one user : with preference by exemple, or importance.
• And with a different order, users for a group : rank by example.

And maybe more, if you decide to add multi-order : an user show group by his rank in it, if his rank is identical, the order is make by love preference, and after by the importance given by the user (not necessary a number, if we imagine filter on them).

So a default order can be choice with parametized fields and could be :

 
@ManyToMany(targetEntity="Group")
...
@JoinFields ( rank: { type: int} , preference:{type:int}, importance:{type: string, length: 40} )
@OrderByJoinFields({"rank" = "ASC", "preference"="ASC", "importance"="ASC" } )

In this case the order must be optional and would be clean if another order appears in the same scope (DQL...). And manytomany became virtual entities act as other entities except they don't appears permetting in the same time a better conception.

So if the solution take in DDC-181 will become the only solution. This would a good idea to document this. Because, this seems to me a very important point.

My last point is even an unique ordering field created in the join table will be a big and usefull improvement.

Thank a lot for your beautiful work.

In my point of view, a collection can be order in a single way only.
If you want to add more than one order between User & Group, it's a new collection, a new relation.
Like :

• User.memberOf() : Group[]
• Group.members() : User[]
• Group.importantMembers() : User[]

And it's your role to keep a consistency between members & importantMembers array.

Because ManyToMany join table is the reflection of a state of an ArrayCollection. It's not a usefull feature to be able to store all of the state of an ArrayCollection, even the order of this Array. It's just a normal feature that is really missing

Thomas.

I don't think:

If you have three collection, you duplicate one relation 3 times and it's easy in consequence to lost the data integrity and unicity.

By example :

• Thomas have rank 10 in Admin
• Thomas think the admin group has importance noted 3 on all of his groups.
• If a responsable of admin group decide to delete Thomas from it.
• Thomas, in his ordered list of groups, think always to be in group admin.

So in my idea, the many to many relation isn't just an array collection, but should be an virtual entity. In UML or in Merise method this is a common problem to have a parametized relation. I think an orm should just implement this.

Hum,
I agree with you.. In a SQL Schema, it's a good choice to add many fields in a ManyToMany join table to description "order".

I just want to add a piece of Doctrine ORM Document :

"When working with collections, keep in mind that a Collection is essentially an ordered map (just like a PHP array). That is why the remove operation accepts an index/key. removeElement is a separate method that has O ( n) complexity using array_search, where n is the size of the map."

Hi there,
After several discussions. on IRC, I have changed my point of view.

Doctrine Documentation says : "When working with collections, keep in mind that a Collection is essentially an ordered map (just like a PHP array)".
So, I think that Doctrine have to be able to store or not the order of a Collection. By adding a new field on the Joined table to store the position of each elements.

But I not agree with @Nicolas. Because in his case, he's talking about Association Class : http://etutorials.org/Programming/UML/Chapter+6.+Class+Diagrams+Advanced+Concepts/Association+Class/
Because he's talking of a business logic, he's talking of a dedicated Entity class.

What do you think about it ?

Thomas;

Any news ?

Hi, any news on this?

If I may add any info to this feature request, maybe something like JPA/Hibernate could be a good start?

See http://docs.oracle.com/javaee/6/api/javax/persistence/OrderColumn.html or http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/collections.html#collections-indexed

The idea in Hibernate is that you persist the order of the list in an extra column. This column is not a field of the entity however.