Issue with column sort when using Master/Detail List Page

[dfrost]

I have a large amount of data and I am using Master/Detail setup where there are pages of Detail Records for a single master record. When the detail records are listed and I include a column "Sort" on a LOCATION field I get very strange behavior when the Detail records span multiple pages. In some cases, when you progress to the next page, records may be skipped or repeated.

Without a deterministic order by clause, the database by definition does not deliver a deterministic row sequence. Paging requires a deterministic sort order. Therefore you need to add a secondary deterministic sort (i.e. by ID) when you need paging to work correctly.

This behavior (due to database optimizations) has changed in MySQL over the years. Since 5.5 (or 5.6) the order of items without a deterministic field included in the ORDER BY clause will not give you correct order per page (LIMIT). The easy fix is just to include a secondary ORDER.

Instead of ORDER BY CATEGORY LIMIT X, Y you would include a unique filed as in ORDER BY CATEGORY.ID LIMIT X, Y

So, I will be looking around to see if there is a way to modify the ORDER BY part of the sql clause without having to change the base code, etc.

NOTE: This issue occurs with MySql when LIMIT is used to do the paging. This has nothing to do with Master/Detail. You will see in any listing when the user select to sort on one of the columns. You may not see this issue with older versions of MySQL or other DBMS's but I am pretty sure you will see it in 5.6 and later versions of MySQL and PostgreSQL.

[dfrost]

SOLVED BY SERVER EVENT:

NOTE: Use server event code so it lives withing your project. No need to manually edit PHP files.

// Recordset Selecting event
function Recordset_Selecting(&$filter) {
// Enter your code here

// Append deterministic ORDER to fix PAGING when user does column SORT.
$order_by_clause = $this->getSessionOrderBy();
if (!empty($order_by_clause)) {
if (!strpos($order_by_clause, ', ID ASC')) {
$order_by_clause .= ', ID ASC';
ew_AddMessage($_SESSION[EW_SESSION_MESSAGE], "MAKE ORDER DETERMINISTIC:" . $order_by_clause);
$this->setSessionOrderBy($order_by_clause);
}
}
}