jQueryUI Sort Example

• Download JQuerySort.zip - 301.7 KB

Introduction

jQuery UI makes it really easy to implement sortable elements on a web page, and getting the results of the sorted data is easy, but figuring out what element was repositioned, and where, takes a bit of work. It would also be nice if there was a way to perform a single database UPDATE to record the new element order instead of iterating through a family of records and updating each one individually.

This article (and attached code) demonstrates how to create sortable elements, react to an event when they are sorted, send the data to your server via AJAX, and process the data using an algorithm to figure out which element was moved... and where.

It also shows you a way to configure the table that stores the element data and calculate the index of the element so the sort order can be updated with a single query.

Using the code

To run the code, you'll need a server with PHP5.4 or a properly configured IDE with a built in PHP server (a NetBeans project file is included in the source, but you can import the code into another IDE). It is not production ready... think of it as a template for your own project. The Profile.php and SortableElement.php classes will be replaced by your own application classes.

The attached code consists of an initialization module (index.php) that simply creates the data for the demo and puts it in the session. The file sortExample.html is the main page and is styled by style.css.

The important files are sortable.js and SortController.php. These handle communication between the browser and server. The controller class contains the logic for processing the serialized data provided by jQuery when the components are sorted.

First things first. This javascript function initializes the container (in this case, a div) that holds your sortable elements. It will be called when the document is loaded.

function initSortableContainer()
   {
   $(function()
      {
      $("#SortableElements").sortable(
         {
         cursor: "move",
         stop: handleReorderElements
         });
      });
   }

The function handleReorderElements() makes an AJAX call to the server. The initSortableContainer() function (above) binds the 'stop' event to the div that contains the sortable elements, specifying that this function is called when a sort operation has completed. Use the jQuery function sortable( "serialize") to get a parameter/value string of all the sortable element Id's in their new order. See the section below for an explanation of how to create the Id's of the sortable elements so this works correctly.

function handleReorderElements()
   {
   var url = 'controllers/SortController.php';
   var fieldData = $( "#SortableElements" ).sortable( "serialize" );
   fieldData += "&action=reorderElements";

   var posting = $.post( url, fieldData);
   posting.done( function( data)
      {
      reorderElementsResponse( data );
      });
   }

This function is the AJAX callback handler. In most applications, you won't need to do anything here except handle an error. For the demo, the controller returns the results of the sort processing. If there was no error, the function will display this on the page.

function reorderElementsResponse( data )
   {
   if (data.FAIL === undefined) // Everything's cool!
      {
      $("#Message").html( data.resultString + data.itemIndexString );
      }
   else
      {
      alert( "Bad Clams!" );
      }
   }

IMPORTANT: Formatting HTML Element ID's

The Id's of the sortable elements must be created using a common name, a dash or underscore, and an Id number. If you look at the file index.php, you'll see that each sortable element is constructed with an Id of id='SortableElement_" . $element->getId()<code> to produce numbered Id tags like SortableElement_1, SortableElement_2, SortableElement_3, etc. The string passed to the server will look like this: SortableElement[]=1&SortableElement[]=2&SortableElement[]=3, etc. and will actually be interpreted by PHP as an array.

In SortController.php, we can get this array using $sortIdArray = $_POST["SortableElement"];
Pretty easy!

Processing The Data

SortController.php receives the AJAX request from the page. It retrieves the name/value pair and stores it in the variable $sortIdArray. It then pulls the original array from the session and extracts the keys (which are the element Id's) into a separate array:

$sortList = $profile->getSortableElementList();
$originalIdList = array_keys( $sortList );

Next, a function is called that compares the arrays to determine which element was moved. The function getArrayDiff( $originalKeys, $newKeys) is passed an array of the original Id's and an array of the modified Id's. It compares each original Id and modified Id until it finds a pair that don't match. One of these Id's will be the one that was moved. To figure out which one, the function removes the original Id from both arrays and compares the two arrays again. Interestingly, when the moved Id is removed, the two arrays will become identical. Since there is a 50% chance that the first test will be correct, the removal/comparison only has to be performed once. If the arrays aren't identical on the first comparison, then the other Id is the one that was moved. So... when the function ends, it returns the moved Id (or -1 if no Id's were moved). Trace through the function to see how it works, and note the caveat below if two adjacent elements are exchanged...

Once the moved Id has been determined, we need to find out where it was moved. After initializing a couple of variables to handle moving to the beginning or end of the array, the code iterates through the modified array to find the moved Id, stores the previous and next Id values and...

Table Structure

Time to back up a bit and discuss the table structure that enables a single update to store the modified Id order. The secret is to define your index field (in this case, ItemIndex) as a DOUBLE. Instead of being forced to iterate through all the records for a family of objects and update the indexes to reflect the new order, we can simply change the ItemIndex to a (possibly) fractional value between the previous and next ItemIndex values. For instance, if we move a record with an ItemIndex of 8 between records with ItemIndexes of 3 and 4, we just update the index of the moved item to 3.5. You may think that you'll eventually lose the ability to keep sub-dividing the indexes; you'll run out of precision, and you'd be correct. In reality, though, you can sub-divide a DOUBLE over 1,000 times before running out of precision. In real-world use, this will probably never happen, but see the note below about handling the possibility.

You'll need to make sure that, when INSERTING new records into the table, you set the ItemIndex field to a higher value than the current maximum ItemIndex (and greater than 0). Something similar to the following:

public function insert( SortableElement $sortableElement )
   {
   $sql = "SELECT MAX(ItemIndex) as Result
           FROM sortable_elements
           WHERE OrganizationId = " . $sortableElement->getOrganizationId();

   $itemIndex = $this->m_FSPDO->executeFunction( $sql );
   if ($itemIndex === null)
      {
      $itemIndex = 10;
      }

   $itemIndex += 10;

   $sql = "INSERT INTO sortable_elements ( OrganizationId, Status, Title, HolderId, HolderAccountType, ItemIndex)
           VALUES ( :organizationId, :status, :title, :holderId, :holderAccountType, :itemIndex)";

   $sortableElement->setId( null );
   $parameters = $sortableElement->getAllParameters( true );
   $parameters[':itemIndex'] = $itemIndex;

   $sortableElement->setId( $this->m_FSPDO->executeInsert( $sql, $parameters ) );
   }

*This uses a two step approach, but depending on your DB and preference, you could use a Stored Procedure.

With a table configured this way, all that's left is to calculate a value between the previous and next ItemIndexes while handling the cases of moving an element to the beginning of the list (hence the need to start the ItemIndex field at a value greater than zero) or to the end of the list:

if ($previousKey === 0)
   {
   $itemIndex = (double)((double)(reset( $sortList )->getItemIndex() / 2));
   }
else if ($nextKey === INF)
   {
   $itemIndex = (double)((double)(end( $sortList )->getItemIndex() + 10));
   }
else
   {
   $lowIndex = (double)$sortList[(int)$previousKey]->getItemIndex();
   $highIndex = (double)$sortList[(int)$nextKey]->getItemIndex();
   $itemIndex = (double)($lowIndex + ($highIndex - $lowIndex) / 2);
   }

Points of Interest

Loss of Precision

Although it isn't likely that you'll run out of precision to recalculate an intermediate value for a new index, it is possible. There are two primary ways to handle the prevent the condition from becoming an error. The first way would be to explicitly check the calculated value to see if it's equal to either of the surrounding values. If it is, you've run out of precision and will need to call a function (preferably a fast, asynchronous function) to re-index the records in the related group. Secondly, and preferable if you believe the condition is possible in your application, you could create a chron task that would occasionally re-index all tables that use this ordering method. The exact implementation is beyond the scope of this article... for now :)

The "MovedKey" Variable Used In This Demo May Be Inaccurate

As part of the demo, the Id that was moved is returned, but you may notice that there is a particular case when it is incorrect. Transposing two adjacent panels will report the correct moved Id only when moving a lower item to a higher position. This is because the arrays are identical when adjacent items are switched.

For example, if the original Id's were 1, 2, 3, 4, 5, and Id '3' was moved between '1' and '2', it would result in:

1, 3, 2, 4, 5.

If Id '2' was moved between '3' and '4', the array would become:

1, 3, 2, 4, 5

Hmmm... same array...

In this case, when the getArrayDiff function compares the arrays, it will always succeed on the first try because there is no way to tell which Id was moved when they are adjacent; the resulting arrays are identical. Tracing through the code is the best way to understand what's happening.

Luckily, this doesn't affect the sorting results at all. Transposing adjacent items either way results in a correct sort order. This is only a problem if, for some reason, you need to know specifically which item was moved. There shouldn't be many instances when you need to know (jQuery handles the UI change), but if you do, it will require significant additional code on the client side.

History

  Version 1.0