Problem/Motivation
EntityQuery offers ordering on any field / column. I believe making this a Total order is desirable but it is currently not, there are situations where a ≤ b and b ≤ a and yet a != b. This happens if entity1->field[0]->value is 1, entity1->field[1]->value is 4 , entity2->field[0]->value is 2 and entity2->field[1]->value is 3. We compare maximums and minimums and so we compare 1 < 2 and 4 > 3 .
Proposed resolution
When a field has multiple values, lexicographic sorting helps this situation. Instead of minimum and maximum, we compare elements at delta 0, if they are the same then at delta 1 and so forth. By making a consistent decision on where to put missing values (MySQL puts them first on ASC, last on DESC and DBTNG hardwires this for PostgreSQL too) this sort is good.
When there are multiple translations you must specify a langcode, plain and simple. If you don't the results are undefined. This requires throwing away the current tests. That's fine.
Remaining tasks
Once we we figure the desired ordering out, we still need to make it work on PostgreSQL. The challenge here is this: for count/pager we want on one result row per revision. However, JOINining field tables with delta > 1 or multiple translations will produce more than one row per revision. DISTINCT will not work because PostgreSQL requires us to put the fields ordering on into SELECT so then DISTINCT will do nothing useful. The resulting queries might look http://stackoverflow.com/questions/9796078/selecting-rows-ordered-by-som... very ugly.
Also, I thought revisions were primary key'd by their revision id, since when do we have multiple languages of the same revision? That perhaps makes it necessary(?) to rekey the results of entity query??
User interface changes
API changes
In certain cases the ordering result will change but the previous results were utterly broken.
Comments
Comment #2
chx CreditAttribution: chx commentedComment #3
chx CreditAttribution: chx commentedComment #6
chx CreditAttribution: chx commentedI tested this on MySQL and it passed. Let's see what the bot says about PostgreSQL and SQLite...
Comment #7
chx CreditAttribution: chx commentedSo the problem is -- although lexicographic sort is good when sorting the shapes in the test -- but the greetings are multiple language and you still have the same problem comparing: entity 15 has both tr and pl, do we want to sort it before or after the ones that only have tr or pl? I do not know.
Comment #8
chx CreditAttribution: chx commentedHere's the contents of the test field tables if the comment in the patch is not clear enough.
Comment #9
chx CreditAttribution: chx commentedComment #10
chx CreditAttribution: chx commentedComment #11
chx CreditAttribution: chx commentedComment #12
chx CreditAttribution: chx commentedComment #13
chx CreditAttribution: chx commentedComment #14
chx CreditAttribution: chx commentedSomeone needs to do this because I won't.
Comment #26
smustgrave CreditAttribution: smustgrave at Mobomo commentedSounds like this still needs discussion and discovery. Hopefully this shakes things loose.