Ordering many-to-many associations

[BenMorel]

As per the latest documentation, the only way to order a many-to-many association is according to a field in the target entity:

@OrderBy({"name" = "ASC"})

Sometimes however, one might want to have a many-to-many association with a custom order. For example, I'm creating a Newsletter that targets a number of Deals. My deals are ordered differently in every newsletter, as picked by the administrator authoring the newsletter.

So it would be nice if I could just add() my Deals to my many-to-many Collection, and next time I retrieve the Newsletter entity, it would just reload the Deals in the same order.

Under the hood, the many-to-many table could have an extra position field, that would transparently be used to order the collection.

As far as I know, there is currently no way to achieve this. I have to create an intermediate NewsletterDeal entity that contains the Newsletter, the Deal and the position, and use a one-to-many association ordered by position.

It's OK, but it's an extra burden that could be avoided.

Would you guys be OK on the principle to add this feature? In which case, I'd be happy to give it a try and submit a PR.

[Ocramius]

I think this already has a long-standing issue somewhere, and the feature is indeed really interesting, and a quite common use-case scenario.

Still, given the current state of 2.x, I'd suggest you to check how much the metadata model has changed in develop, and eventually start from there.

I think that a separate collection persister will be needed though.

[BenMorel]

Ah, true, found it in #2817 (DDC-213), dating back to... 2010. It seems to be more complicated to implement that I first thought, and had been moved to 3.x.

Has any planification been started for 3.x by now, or are you still focused on 2.x only?

[Ocramius]

develop is 3.x.

See https://github.com/doctrine/doctrine2/projects/1

[backbone87]

If work is put into this, all possible scenarios should be considered (all ToMany relations):

Legend:

• JT: Join table
• FT: Foreign table
• FK: The foreign key to the foreign entity
• OT: Own table
• OK: The foreign key to the own entity
• PK[$C]: Primary key over columns $C
• UK[$C]: Unique key over columns $C
• [$C1]->$T[$C2]: Foreign key over columns $C1 to columns $C2 of table $T

Common cases:

• 1-n Set; OK on FT; 4 cases
  • Order: Not ordered, Ordered
  • Index: Not indexed, Indexed
• 1-n Set; JT: OK, PK[FK]; 9 cases
  • Order: Not ordered, Ordered, Persisted order on JT
  • Index: Not indexed, Indexed, Persisted index on JT with UK[OK,index]
• n-n Set; JT: PK[OK,FK]; 9 cases
  • Order: Not ordered, Ordered, Persisted order on JT
  • Index: Not indexed, Indexed, Persisted index on JT with UK[OK,index]
• n-n Map; JT: PK[OK,index], FK (duplicate foreign entities possible); 3 cases
  • Order: Not ordered, Ordered, Persisted order on JT
  • Index: Persisted indexed on JT

There are also some corner cases:

• 1-n Map; OK on FT; JT: PK[OK,index], [OK,FK]->FT[OK,PK]; 3 cases
  • Order: Not ordered, Ordered, Persisted order on JT
• 1-n Map; JT1: OK, PK[FK]; JT2: PK[OK,index], [OK,FK]->JT1[OK,PK]; 3 cases
  • Order: Not ordered, Ordered, Persisted order on JT2
• 1-n Bag; OK on FT; JT: [OK,FK]->FT[OK,PK], "PK-less"; 3 cases
  • Order: Not ordered, Ordered, Persisted order on JT
• 1-n Bag; JT1: OK, PK[FK]; JT2: [OK,FK]->JT1[OK,PK], "PK-less"; 3 cases
  • Order: Not ordered, Ordered, Persisted order on JT2
• n-n Bag; JT: OK, FK, "PK-less"; 3 cases
  • Order: Not ordered, Ordered, Persisted order on JT

Notes:

• "1-n Map" and "1-n Bag" are very sketchy use cases
• "n-n Bag" can be solved as "n-n Map"
• "Persisted order"/"Persisted index" could write the order/index into a foreign entitie's table, but that would probably violate some core principle (thats why I also left it out for "1-n Set; OK on FT")
• "Persisted order" could use an integer from 0 to n, that is rewritten on save, or a tree encoded as binary string label to allow inserts between nodes and therefore writing only moves, inserts, deletes
• "Order"/"Persisted order" should use a general DQL ORDER BY expression. ofc when using "Persisted order", you should use the position column in your ORDER BY expression, but maybe preceded by other order keys (like a foreign entity's type column)
• "Persisted order" used together with "Persisted index" MUST NOT write to the same column. if ordering by index value is desired, just use "Order" by index (without persisting order)
• "Index"/"Persisted index" mapped ORM-values are always cast to string before being used as collection keys
• "Persisted index": Each index column's mapped ORM-value must be isomorphic to strings. (e.g. a string, an integer, a float; but a Value Object with 2 columns works too, as long as it provides a __toString and fromString with $v == fromString(__toString($v)) OR implements Serializeable with $v == unserialize(serialize($v)) )
• "Persisted index" must use index columns, that are part of a UK over all index columns and optionally (and commonly) including the OK
• on all n-n variants a bidirectional association can be used, where both side may be declared with "Persisted order" and/or "Persisted index". In such cases a "Persisted order" or "Persisted index" on the one side, must not write to columns that are written by the other side's "Persisted order" or "Persisted index"
• The UK for "Persisted index" on "1-n Set" and "n-n Set" may be used as PK (degrading the previous PK to UK). This could result in a better on-disc structure (depending on DBMS and its configuration)

[tseho]

Hey @Ocramius, I have been working on a prototype for implementing this feature.

For ManyToMany relations, the approach I took is :

• add a "position" column on the JoinTable
• guess the position of new entries by runing a pre-insert query like SELECT MAX(position) FROM ...
• update positions when an entry is removed in two steps:
  • pre-delete: SELECT position FROM ... for finding position of removed entry
  • post-delete: UPDATE ... SET position = position - 1 WHERE position > ?
• add an "ORDER BY" on the ManyToMany select queries

It is an approach you would be interested into for this feature in doctrine ?

If it is, I will cleanup, add some tests and open a PR.

[Ocramius]

Using MAX+1 is not viable unless you run in a serialized transaction.

…

On 29 Jan 2018 14:56, "Quentin Favrie" ***@***.***> wrote: Hey @Ocramius <https://github.com/ocramius>, I have been working on a prototype for implementing this feature. For ManyToMany relations, the approach I took is : - add a "position" column on the JoinTable - guess the position of new entries by runing a pre-insert query like SELECT MAX(position) FROM ... - update positions when an entry is removed in two steps: - pre-delete: SELECT position FROM ... for finding position of removed entry - post-delete: UPDATE ... SET position = position - 1 WHERE position > ? - add an "ORDER BY" on the ManyToMany select queries It is an approach you would be interested into for this feature in doctrine ? If it is, I will cleanup, add some tests and open a PR. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#6261 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJakIznmasf_ZDXmu8u7upzO054gZNeks5tPc4RgaJpZM4Lu6Sb> .

[tseho]

(I have some spare time to work on it again)

I was trying this way because it's how DoctrineExtensions does with Sortable.
Gedmo/Sortable/Mapping/Event/Adapter/ORM.php#L20

Could we use a PESSIMISTIC_WRITE lock for this case ?