Laravel: drag-and-drop repositioning with auto-save of DB entries

intro.markdown

Laravel: drag-and-drop repositioning with auto-save of DB entries

Use case: Database entries are represented in a table. By grabbing and moving a row up or down the table, you can change the entries' order/position. The changes are submitted automatically via ajax.

• Uses jQueryUI (custom download: sortable is needed)
• newly created elements are added to the top (see route /jobs/create)

jobs.blade.php

// head
{{ HTML::style('scripts/vendor/jquery-ui/jquery-ui.min.css') }}

// handle in each table row
<a class="handle">Move</a>

// end of body
@section('additional-scripts')
	{{ HTML::script('scripts/vendor/jquery-ui/jquery-ui.min.js') }}
	<script>
	$('table.db tbody').sortable({
		'containment': 'parent',
		'revert': true,
		helper: function(e, tr) {
			var $originals = tr.children();
			var $helper = tr.clone();
			$helper.children().each(function(index) {
				$(this).width($originals.eq(index).width());
			});
			return $helper;
		},
		'handle': '.handle',
		update: function(event, ui) {
			$.post('{{ route('jobs-reposition') }}', $(this).sortable('serialize'), function(data) {
				if(!data.success) {
					alert('Whoops, something went wrong :/');
				}
			}, 'json');
		}
	});
	$(window).resize(function() {
		$('table.db tr').css('min-width', $('table.db').width());
	});
	</script>
@endsection

migration_create_jobs.php

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateJobs extends Migration {

	public function up()
	{
		Schema::create('jobs', function($table)
		{
			$table->increments('id');
			$table->string('profession', '256');
			$table->text('description');
			$table->integer('position')->unsigned();
			$table->boolean('visible')->default(false);
			$table->timestamps();
		});
	}

	public function down()
	{
		Schema::dropIfExists('jobs');
	}

}

routes.php

<?php

Route::post('/jobs/create', function(){
  // Validation [...]
	Jobs::increment('position');
	$jobs = new Jobs;
	  // [...]
		$jobs->position = 1;
		$jobs->save();
		return Redirect::route('home');
	}
});

Route::post('/jobs/reposition', function()
{
	if(Input::has('item'))
	{
		$i = 0;
		foreach(Input::get('item') as $id)
		{
			$i++;
			$item = Jobs::find($id);
			$item->position = $i;
			$item->save();
		}
		return Response::json(array('success' => true));
	}
	else
	{
		return Response::json(array('success' => false));
	}
});

Thanks for sharing!

I'm curious though... If you are saving position right on the Job itself, then changing position of one item means a bunch of other items' positions will be edited in the process. What if you want to offer ability to also order by updated_at? Now ordering by updated_at won't show an accurate representation of actual Job edits, because they'll be re-timestamped every time you perform a reposition.

I guess you could have a Model observer which would listen for position-only item edits, and pass in the original updated_at to prevent it from being re-timestamped.

How would you handle this?

@jesseleite: Very interesting question. To be honest, this script was intended for a small website, so it is not that sophisticated. However, I think the best solution would be to create an additional table. What do you think?

How did you get access to Input::get('item') ?
I can't see where you pass data to the route "jobs/reposition"

thanks for this nice explanation ,
can you also put the PHP (html tags) code in the Jobs file?
@Photoshopper : it's from JQueryui with "serialize" method , but you have to call each item (li for example) with id="item_{{jobs_id}}" and then you will get it as array of item[]

Could you maybe post a full working example by using your method please? :)

@tillsanders any chance you could post the table just to make sure mines close ? I'm looking for a simple way to do this, however I'm new to JQuery and have no idea what the table would look like

Could you post a full working example by using your method please? 👍

Thanks for sharing!

The code generated is:
item[]=4&item[]=8&item[]=6&item[]=9
is that right?

would you mind share table.db tbody (view table) to see what is where exactly and which part of JavaScript get what elements?

Thanks.

Input::has('item') is false why?

https://www.codecheef.org/article/laravel-drag-and-drop-with-sortable-data-using-jquery-ui