Occurrence table should have a default sort order

[zygoballus]

Symbiota Portal Name

Ecdysis

Is your feature/tool request related to a problem? Please describe.

Since no sort order is specified by default when using occurrencetabledisplay.php, the order in which the results are displayed is formally unpredictable. In practice, the database will return them in the order they are stored in the database, but this can get jumbled, especially due to imports. Example screenshot:

If your occurrence table gets jumbled, the default table display can becomes mostly useless and you have to manually specify a sort order every time. To add insult to injury, there is no option in the interface to sort by Symbiota ID (occid), so it may not even be possible to get a table sorted by chronological insertion order (sorting by Date Entered doesn't work for imported records).

Describe the solution you'd like.

By default, occurrencetabledisplay.php should sort by Symbiota ID (occid). Since this field is the primary key it shouldn't cause any performance issues. This will give a reliable and predictable table order by default.

This could probably be fixed by editing OccurrenceEditorManager->setSqlOrderBy().

[themerekat]

@zygoballus , is there a particular reason why you'd want to sort based on occid, rather than something like catalognumber or date last modified?

[zygoballus]

@themerekat - Yes, I think in most cases the most useful (and expected) sort order is the order in which the records were added to the collection, i.e. chronological order. Catalog Number is actually a string, so it doesn't give the sorting you would expect, e.g. CMNH-IZ-A1, CMNH-IZ-A10, CMNH-IZ-A100, CMNH-IZ-A1000, CMNH-IZ-A1001. Date last modified is sometimes useful, but I don't think it's a good default. Plus sorting on anything that isn't a key in the database is going to be potentially slow for large collections. Sorting by occid should also be the least disruptive, as it will probably not cause any change for collections that have been managed in a single portal from the start. It should just eliminate unexpected anomalies in the current default sorting.

[themerekat]

Roger. Performance is definitely my highest concern here.

[zygoballus]

Sorry for the bad initial pull request. I think this one should solve it correctly.

I've noticed this problem is more widespread than I thought, but I'm not really sure what causes it exactly (other than performing imports). Here's another example (from midatlanticherbaria.org) showing a jumbled default sort:

[zygoballus]

Interestingly, this problem no longer occurs on Ecdysis. I wonder if the database there was reloaded or reindexed or something. The problem still occurs on midatlanticherbaria.org, though. The submitted patchset should keep it from happening regardless of the state of the database.

[themerekat]

The Mid-Atlantic Herbaria Consortium hasn't been updated to Symbiota 3.1, so it makes sense that you are seeing a discrepancy there.

[zygoballus]

@themerekat - Well, the weird thing is that the patchset to fix this hasn't been merged yet, so I wasn't expected it to be fixed. However, reloading the database (e.g. from a backup) or defragmenting it would also fix it.

[themerekat]

@zygoballus , right, I think the schema changes between 3.0 and 3.1 might be responsible for the change.