Reorder

[JohnBlack]

Hy,

Starting from this example:
http://www.smartclient.com/index.jsp#gridsDragReorder

I'm trying to make reorder permanent in my database.

This is my logic:
First, in my table I have an sortOrder field.
Now, when the user reorders the records I need to:
Get the record that it is moved (this is easy) and after that record is reordered I need to know which is the record that's before (a reference record).
After that I send both records ids via RPC to my server-side script.
On the server-side I modify sortOrder field for the reordered record using the sortOrder reference record. (In fact there'll be more than one sortOrder changed)
Back to client-side: I need to refetch the data with the same params (startRow,endRow etc.)

So what I need to know is:
1. which event is triggered when the reordered record is droped back in list grid - meanwhile I found it: ListGrid.recordDrop()

2. How can I find which is the record before the reordered record.

3. How I can refetch the data with the same params ( startRow, endRow, etc) ? - to be clear: I've noticed that the records does not really reorder (yeap it is written in the docs: "Note that reordering records has no effect on a databound grid.") - but still I'm not satisfied with that :))


To make it simple I'll use the example from gridsDragReorder:
When list grid is loaded the order is : US, China, Japan, India .
Let's say I want to move Japan after US:
2. How can I find what that the record that's before Japan (after reordering) is US ?


Thanks in advance,
John

[Isomorphic]

Use the findIndex property of ListGrid.data to find the index of the record before japan:

var japanIndex = grid.data.findIndex("countryName", "Japan");
var previousIndex = japanIndex - 1;

[JohnBlack]

Thanks for the quick answer, but sadly it's not that easy :(

Here is my code for recordDrop:
I have a field named "id" in the grid.

    recordDrop:function () {
       var rec=this.getSelectedRecord();
       var myIndex = this.data.findIndex("id", rec.id);
       var previousIndex = myIndex - 1;
       var refRec=this.data.get(previousIndex);
       isc.say(refRec.id+' '+rec.id);
    },

The problem is this: previousIndex is not returning the previousIndex after reordering but the one before.
So for the example with the countryList:
If I move Japan after US and run my code from above it will return me "China" for previosIndex instead of US.

I think that this "Note that reordering records has no effect on a databound grid." it's not gonna be so easy to walkaround it. :(
So maybe recordDrop is not the solution.

Ok, but what can I do to pass this problem?
Can you help me with any sugestions ?

Thanks again,
John

[Isomorphic]

From the recordDrop docs, the index parameter that is passed into recordDrop should give you the correct index:

isc.ListGrid.create({
    ID: "countryList",
    ....
    recordDrop : function (dropRecords, targetRecord, index, sourceWidget) {
        this.Super('recordDrop', arguments);
        // should make sure index != 0
        var prevIndex = index - 1;
        var refRec = this.data.get(prevIndex);
    }
})

[JohnBlack]

Woow,

This is kind of magic! Haha!

Thanks a lot!

John

[msatkevich]

I often feel the same way about this software...it's like magic. 5 lines of code, and presto!

I have a back-end database question about record sorting...

Is the "sort field" simply a pointer to the record id that comes before it? Do you use the auto-increment record identifier for this?

If so, does this still work after a whole bunch of records have been deleted and inserted?

I have never been able to allow users to sort, simply because I did not have the front-end processing power that I have now with SmartClient.

Mike

[Isomorphic]

Hi Mike,

Where are you seeing "sort field" in the docs? In most cases in means the field.name of the ListGrid field where the user has clicked to request sorting.

In general in SmartClient, client-side sorting will be performed whenever there is a full cache - see the ResultSet docs for more detail.

[msatkevich]

The question I had was more about how to handle the sorting in the back end database.

If I had a list of random items, and I wanted to see them on the screen in a certain order, normally I'd add an integer column, and number the items in the order I want them.

(Then use a SQL ORDER BY clause in the query to put the records in that order).

That would work for a static list. But if my users can re-order the items themselves using drag and drop, and also add and delete items, how is that normally handled in the database?

For instance, if they moved the 2nd item in the list to the 7th position, would I have to renumber all the items, and update the entire table?

My guess is no, but at the moment, it's not clear to me how people usually accomplish this feat. The new order of the items has to make it back into the database for next time...I just don't know how yet.

From what I've read in this thread, the secret seems to be something like a pointer system, but how does that work with SQL ORDER BY ?

I may be missing some computer science theory here. Sorry if this is remedial stuff. If so, perhaps just point me to a link on the subject.

Thanks,
Mike

[Isomorphic]

you could use floating point numbers. That way there would be an infinite number of values between any given number.

[JohnBlack]

Hy Mike,

The question I had was more about how to handle the sorting in the back end database.

If I had a list of random items, and I wanted to see them on the screen in a certain order, normally I'd add an integer column, and number the items in the order I want them.

(Then use a SQL ORDER BY clause in the query to put the records in that order).

That would work for a static list. But if my users can re-order the items themselves using drag and drop, and also add and delete items, how is that normally handled in the database?

For instance, if they moved the 2nd item in the list to the 7th position, would I have to renumber all the items, and update the entire table?

That's the way I do it.

I have an integer column in my table called "sort_order"
I don't let the user set this value through input.
I do this:
1. for add:
a)see which is the max sort_order
b) for the new record I set sort_order with: max+1
2)for delete:
a) I get the sort_order for the deleted record
b) update the records with condition sort_order>deleted_record sort_order
somenthing like: "UPDATE table SET sort_order=sort_order-1 WHERE sort_order>deleted_record_sort_order"
3)Update
nothing beacuse I don't let him modify the sort_order :))
4) Reorder
a)I get the id of the moved record
b) I get the id of the record which wil be right above the moved record
c) sent them to my php backend
d) in callback I refetch the data
here is the code:

I. for recordDrop

isc.ListGrid.create({
    ID: "dbList",
   
    ................... 

    canReorderRecords: true,
    recordDrop : function (dropRecords, targetRecord, index, sourceWidget) {
       this.Super('recordDrop', arguments);
       var rec=this.getSelectedRecord();
       var thisID = rec.id;

        if(index!=0)
         {
           var prevIndex = index - 1;
           var refRec = this.data.get(prevIndex);
           if(refRec!= null)
            {
              var prevID = refRec.id;
            }
            else  {
                    var lastIndex= this.data.getLength()-1;
                    var refLRec = this.data.get(lastIndex);
                    var prevID = refLRec.id;
                  }
         }
         else
         {
           var prevID = 0;
         }
         var data = { table:"<?=$table?>", prevID:prevID , thisID: thisID};
        RPCManager.sendRequest({
            params: data,
            serverOutputAsString: true,
            actionURL:"db.php?_operationType=reorder",
            callback:"doShow(rpcResponse)"
        });
      },
  autoFetchData:true
});
    function doShow(rpcResponse) {
        dbList.setData([]);
        dbList.fetchData();
    }

II.here is the code for db.php :

switch($_REQUEST['_operationType'])
{
  case 'reorder':
  if(isset($_POST['table']) && is_numeric($_POST['prevID']) && is_numeric($_POST['thisID']))
   {
     $table=$_POST['table'];
     $thisID=$_POST['thisID'];
     $prevID=$_POST['prevID'];
     if($prevID==0) $thisNewSort=1;
     else
      {
        $prevQuery=mysql_query("SELECT id,sort_order FROM ".$table." WHERE id='".$prevID."'");
        $prevRec=mysql_fetch_array($prevQuery);
        $thisNewSort=$prevRec['sort_order']+1;
      }
     $thisQuery=mysql_query("SELECT id,sort_order FROM ".$table." WHERE id='".$thisID."'");
     $thisRec=mysql_fetch_array($thisQuery);
     $thisOldSort=$thisRec['sort_order'];
     if($thisNewSort<$thisOldSort)
      {
      mysql_query("UPDATE ".$table." SET sort_order=sort_order+1 WHERE sort_order>=".$thisNewSort." AND sort_order<".$thisOldSort);
      mysql_query("UPDATE ".$table." SET sort_order=".$thisNewSort." WHERE id='".$thisID."'");
      }
    elseif($thisNewSort>$thisOldSort)
      {
      $thisNewSort=$thisNewSort-1;
      mysql_query("UPDATE ".$table." SET sort_order=sort_order-1 WHERE sort_order<=".$thisNewSort." AND sort_order>".$thisOldSort);
      mysql_query("UPDATE ".$table." SET sort_order=".$thisNewSort." WHERE id='".$thisID."'");
      }

   }
  break;

Hope this helps you,
John

[Isomorphic]

John, thanks much for posting that code and explanation. I'm sure that will help a number of users!

[JohnBlack]

Hy,

I've made a little tweak to the code for recordDrop:

I've changed:

var prevID = refRec.id;

with

           if(refRec!= null)
            {
              var prevID = refRec.id;
            }
            else  {
                    var lastIndex= this.data.getLength()-1;
                    var refLRec = this.data.get(lastIndex);
                    var prevID = refLRec.id;
                  }

I did this because of this scenario:
User adds a new record add suddenly He changes his mind about adding and doesn't hit the "Esc" button to discard it.
Now he tries to move another record right under the undiscarded new record.
Because the primaryKey( "id" field in this eg) is set by Mysql auto_increment I won't have the field until the response from an add operation so obviously on the client side I'll get the error: "refRec has no property"

Yes I know It's very improbably -- but if I found it be sure that the client finds it too. From my experience I know that they are very ingenious in this matter. :))

[msatkevich]

Hi John,

Yes, thank you for the explanation! I knew there had to be some SmartCode written to support the SmartClient drag and drop magic. ;) Thanks for putting into code my nebulous thoughts.

Isomorphic, would this reorder operation in the database be handled automagically if we had your server component in place?

Thanks,
Mike

[msatkevich]

Hi John,

I had to make an update to your code for SC8.0.

When I dragged a record down, it was already moved into place when the code attempted to get the id of the record above the destination. They were the same record id!

Dragging up was not a problem. This must be new behavior in SC8.0?

I suspected that the super code was being executed too soon, so I moved this.Super to the end of the function, and that worked!

However, there are a couple warnings that appear if the user's mouse is a little too high or low when they drop the record into the top or bottom slot.


16:16:41.148:TMR3:WARN:ResultSet:isc_ResultSet_4 (created by: demoList):get: invalid index -1
15:52:05.307:MUP1:WARN:ResultSet:isc_ResultSet_4 (created by: demoList):getRange(12, 13): start beyond end of rows, returning empty list


If anyone else knows a better way to do this in SC 8.0, let me know.

Thanks,
Mike

[msatkevich]

I also had to make an adjustment to refresh the ListGrid datan after each operation (add, delete, reorder).

The reason is that the sort_order field is embedded in the ListGrid records.

Even though you renumber them in the database, the ListGrid data still has the old numbers!

You could update the existing data in the ListGrid (which would be duplicating your code in JavaScript, after you've already written it on the back end in PHP).

I figured it was easier to simply refetch the data with the new sort values.

Mike