[Custom Sort Order] at tagSwap.net: Open Source Lasso Code

[Custom Sort Order]

Bil Corry, September 26, 2005

Problem:

Your users want to be able to specify the order that their records (rows) should appear in.

Solution:

The solution consists of four changes to your web application:

Database Backend
Inline Sort Order
Detail Page
Response Page

Database Backend

First, you need to add a column to the table that holds the rows that need to be in custom order (or you can create an entire table for it, but we'll stick with simple). Make it of type unsigned tinyint if 255 items or less, or unsigned smallint if 65k items or less. The default value is 0 (zero). It doesn't matter if the table already has existing rows or not (so this method works with legacy systems).

Now you have a place to store your custom order.

Inline Sort Order

Next, update your inlines to use the custom order column for sorting - it should sort ascending. You can also have other sort items, but the first sort item should be your custom order column.

Great, now you have the backend done!

Interface Overview

Now all you have to do is build the user interface to change the order. This part is actually easier than you might think. Note that my method means you only have to change the order value of the item you want to move, you DO NOT have to change every order value for every row to avoid duplicates. So if you want item #1 to be item #4, make the order value of item #1 be 4 and submit. It won't matter that there are two order #4s coming through. Oh, and you won't have to pass any data in sessions or hidden fields, or other sillyness.

So the UI consists of two pages, the detail page (where you change the sort order) and the response page (where the changes are committed to the table).

Detail Page

First, the detail page. For each item that is orderable (that is, for every item that we want to adjust the order in which it appears), we will present the user with a pop-up list, with the first pop-up list pre-selected to option #1, the second is #2, etc. We build it like this:

inline: <find your rows, sorted in order by your order column>;
 	

 
 
 
 var:'items' = array;
 	

 
 
 
 var:'order' = 0;
 	

 
 
 
 rows;
 	
	

 
 
 
 
 
 
 
 $order += 1;
 	
	

 
 
 
 
 
 
 
 $items->(insert: (column:'id') =  	
	

 
 
 
 
 
 
 
 
 
 
 
 (map: 	
	
	

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 'name' = column:'name', 	
	
	

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 'order' = $order 	
	
 
 
 
 
 
 
 
 
 
 
 
 
 ));
 	

 
 
 
 /rows;
 
/inline;

What the above does is build an array of maps of your rows, with the order number calculated on the fly. We do this in case the order column contains all zeros (remember the legacy system?) or if the order column contains invalid values, etc.

Next, we present this to the user, pay close attention, here comes the good stuff:

<h1>Items to sort</h1>
<form action="response.lasso" method="post">
<table>
    <tr>
        <th>ORDER</th>
        <th>ITEM</th>
    </tr>
[iterate: $items, local:'item']
    <tr>
        <td><select name="order">
            [loop: $order]
            [local:'loop_count' = loop_count]
            // put this on one line
            <option value="[#loop_count->(setformat: -padchar='0', -padding=3) &
]
            [(#item->value->(find:'order') == loop_count)
                ? '1'
                | (#item->value->(find:'order') < loop_count) ? '2' | '0']_[#item->name]"
            [(#item->value->(find:'order') == loop_count) ? ' SELECTED']>[loop_count]
            [/loop]
            </select>
        </td>
        <td>[#item->value->(find:'name')]</td>
    </tr>
[/iterate]
</table>
<input type="submit" name="submit" value="Update">
</form>

What we're building here is list of rows, each with it's own pop-up list. I'm assuming there are less than 1000 items for this example (the -padding=3). Here's what the above creates with four rows:

<tr>
    <td><select name="order">
        <option value="0011_AAAAA" SELECTED>1
        <option value="0022_AAAAA">2
        <option value="0032_AAAAA">3
        <option value="0042_AAAAA">4
    </select>
    </td>
    <td>Grapes</td>
</tr>
<tr>
    <td><select name="order">
        <option value="0010_BBBBB">1
        <option value="0021_BBBBB" SELECTED>2
        <option value="0032_BBBBB">3
        <option value="0042_BBBBB">4
    </select>
    </td>
    <td>Apples</td>
</tr>
<tr>
    <td><select name="order">
        <option value="0010_CCCCC">1
        <option value="0020_CCCCC">2
        <option value="0031_CCCCC" SELECTED>3
        <option value="0042_CCCCC">4
    </select>
    </td>
    <td>Pears</td>
</tr>
<tr>
    <td><select name="order">
        <option value="0010_DDDDD">1
        <option value="0020_DDDDD">2
        <option value="0030_DDDDD">3
        <option value="0041_DDDDD" SELECTED>4
    </select>
    </td>
    <td>Oranges</td>
</tr>

So let's break out the option values. The first three digits are the sort order. So 001 is 1, 002 is 2, etc. The fourth digit designates it's place to the current value - so if the current item is order #3, then order #1 and #2 are less, so we put a zero. Order #3 is the same, so it gets a one, and order #4 is greater so it gets a two. If you look, you'll see this is how the numbers were generated. This will be important on the response page.

Next you'll see an underscore, it's a delimited, then the row ID. I've used a five-char id (AAAAA,BBBBB, etc), but it can be anything except an underscore (numbers, letters, etc). I think most will probably have a sequential ID number here.

Ok, that's it for this page. On to the response page.

Response Page

The response page is the easiest. Here's the code you'll need to figure out the new custom sort order:

// get new order
var:'orders' = (action_param:'order')->(split:'\r');

var:'update' = array;

iterate: $orders, local:'order';

    if: #order->(split:'_')->size == 2;

        $update->(insert: #order->(split:'_')->(get:1) = #order->(split:'_')->(get:2) );

    /if;

/iterate;

$update->sort;

// update sort order
iterate: $update, local:'u';

    var:'sql' = "
        UPDATE mytable
        SET
            order = '" loop_count "'
        WHERE
            id = '" (encode_sql: #u->value) "'
    ";

    inline: -sql=$sql;

    /inline;

/iterate;

"That's it?" you ask? Yep, not much to do here. Pull the order values out, sort them and update each row with it's new sort order. You'll notice I don't use what was passed by the user for the sort order value, I just use loop_count. Loop_count is safer as we don't have to worry about someone passing crazy values.

How the heck does it work?

Now I bet you're wondering, "how does it work?"

It's easier to explain using an example (I'll use the sample html output from above for this example). Say we want Apples to be sorted last. So we change that row to be #4 (we don't change any other value). That means we'll receive the following POST data for the form field "order":

0011_AAAAA 
0042_BBBBB (this is apples, we want it last) 
0031_CCCCC 
0041_DDDDD

If you sort the above, you'll notice it'll come out as:

0011_AAAAA 
0031_CCCCC 
0041_DDDDD 
0042_BBBBB (this is apples, we want it last)

Wow! Apples are last!

It works going the other way, making items sooner in the list. Here's an example of that. Say we want Oranges (the last item) to be the first item. So our POST data will be:

0011_AAAAA 
0021_BBBBB 
0031_CCCCC 
0010_DDDDD (this is oranges we want it first)

Sort it:

0010_DDDDD (this is oranges we want it first) 
0011_AAAAA 
0021_BBBBB 
0031_CCCCC

Wow again! It's the first item!

Ok, well, I think it's pretty cool. Hopefully it'll give you a workable idea to use.

Bil Corry provides Lasso development and consulting services.

[Custom Sort Order]

Articles