Update the database when I move rows of a table

Question

Update the database when I move rows of a table

Navigation

#1 by (3 votes)
#2 by (0 votes)

5

View code:

<div id="tabs">
        <div class="col-md-12" id="current">
            @include('cms.public.views.partials._messages')         
            <div id="table1">
              <table class="table">
                <thead style="color:white">
                  <tr>
                    <th><a href="{{route('admin.projects.order', ['field' => 'id','order' => 'asc'])}}"><span class="glyphicon glyphicon-arrow-up" id="orderByIdAsc"></span></a>ID<a href="{{route('admin.projects.order', ['field' => 'id','order' => 'desc'])}}"><span class="glyphicon glyphicon-arrow-down" id="orderByIdDesc"></span></a></th>
                    <th><a href="{{route('admin.projects.order', ['field' => 'slug','order' => 'asc'])}}"><span class="glyphicon glyphicon-arrow-up" id="orderBySlugAsc"></span></a>SLUG<a href="{{route('admin.projects.order',['field' => 'slug','order' => 'desc'])}}"><span class="glyphicon glyphicon-arrow-down" id="orderBySlugDown"></span></a></th>
                    <th><a href="{{route('admin.projects.order', ['field' => 'order','order' => 'asc'])}}"><span class="glyphicon glyphicon-arrow-up" id="orderByOrderAsc"></span></a>ORDER<a href="{{route('admin.projects.order', ['field' => 'order','order' => 'desc'])}}"><span class="glyphicon glyphicon-arrow-down" id="orderByOrderDesc"></span></a></th>
                    <th><a href="{{route('admin.projects.order', ['field' => 'public','order' => 'asc'])}}"><span class="glyphicon glyphicon-arrow-up" id="orderByPublicAsc"></span></a>PUBLIC<a href="{{route('admin.projects.order', ['field' => 'public','order' => 'desc'])}}"><span class="glyphicon glyphicon-arrow-down" id="orderByPublicDesc"></span></a></th>
                    <th><span class="glyphicon glyphicon-cog"></span></th>
                  </tr>
                </thead>
                <tbody style="color:white">
                  @foreach ($projects as $key => $project)
                    <tr>
                      <td>{{$project->id}}</td>
                      <td>{{$project->slug}}</td>
                      <td>{{$project->order}}</td>
                      <td>{{$project->public}}</td>
                      <td><a href="{{ route('admin.projects.show', $project->id)}}" class="btn btn-info btn-sm">View</a> <a href="{{ route('admin.project.edit', $project->id)}}" class="btn btn-success btn-sm">Edit</a></td>
                    </tr>
                  @endforeach
                </tbody>
              </table>
              <br><br>
            </div>
        </div>
</div>

Jquery Code:

$("#tabs").tabs();

$("tbody").sortable({
    items: "> tr",
    appendTo: "parent",
    helper: "clone"
}).disableSelection();

$("#tabs ul li a").droppable({
    hoverClass: "drophover",
    tolerance: "pointer",
    drop: function(e, ui) {
        var tabdiv = $(this).attr("href");
        $(tabdiv + " table tr:last").after("<tr>" + ui.draggable.html() + "</tr>");
        ui.draggable.remove();
    }
});

Could someone give me an example of how to automatically update the data in the database, when moving a row?

Update

I will be much more detailed so that you understand me better and can help me.

The view is sorted by the 'order' field, that is, the data is displayed as follows:

id - pepito- 29 - public

id - slug - 28 - public

id - slug - 27 - public

id - slug - 26 - public

id - slug - 25 - public

id - slug - 24 - public
y así hasta el

id - ultimopepito- 1 - public

If I drag, for example, the one with the field 'order' = a 1 above the whole view looks like this:

id - ultimopepito- 1 - public

id - pepito- 29 - public, etc.

The idea would be to update the data when doing the 'drop' and it looks like this:

id - ultimopepito - 29 - public

id - pepito - 28 - public

For this, when doing the 'drop' you have to update the data automatically.

I think that could be done like this: Take the order of the drag and take the order of the drop. If the movement is up, I would subtract one from the ones below the drop and vice versa, the problem is that I do not have the knowledge to do this.

javascript php ajax laravel laravel-5

asked by Lluís Puig Ferrer 23.08.2017 в 14:49

source

2 answers

0

You have to capture the new order of the elements and send them to the database, it can be through ajax.

 $(function() {
    $('#sortable-8').sortable({
       update: function(event, ui) {
          var productOrder = $(this).sortable('toArray').toString();
          $("#sortable-9").text (productOrder);
       }
    });
 });

update (event, ui): This event is triggered when a sort operation stops and the position of the item has been changed.

I leave a link where he explains very well everything with examples link

answered by 25.08.2017 в 17:20

Save images on sqlite android Compare content of two strings in c?

score 3 · Accepted Answer

There are several ways to do it, the easiest would be with sortable of jQuery UI, using its update () method, but you should preferably include the id in the html attributes, something like this:

              @foreach ($projects as $key => $project)
                <tr id="{{$project->id}}">
                  <td>{{$project->id}}</td>
                  <td>{{$project->slug}}</td>
                  <td>{{$project->order}}</td>
                  <td>{{$project->public}}</td>
                  <td><a href="{{ route('admin.projects.show', $project->id)}}" class="btn btn-info btn-sm">View</a> <a href="{{ route('admin.project.edit', $project->id)}}" class="btn btn-success btn-sm">Edit</a></td>
                </tr>
              @endforeach

Then you do the sending by ajax of the order of the id:

$("tbody").sortable({
    items: "> tr",
    appendTo: "parent",
    helper: "clone",
    update: function( event, ui ) {
        let newOrder = $(this).sortable('toArray');
        $.ajax({
            method: "POST",
            url: {{ route('updateOrder') }},
            data: {ids: newOrder}
        })
       .done(function( msg ) {
            // render table with new order?
       });
    }
}).disableSelection();

And in the controller you organize the query that you are going to send to update all the ids in the database:

// en el array ids, los "keys" serían el orden, los cuales puedes modificar según lo que requieras
$ids = $request->ids;

// invertimos el orden de los ids, para que sea descendente
$ids = array_reverse($ids);

$caseQuery = 'CASE id ';
foreach ($ids as $order => $id) {
    $caseQuery .= "WHEN $id THEN $order ";
}
$caseQuery .= ' END';

DB::table('projects')
        ->whereIn('id', $ids)
        ->update(['order' => $caseQuery]);

EDITING

Laravel has a limitation in the update method that does not allow us to use the string that includes CASE ... END , this is because Laravel expects a parameter (integer) that it will replace when making the bind in PDO.

To be able to solve the problem we will use the same method update() but we will pass the query as "raw" and we append all its parameters manually so that PDO takes care:

$queryParams = [];

// el query será definido en su totalidad de forma manual
$query = 'UPDATE projects SET ordering = CASE id ';

// agregamos cada parámetro de orden y de id al array para respetar las convenciones de PDO
foreach ($ids as $order => $id) {
    $query .= 'WHEN ? THEN ? ';
    $queryParams[] = (int) $id;
    $queryParams[] = (int) $order;
}

// por último agregamos los ids implicados en el update
$queryParams = array_merge($queryParams, $ids);

// generamos los ? necesarios para el array de ids en el query PDO
$whereInArray = array_fill(0, count($ids), '?');
$whereInString = implode(", ", $whereInArray);

// agregamos dicho string generado al query final
$query .= "END WHERE id IN ($whereInString)";

// realizamos el update
DB::update($query, $queryParams);

Another method would be to update each record, but it could be slightly slower for so many requests to the database, for which you would use a transaction: link