How to order the list by ID(autoincrement) from DB and reorder without changing this ID?

Vol.And

The situation:

I get a list from DB. There are 2 columns with numbers: Id(autoincrement) and nr.

This is how it fetches the table, what is wrong:

I need it to order by ID column, but no change has to be perform to Id number when I drag&drop row to a different place.
The code looks like this:

$(document).ready(function () {
        let data_arr = <?php echo $json_array;?>;
        $('#draggable').DataTable({
            data: data_arr,
            "paging": false,
            "searching": false,
            "ordering": true,
            "autoWidth": false,
            "info": true,
            rowReorder: {
                dataSrc: "id"
            },
            "columns": [
                {"data": 'nr'},
                {"data": 'id'},
                {"data": 'topic'},
                {"data": 'answers'},
                {"data": 'hash'},
                {"data": 'active'}
            ],
            "columnDefs": [
                {"className": "dots", "targets": [2]},
                {"className": "table-30", "targets": [3]},
                {
                    "targets": [4, 5],
                    "visible": false
                },
                {"orderable": false, "targets": [1, 2, 3]}
            ]
        });
    });

as you can see dataSrc is id, but it still orders by nr. I need by Id and no change to Id if rowreorder will be performed. I just need to save new order numbers in DB.

Is it possible? What should I change in order to achieve the goal?

P.S. btw, rowreorder is not functional if dataSrc is smth different than nr. Why is that?

kthorngren

The second paragraph of the RowReorder docs state this:

RowReorder operates on a data swap method so it can operate even on non-sequential data......The data point in the row that is modified is defined by the rowReorder.dataSrc. Normally you will want this to be a sequential number!

Basically that means the id column, being used as the dataSrc, will change to keep the table in the proper order. If you don't want to change the id column then you can create an additional, hidden if you like, column with the index.

rowreorder is not functional if dataSrc is smth different than nr. Why is that?

RowReorder works when the dataSrc column is sorted and has unique data. The swapping mechanism is what is used to keep the table in the reordered order.

Hope all this makes sense.

Kevin

Vol.And

I think I got the answer for > rowreorder is not functional if dataSrc is smth different than nr.
if I swap columns on lines 14, 15, so the id comes as first, then it will be draggable and sortable in conjunction with dataSrc:"id". That means that the column number 0 will be sortable and draggable.

As for addittional index: that's a good point of view you've mentioned.
I can add a new column with index, that will be changable as rows order changes.
I add a code for index iteration, for instance:

Table

    <thead>
            <tr>
                 <th>index</th> // new column (0) for reordering 
                 <th>Nr.</th>
                 <th>Topic</th>
                 <th>Answers</th>
                 <th>hash</th>
                 <th>Active</th>
             </tr>
    </thead>

Datatable

          let table = $('#draggable').DataTable({
                 .....,
               rowReorder: {
                    dataSrc: "id" // what should be here?
                },
             "columns": [
                            {"data": 'id'},
                            {"data": 'nr'},
                            {"data": 'topic'},
                            {"data": 'answers'},
                            {"data": 'hash'},
                            {"data": 'aktiv'}
                        ],
                "columnDefs": [
                    {"className": "dots", "targets": [2]},
                    {"className": "table-30", "targets": [3]},
                    {
                        "targets": [4, 5],
                        "visible": false
                    },
                    {"orderable": false, "targets": [1, 2, 3]}
                ]
            });

increment for new column:

                table.on( 'order.dt search.dt', function () {
                    table.column(0, {search:'applied', order:'applied'}).nodes().each( function (cell, i) {
                        cell.innerHTML = i+1;
                    } );
                } ).draw();

I get:

and it can be the right thing that I am trying to achieve (I don't have id 3 in DB, just 2 then 4; and the last record in DB has id 52)

But I am worried about this structure:

    rowReorder: {
                    dataSrc: "id"
    },
    "columns": [
                    {"data": 'id'},
                    {"data": 'nr'},
                    {"data": 'topic'},
                    {"data": 'answers'},
                    {"data": 'votinghash'},
                    {"data": 'aktiv'}
     ],

I load somewhere all the IDs, and this is not the first column. Moreover, I can't remove {"data": 'id'} the whole table will be destroyed.

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

Vol.And

ok, made it as simple as I could, please check the link below:

http://live.datatables.net/pezebozo/1/edit
(unconsciously copied local links from my local mashine and now cannot change them to CDNs in this template)

would be happy to hearing from anybody soon. Thanks.

tangerine

"...now cannot change them to CDNs"

Why?

Vol.And

Why?

I change and save as Template - the old ones come back after page refresh

I've cloned and added CDNs:

http://live.datatables.net/zocifuha/1/edit

colin

The test case isn't running. Please can you fix the errors so we can see the issue you want help with,

Colin

Vol.And

http://live.datatables.net/zocifuha/2/edit

now fetches the table, but doesn't drag ((

kthorngren

You weren't loading dataTables.rowReorder.min.js. Here is the updated example:
http://live.datatables.net/zocifuha/3/edit

The rows can be reordered. Please describe the steps to recreate the issue or the details of what you are wanting to do differently.

Kevin

Vol.And

@kthorngren Thank you very much for your support!

so the goal is to achieve such functionality:

1) I get from order table order_string (this will always change as user changes the order of rows)

2) Then I get values for D&D table which have such fields (autoincrement ´id´ and ´nr´ - a number of question/topic to save in order_string):

3) I get a list of questions(rows), order and convert it before fetching, so the order of rows will be [keys] from $order array:

if ($list && $list_order) { usort($list, static function (array $a, array $b) use ($order) { return $order[$a['nr']] <=> $order[$b['nr']]; }); }

var_dump($order):

Anyways, I've managed to save the order of nr after it was changed by user, but it sorts not by nr. After I move row with number 9 after nr 10 it is saved in db:

but after page refresh the order will be as before

1. 9
2. 10
3. 11
4. and so on

I assume it is with this connected:

´rowReorder: {
dataSrc: "id",´
but simply changing ´id´ to ´nr´ won't do the trick.

Hope, I expressed my thoughts clearly. If questions, please ask!

kthorngren

I'm not sure I understand everything but it sounds like you want the table sorted by the nr column and the rowReorder.dataSrc to be nr. You can use orderFixed to always order the table by the nr column and you can use order to set the initial order of the table. See this example:
http://live.datatables.net/zocifuha/4/edit

Is this what you are wanting?

Kevin

Vol.And

not really, The numbers (´nr´ and ´id´) are not allowed to be changed.

The order how the rows are fetched into d&d table comes from the other table (column order_string, see screenshot). This order is always changing as soon as user drags and drops the row to a new place.

kthorngren

The numbers (´nr´ and ´id´) are not allowed to be changed.

As I pointed out before the docs state this:

RowReorder operates on a data swap method so it can operate even on non-sequential data......The data point in the row that is modified is defined by the rowReorder.dataSrc. Normally you will want this to be a sequential number!

Maybe you need to create a new field that holds the order and can be updated when the order changes. This will need to be independent of your auto-increment fields.

Kevin

Vol.And

Thank you Kevin, that you don't leave me on this all alone, but I am afraid I didn't explained it right. So I will do it one more time, may be this time I will understand, that what I want is not a good logic and will cancel this idea and swithc to something else.

So, let's say it is our starting point - no changes are made to the table order or whatsoever.
Table with topics (rows):
in this table we get only the topics with their values such as id, nr and so on. No changes are made in this table except deleting or adding topics.

then, I got order table (here is defined the order of topics in browser table):
this string changes corresponding to how user changes the order

this is console.log of data_arr that comes from backend/db:

this is how it fetches frontend table:

and now comes the toughest part. As I change the order (I want to switch 9 and 10):
data_arr now as on screen

and in DB is also changed:

But if I refresh the page - the order comes from db as is:

but in browser user sees the same order:

because DataTable is ordered by id

rowReorder: {
dataSrc: "id", // changing this to nr will not help
selector: "tr"
},
Honestly saing I don't need index and NR. in frontend table at all, I left it only for dev purposes. I made it so complex that I've lost myself in this ((

kthorngren

Still not 100% sure I understand. If the nr field is the order you want to display the table then use the order or orderFixed options to order the table by column 2.

But you said the nr values are not to be changed.

Honestly saing I don't need index and NR. in frontend table at all,

RowReorder needs an index column. The column can be hidden using columns.visible. Once you get the solution worked out you can hide those columns.

How are you updating the database when the columns are reordered? Are you using the Editor for this?

Kevin

Vol.And

How are you updating the database when the columns are reordered? Are you using the Editor for this?

no, I collect the order of ´nr´ in array and send it via ajax call to backend, then save this string in order table in DB

kthorngren

If I understand correctly the nr field is the order you want to display the table. Then use the order options li described earlier to order the table by this column.

Kevin