QSqlRelationalTableModel get relation foreign key value

maurosanjo

I have a QSqlRelationalTableModel that has a relation defined that maps a foreign key to a display column.

for instance:

QSqlRelationalTableModel *myModel = new QSqlRelationalTableModel(this);
myModel->setTable("address");
myModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
myModel->setRelation(myModel->fieldIndex("id_city"), QSqlRelation("cities","id_city","name"));

I could not find any reasonable solution to just get the foreign key value when retrieving a record data.

When I insert records (using insertRecord), berfore submitting the changes to DB, the value returned by display column name "name" is the foreign key value of "id_city".

QSqlRecord record = myModel->record(row);
// Returns the FK value before submitting new records
QString val = record.value("name").toString();

After submitting them, the behaviour is returning the city name, and no access to the foreign key.

Imagine I do not want to go through relationalModel records looking for the city name, because i do not guarantee unique names for whatever reason.

This should be pretty straightforward as the whole Qt Framework, but I could not find it...

kshegunov

@maurosanjo
Hello,
Is the footnote on the relational model page what you're looking for. If I understand it correctly you can retrieve the fields you're interested in by using properly aliased name.

Kind regards.

maurosanjo

@kshegunov
Hello,

Unfortunatelly that is not case. If I get a QSqlRecord from the QSqlRelationalTableModel and iterate through the fields and show their names, it only outputs the table columns changing the foreign key column to the display column defined on the relation (in this example "name" in place of "id_city").

If I check the column count on the QSqlRelationalTableModel it gives the exact number of columns on the "main" table, and no aliased field from the related table.

I believe that note is talking about aliasing fields when the relations created have equal column names, which is not the case,

What I need is pretty simple, I create a QSqlRealtionalTableModel with one relation, and for each record i want to retrieve the foreing key value for the display column.

Regards

kshegunov

@maurosanjo

If I get a QSqlRecord from the QSqlRelationalTableModel and iterate through the fields and show their names, it only outputs the table columns changing the foreign key column to the display column defined on the relation (in this example "name" in place of "id_city").

I see, but what about retrieving the data directly from the model, not passing through QSqlRecord at all?

I believe that note is talking about aliasing fields when the relations created have equal column names, which is not the case,

Yes, but also it mentions (joined) table names are aliased. That's what I was referring to when I suggested the note.

What I need is pretty simple, I create a QSqlRealtionalTableModel with one relation, and for each record i want to retrieve the foreing key value for the display column.

That is the integer (presumably) that's declared as a foreign key, correct?

Kind regards.

maurosanjo

@kshegunov
Hello,

I also tried that, and looked at the select query from the model, and as i said the id column "id_city" is used only in the WHERE clause, in the select it is replaced by the display column "name".

Doing some tests I found a way to add the foreign key but this creates problems when adding new records...

Taking a look at the select query, Qt does not include " on the field name of the display column, allowing do to something like this:

myModel->setRelation(myModel->fieldIndex("id_city"), QSqlRelation("cities", "id", "name, relTblAl_2.id_city"));

After select the query shows the additional foreing key column, and you're able to manipulate it record by record.

Still investigating if it is possible to overcome the problems when inserting records. But this is not a proper solution, this info should be easily retrieved,

kshegunov

@maurosanjo
The only other thing I could think of is to use something like this:

int relationIndex = myModel->fieldIndex("id_city");
QVariant idCity = myModel->relationModel(relationIndex)->record(row).value("id_city");

But it might not work, I'm just speculating ...

Kind regards.

maurosanjo

@kshegunov

This won't work because the relational Model is just a QSqlTableModel pointing to the reference table, so it fetches all the records from that table not linking with the QSqlRelationalModel.

I did not succeed trying with QSqlRelationalModel. I had to extend QSqlTableModel reimplementing columnCount, data, flags and headerData, to add new columns and personalize the columns data when populated with the model.

For some reason I could not do that extending QSortFilterProxyModel. the columns where always empty.

Regards

kshegunov

@maurosanjo
Well, I'm all out of ideas, sorry.