User defined list order

[NotionCommotion]

I have provided a list of records:

SELECT id, name, bla FROM mytable WHERE fk=123;

But now I wish to allow the user to define the order of the list, so I add an extra integer column. If inserting multiple rows, maybe I use a user defined variable such as @position := @position + 1 to increment them.

SELECT id, name, bla FROM mytable WHERE fk=123 ORDER BY position ASC;

But then a new record is inserted between two records, or record is moved.

 

Do I renumber all the position columns?  Doesn't seem like a good idea.

 

Instead, thinking of changing position to DECIMAL(6,6), find the existing records above and below the desired position, and set the new record to the average of the two?  Maybe add 0.000100 instead of one with my auto-increment user defined variable approach and hope I have room?

 

I am sure I am not the first one attempting to do this.  Any thoughts how best to implement?

 

 

 

 

[kicken]

If you're just wanting to tack a new item onto the list at the end, you could just set it's position to MAX(position) and let the user sort things later.

 

If you want to add it after a specific item, first update any items after the target one

UPDATE mytable SET position=position+1 WHERE fk=123 AND position > (SELECT position FROM mytable WHERE pk=456)

Then insert into the gap.

 

That said, how does the user change the order? You may not need either of the above if you just let the user specify what the position is to begin with.

 

For example, I generally let user's drag-and-drop sort using something like jQuery UI's Sortable and every item has a hidden input associated with it called displayOrder

 

After the user re-arranges the items some the JS code loops the item setting that displayOrder field to that item's position. When they then submit the form I just update all the items with the new display order settings (and other changes if applicable).

[NotionCommotion]

Thanks kicken,

 

I ended up thinking that the integer approach and reordering was so bad after all and was implementing very similar to what you showed.

 

I am definitely more interested in your additional remarks about not needing to in the first place, and I totally agree you are right on.  Do you also sometimes save results on jQueryUi sorter's change event?

 

Off topic, but do you know if jQuery's sorter can handle both a tables columns and rows?  If not, maybe https://sindu12jun.github.io/table-dragger/ will work.  Ever use it?

[kicken]

Do you also sometimes save results on jQueryUi sorter's change event?

The change event is triggered every time elements are swapped, even during dragging. I don't care about the state of things during dragging, only when they finally drop the item in place. As such, the update event is a better choice.

    $blockList.sortable({
        items: '> tr'
        , handle: '.sortable-handle'
        , helper: createSortableHelper
        , axis: 'y'
        , update: updateDisplayOrder
    });

    function updateDisplayOrder(){
        var order = 1;
        var regex = /^\w+\[\w+]\[displayOrder]/;

        $blockList.find('tr').each(function(){
            var $tr = $(this);
            $tr.find('input').filter(function(){
                return regex.test(this.getAttribute('name'));
            }).val(order);
            $tr.find('.block-number').text(order);

            order++;
        });
    }

Then the PHP code just saves the values when the form is submitted.

 

 

Off topic, but do you know if jQuery's sorter can handle both a tables columns and rows?  If not, maybe https://sindu12jun.github.io/table-dragger/ will work.  Ever use it?

I've never tried table columns, but I don't think it'd work very well. Your sortable items are supposed to be complete elements, which a table column is not. You might be able to hack something together. Never used that table dragger but if it can do what you need then may as well use it.

[NotionCommotion]

Ah yeah, the update callback is a much better choice.  You've been doing this for a while, haven't you

 

The first example in the table-columns demo site shows using columns, and the last shows using both columns and rows.  Haven't attempted to do so myself, but will post the positive or negative results when I do.

[NotionCommotion]

The table-columns library works pretty well.  See https://jsbin.com/papohan/edit?html,output.  The only issue is I can either prevent the first row or the first column from being moved, but not both at the same time, and will need to figure that part out still.

 

Thanks for pointing me in the right direction and using the client to specify the position.