How to reorder columns with Knex?

Question

I am pulling all of my data from an Sqlite3 table using Knex, electron and JavaScript and I wish to reorder the columns either on the Knex query or in the HTML/JavaScript side.

My sqlite3 db has the following header data:

id|Role|Password|Reference

With the following code, the table displays in the following order:

Password|Reference|Role|id

I have attempted to utilize the .orderBy method in Knex and have also attempted to reorder in JavaScript, but I cannot seem to reorder the columns.

The Electron side of things, I have:

ipcMain.on('getUserTable:all', (event) => {
  let getUserTable = 
knex('User').select(['id','Role','Reference','Password']).orderBy('Role');
  getUserTable.then(function(tableData){
    newWin.webContents.send("userResultSent", tableData);
  });
});

In the HTML side of things, I have:

ipc.on('userResultSent', (event, tableData) => {
    var html = '<table>';
    html += '<tr>';
    for( var j in tableData[0] ) {
        html += '<th>' + j + '</th>';
        }
    html += '</tr>';
    for( var i = 0; i < tableData.length; i++) {
        html += '<tr>';
            for( var j in tableData[i] ) {
            html += '<td>' + tableData[i][j] + '</td>';
        }
    }
    html += '</table>';
    document.getElementById('db_output_container').innerHTML = html;
});

I wish to be able to query the db so that the array displays in the exact order as in the table.

Objects do not have any order of properties. You need to send this information as an array within `tableData` or somehow else. `for( var j in tableData[0] ) { html += '' + j + ''; }` might be implementation dependent and not guaranteed. From MDN "A for...in loop iterates over the properties of an object in an arbitrary order" — Yury Tarabanko, Jul 10 '19 at 12:29
`orderBy` refers to the order of objects in resultset not the order of properties. Because @see above — Yury Tarabanko, Jul 10 '19 at 12:31
So you are suggesting that I create an array from the array passed in from Sqlite3? — bechavez, Jul 10 '19 at 15:49
No I suggest to send column order as metadata `newWin.webContents.send("userResultSent", {columns: ['id', 'Role' and so on], tableData})` and the loop over columns instead of object properties. This would guarantee the order. — Yury Tarabanko, Jul 10 '19 at 16:14

score 0 · Answer 1 · answered Jul 10 '19 at 12:28

At the end of the second for loop, you should close the tr tag. Indeed, you open it right after the second for loop but you don't close it. I haven't tested yet but it should work.

Your html file should looks like this.

ipc.on('userResultSent', (event, tableData) => {
    var html = '<table>';
    html += '<tr>';
    for( var j in tableData[0] ) {
        html += '<th>' + j + '</th>';
        }
    html += '</tr>';
    for( var i = 0; i < tableData.length; i++) {
        html += '<tr>';
            for( var j in tableData[i] ) {
            html += '<td>' + tableData[i][j] + '</td>';
        }
        html += '</tr>'; /* i added this line here */
    }
    html += '</table>';
    document.getElementById('db_output_container').innerHTML = html;
});

Furthermore, it should be better to use createElement method in javascript instead of innerHTML. This [link](https://stackoverflow.com/questions/2946656/advantages-of-createelement-over-innerhtml) should help you — MrAnyx, Jul 10 '19 at 15:10
I appreciate your response, I have tested both ways and it is still closing the with on execution. This is cleaner though, thanks for this. — bechavez, Jul 10 '19 at 15:48

score 0 · Accepted Answer · answered Jul 10 '19 at 17:21

The problem with your current approach is that object are unordered bags of properties. So it does not matter how order your columns - properties order is not guaranteed.

If you need specific order you could use Array instead.

Since you have general code to display tabular data you could do the following

ipcMain.on('getUserTable:all', (event) => {
  const columns = ['id','role','reference','password']

  let getUserTable = 
knex('User').select(columns).orderBy('role');
  getUserTable.then(function(tableData){
    newWin.webContents.send("userResultSent", {columns, tableData});
  });
});

When creating html

ipc.on('userResultSent', (event, {columns, tableData}) => {
    var html = '<table>';
    html += '<tr>';

    columns.forEach(column => {
        // if you want to capitalize names just do it here
        html += '<th>' + column + '</th>';
    })

    html += '</tr>';
    for( var i = 0; i < tableData.length; i++) {
        html += '<tr>';
        columns.forEach(column => {
          html += '<td>' + tableData[i][column] + '</td>';
        })

        html += '</tr>'; 
    }
    html += '</table>';
    document.getElementById('db_output_container').innerHTML = html;
  })

I think this may work, I will give it a shot tomorrow, thank you! — bechavez, Jul 10 '19 at 21:10
Sorry for the delay, this works perfectly, thank you so much!! Lesson learned here big time. — bechavez, Jul 18 '19 at 20:14

How to reorder columns with Knex?

2 Answers2