Specify column ordering in qsqltablemodel (was: problem moving columns of a QTableView)

[fluca1978]

Hi,
in my application I've got a QTableView associated to a QSqlTableModel. Everything works fine, but I want to implement a way to change the ordering of the columns in the qtableview, so I created a qlist of strings corresponding to the column names in the order I want them to appear. For instance, if the table has columns (a,b,c) and I want them to appear as (c,a,b) I populate the list with "c", "a,"b". Then, to move and place each column, I do the following:

@
tableModel->select();

for( int j = 0; j < columnMapping.size(); j++ ){
QString tableViewColumnName = columnMapping.at( j );
// set table header
tableModel->setHeaderData( j,
Qt::Horizontal,
tableViewColumnName
);

        int fromIndex = tableModel->fieldIndex( tableViewColumnName );
        int toIndex   = j;

        if( fromIndex != toIndex )
             tableView->horizontalHeader()->moveSection( fromIndex , toIndex );



    }

@

The problem is that only headers are moved correctly, data is not moved. Am I missing something?

[andre]

Yes, you are missing the build-in functionality in QTableView that can already do this for you :-)

Checkout [[doc:QHeaderView]] moveSection method.

[fluca1978]

Sorry, I don't get your hint: isn't the line

@tableView->horizontalHeader()->moveSection( fromIndex , toIndex );@

doing what you are suggesting me?

[andre]

Yes, it should. Did you try?

[fluca1978]

Yes I did, the code I posted in the first message was used to set the header values and to move columns, but it has the effect of moving only the headers, and not the real data in the table.

[andre]

Hmmm... That is strange. I think I did not read your original posting correctly, I apologize for that. I have to go now, but I will take another look at this issue later, ok?

[fluca1978]

I made other testings and have different results. It seems to me that the problem is trying to move twice the same columns, for instance switching the column 5 and 6 produces moving 5 to 6 and then 6 to five, that could make the data not moving. Moreover, it seems to me that moving the headers while labeling it confuses the tableview. Some times in fact I see the headers not moving, and other times they move but data does not follow them. It could be really simpler if I can specify the column ordering in the database query without having to specify all the query string.

[fluca1978]

No way I can get it working. I've modified the implementation so that indexes are not moved twice (using movingColumns list), I've moved the select on the model from before to after the moving, but still get the data moved but the header fixed.

@
QList<int> movingColumns;
for( int j = 0; j < columnMapping.size(); j++ ){
QString tableViewColumnName = columnMapping.at( j );
// set table header
tableModel->setHeaderData( j,
Qt::Horizontal,
tableViewColumnName
);

        int fromIndex = tableModel->fieldIndex( tableViewColumnName );
        int toIndex   = j;
        *movingColumns.append( toIndex);*
        if( fromIndex != toIndex *&& ! movingColumns.contains( fromIndex )* )
             tableView->horizontalHeader()->moveSection( fromIndex , toIndex );



    }

@

What happens if I switch the position of two columns is that I get the column data switched, but the labels in the table header remains in the original position. There must be an easy way to do this...

[fluca1978]

Since I'm not getting rid of this problem, I decided to try to move only one column and see what happens.
I've got a table with the columns (a,b,c,d,e,f, g) having a = 0, g = 6.
If I do:

@
tableView->horizontalHeader()->moveSection( 0, 3 );
@

the resulting situation is (d,a,b,c,e,f, g) so column 0 (fromIndex) is moved to 0+1 and column 3 (toIndex) is moved to column 0.
If I do:

@tableView->horizontalHeader()->moveSection( 3, 0 );@

the situation becomes (b,c,d,a,e,f,g) so column o (toIndex) is moved to column 3 (fromIndex) and old column 3 is shifted right (3+1).
The documentation from moveSection reports that

bq. Moves the section at visual index from to occupy visual index to.

But I'm doing wrong something, since the to and from index seems to exchange their roles.
Moreover, in my specific case, I've got that after a first move, all the columns at the right of the target column are shifted, so the indexes must be recomputed.
Any comment?

[fluca1978]

Having a look at the implementation of moveSection I understand my error: as the name states the method moves a whole section of columns, not a single column.
What I'm looking for is to re-order a set of columns so that (a,b,c,d) could become (d,a,b,c). One solution could be to set the SQL query, but this is not really portable (I cannot use advanced features like filtering when using qsqlrelationaltablemodel). Another solution could be to produce a view in the database and map the model to such view, but this will not allow me easily to perform inserts/updates (not all database support updatable views).
Any idea about how to specify the column order in a qsqltablemodel?

[nikpelgr]

Hi,
I don't know is the question is still valid, but I solved it with the following way:

let 'model' be my model (which is connected to My SQLite DB).
I create an 'int ididx' and I get the fieldIndex and store it in 'ididx'
@ int ididx = model->fieldIndex("id"); @

let 'theView' be my tableview (ui->theView->setModel(model) etc)
I create another int ididx1 and I get the visual Index from the tableview
@ int ididx1 = ui->theView->horizontalHeader()->visualIndex(ididx); @

and then move it to the desired position. (if I wanted the position to be 3 I would type 3 instead of 0 below.)

@if (ididx!=0) ui->theView->horizontalHeader()->moveSection(ididx1,0);@

I do this for 20 columns, from an SQLite DB which are mixed ( I explain later).
After that, I hide the first 5 columns like this.
@ ui->theView->hideColumn(ididx);@

All the "magic" is the if statement. You could easily get the same result:
@int ididx = model->fieldIndex("id");
if (ididx!=0) ui->theView->horizontalHeader()->moveSection(ididx,0);@

I have an SQLite db with a table named "data". First I designed my table, my Qt model, my Qt table view and got it all right and working. In a while, there was a need to alter my SQLite table and SQLite mixed the updated fields (upside down). That was very annoying. So I used the above method in order to make it somehow "independent" from the order of the fields in the table, because I am sure that I will alter the table again and again.

I didn't know how to make my own SELECT queries in QSQLRelationTableModel but I managed to do it with this way.

Cheers.

[nikpelgr]

Hi,
I don't know is the question is still valid, but I solved it with the following way:

let 'model' be my model (which is connected to My SQLite DB).
I create an 'int ididx' and I get the fieldIndex and store it in 'ididx'
@ int ididx = model->fieldIndex("id"); @

let 'theView' be my tableview (ui->theView->setModel(model) etc)
I create another int ididx1 and I get the visual Index from the tableview
@ int ididx1 = ui->theView->horizontalHeader()->visualIndex(ididx); @

and then move it to the desired position. (if I wanted the position to be 3 I would type 3 instead of 0 below.)

@if (ididx!=0) ui->theView->horizontalHeader()->moveSection(ididx1,0);@

I do this for 20 columns, from an SQLite DB which are mixed ( I explain later).
After that, I hide the first 5 columns like this.
@ ui->theView->hideColumn(ididx);@

All the "magic" is the if statement. You could easily get the same result:
@int ididx = model->fieldIndex("id");
if (ididx!=0) ui->theView->horizontalHeader()->moveSection(ididx,0);@

I have an SQLite db with a table named "data". First I designed my table, my Qt model, my Qt table view and got it all right and working. In a while, there was a need to alter my SQLite table and SQLite mixed the updated fields (upside down). That was very annoying. So I used the above method in order to make it somehow "independent" from the order of the fields in the table, because I am sure that I will alter the table again and again.

I didn't know how to make my own SELECT queries in QSQLRelationTableModel but I managed to do it with this way.

Cheers.

[nicktrandafil]

In situation with QSqlRelationalTableModel/QTableView I passed this problem this way:

persons
pk_person, fk_job, last_name, first_name

jobs
pk_job, name

We want to show the table "persons" in QSqlRalationalModel/QTableView and we want columns to be in this order: pk_person, first_name, last_name, fk_job.
Eventually we would like to see joined table columns: pk_person, first_name, last_name, jobs.name

void initModelAndView(  const QString &table
                      , QSqlRelationalTableModel *model
                      , QTableView *view
                      , const vector<QString> &cols
                      , const vector<QString> &trans
                      // tbl, id, name
                      , const list<tuple<size_t, QString, QString, QString>> &rels
                      , const list<size_t> &hiddens)
{
  Q_ASSERT(cols.size() == trans.size());

  model->setEditStrategy(QSqlTableModel::OnManualSubmit);
  model->setTable(table);

  // headers
  for (size_t i = 0; i < cols.size(); ++i) {
    model->setHeaderData(  model->fieldIndex(cols[i])
                         , Qt::Horizontal, trans[i], Qt::EditRole);
  }

  // actual order in DB
  unordered_map<QString, size_t> poss;
  for (auto col: cols) {
    poss[col] = model->fieldIndex(col);
  }

  for (auto rel: rels) {
    model->setRelation(  model->fieldIndex(cols[get<0>(rel)])
                       , QSqlRelation(get<1>(rel), get<2>(rel), get<3>(rel)));
  }

  if (!model->select()) {
    throw exception::DBError(model->lastError().text());
  }

  // reordering
  for (size_t i = 0; i < cols.size(); ++i) {
    auto old_pos = find_if(  poss.begin(), poss.end()
                           , [i](auto v) { return i == v.second; });
    Q_ASSERT(old_pos != poss.end());
    view->horizontalHeader()->swapSections(poss[cols[i]], old_pos->second);
    std::swap(poss[cols[i]], old_pos->second);
  }

  for (auto hid: hiddens) {
    view->setColumnHidden(model->fieldIndex(cols[hid]), true);
  }

  view->resizeColumnsToContents();
}
...

and somewhere
auto view = new QTableView();
auto model = new QSqlRelationalTableModel(view);
view->setModel(model);
view->setSelectionMode(QAbstractItemView::SingleSelection);
view->setSortingEnabled(true)

// we want columns to be in this order
vector<QString> cols;
cols.push_back("pk_person");
cols.push_back("first_name");
cols.push_back("last_name");
cols.push_back("fk_job");

vector<QString> trans;
trans.push_back(QObject::tr("Id");
trans.push_back(QObject::tr("First Name");
trans.push_back(QObject::tr("Last Name");
trans.push_back(QObject::tr("Job");

list<tuple<size_t, QString, QString, QString>> rels;
rels.push_back(make_tuple(3, "jobs", "pk_job", "name"));

list<size_t> hiddens;
hiddens.push_back(0);

initTableAndModel(  "persons", model, view 
                  , cols, trans, rels, hiddens);

I used c++11