Rails/jQuery UI sortables with single UPDATE query

I just wrote some sortable code for a Rails/jQuery app and figured I would blog just how little code it takes, and also the single MySQL query I used on the backend.

I have a #images div containing several .image divs. I want the .image divs to be drag-and-drop sortable, and for the ordering to be persisted to the database (in a column named “ordinal”).

The JavaScript sorting, using Sortables from jQuery UI:

1
2
3

$('#images').sortable({items:'.image', containment:'parent', axis:'y', update: function() {
  $.post('/admin/images/sort', '_method=put&authenticity_token='+AUTH_TOKEN+'&'+$(this).sortable('serialize'));
}});

So my .image divs are sortable within their containing #images, and can only be dragged on the y axis (up and down). When the sorting is done, an Ajax request is sent to /admin/images/sort. The AUTH_TOKEN bit is Rails CSRF protection – see this post for more details and another way of handling it.

The Ajax request contains params like image[]=3&image[]=1&image[]=2, reflecting the order. The parameter name and values are taken from the element ids (e.g. “image_1”).

I route the path:

1

admin.resources :images, :collection => { :sort => :put }

Then make a controller action:

1
2
3
4
5

def sort
  order = params[:image]
  Image.order(order)
  render :text => order.inspect
end

What’s rendered isn’t important, but you should render something or you get a 404.

The model method is just this:

1
2
3
4
5
6
7

# Set passed-in order for passed-in ids.
def self.order(ids)
  update_all(
    ['ordinal = FIND_IN_SET(id, ?)', ids.join(',')],
    { :id => ids }
  )
end

This generates a query like

1

UPDATE images SET ordinal = FIND_IN_SET(id, "3,1,2") WHERE id IN (3,1,2)

which sets the ordinal column to the position of the record id in that set.

Whenever I need the images ordered, I just make sure they’re sorted by ordinal ASC, created_at ASC.

That’s all the code it takes.