Unable to perform setValue on QSqlRecord when model has relations

[wysota]

What does this print?

for(int i=0;i<record.count();++i){
  qDebug() << "Field " << i << "is called" << record.fieldName(i);
}

for(int i=0;i<record.count();++i){
  qDebug() << "Field " << i << "is called" << record.fieldName(i);
}

To copy to clipboard, switch view to plain text mode 

[stevebakh]

What does this print?

for(int i=0;i<record.count();++i){
  qDebug() << "Field " << i << "is called" << record.fieldName(i);
}

for(int i=0;i<record.count();++i){
  qDebug() << "Field " << i << "is called" << record.fieldName(i);
}

To copy to clipboard, switch view to plain text mode 

Oooh, this gives interesting results. It turns out that the field name changes when adding the relation, it's appended with "_2", so "foo_id_2".

Here's some code as an example:

QSqlRelationalTableModel *model = new QSqlRelationalTableModel(this);
model->setTable("foo");
model->setRelation(model->fieldIndex("bar_id"), QSqlRelation("bar", "id", "somecolumn"));
model->select();
 
QSqlRecord row = model->record();
// the following fails to set the value
row.setValue("bar_id", 123);
 
// because the following returns -1
qDebug() << row.indexOf("bar_id");
 
for (int i = 0; i < row.count(); i++) {
    qDebug() << "field: " << i << " is called: " << row.fieldName(i);
}
 
// the above outputs the following:
// field: 0 is called: "blah"
// field: 1 is called: "blah1"
// field: 2 is called: "blah2"
// field: 3 is called: "bar_id_2" <<<< The _2 is appended only when a setRelation() is called on the model.

QSqlRelationalTableModel *model = new QSqlRelationalTableModel(this);
model->setTable("foo");
model->setRelation(model->fieldIndex("bar_id"), QSqlRelation("bar", "id", "somecolumn"));
model->select();

QSqlRecord row = model->record();
// the following fails to set the value
row.setValue("bar_id", 123);

// because the following returns -1
qDebug() << row.indexOf("bar_id");

for (int i = 0; i < row.count(); i++) {
    qDebug() << "field: " << i << " is called: " << row.fieldName(i);
}

// the above outputs the following:
// field: 0 is called: "blah"
// field: 1 is called: "blah1"
// field: 2 is called: "blah2"
// field: 3 is called: "bar_id_2" <<<< The _2 is appended only when a setRelation() is called on the model.

To copy to clipboard, switch view to plain text mode 

So, doesn't this happen if you try my example code? Is this standard / normal behaviour?

[graciano]

for(int i=0;i<record.count();++i){
qDebug() << "Field " << i << "is called" << record.fieldName(i);
}

I tested this code and, for the foreign key field, i get: REFERENCEDTABLENAME_REFERENCEDFIELDNAME.
Nothing strange here!?

In you exemple the filed name show be "bar_somecolumn".

[wysota]

Anyway if you referenced the column by its index and not by its name, you wouldn't have even noticed there was a name change

[stevebakh]

Anyway if you referenced the column by its index and not by its name, you wouldn't have even noticed there was a name change

That's very true, however, I figured it would be bad practice to just use magic numbers to refer to arbitrary columns. What if the table structure changes?

I could define some enum values that represent each column and use those, but it's the same problem. If the table structure changes, the app breaks and needs recompiling. Hmmm, I just thought about it, I could/should probably use the model instead of the record to find the column number. record.setValue(model.fieldIndex("foo_id"), 123); Le sigh...

Thanks guys! Lesson learnt, knowledge earnt.

[wysota]

That's very true, however, I figured it would be bad practice to just use magic numbers to refer to arbitrary columns. What if the table structure changes?

I don't think it would change behind your back. If it did, the change could as well be that a column is removed at all. So we can assume that any change like that would only require you to renumber some enum and rebuild the app.