The Pug Automatic

Rails/jQuery UI sortables with single UPDATE query

Written November 15, 2008. Tagged JavaScript, Ruby, Ruby on Rails, SQL, Ajax, jQuery.

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:

$('#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:

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

Then make a controller action:

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:

# 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

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.