Get foreign key value from QSqlRelationalTable

[scrand]

I'm using a QSqlRelationalTableModel to do some nice display things like populate a QComboBox with human-readable values instead of the foreign key. But then I sometimes need the actual value of the foreign key so I can use it to grab a record from another table. The problem is, QSqlRelationalTableModel joins the human-readable display, and drops the foreign key. So that column is now populated by a human-readable value that is pretty useless for the database. I'd like for it to do something like the equivalent of:

SELECT
     a.id AS primary_key, 
     b.readable AS human_readable,
     a.fk AS foreign_key,
     FROM table_a a 
     JOIN table_b b ON a.fk = b.id 
     ORDER BY b.readable ASC;

(I'm not an SQL guy, so excuse me if there are errors in there.)

I've also tried something like

setRelation("b", "key", "b.readable as human_readable, a.fk as foreign_key");
bool result = select();

But the query fails, and when I look at lastQuery(), I can see it's formed wrong. It looks like Qt is trying to form a correct query with two columns, but it doesn't work right.

I don't even want to display the foreign key value. I just want it to be there so that I can grab the right record from the other table. I get that I could use the query above in a QSqlQuery, but QSqlQuery doesn't auto-update like QSqlRelationalTableModel. I also get that I could just load the whole table into a QTableView and edit it there with QSqlRelationalDelegate. But I'm trying to make a display that works on a single record. It would be nice if I could use a QDataWidgetMapper to just map the QComboBox to the foreign key field instead of manually updating when it changes. I just can't find a way to make QSqlRelationalTableModel keep the foreign key value. It's almost perfect!

I've Googled a bunch, and can't find an elegant solution that doesn't involve me basically reinventing QSqlRelationalTableModel. Am I missing something?

[JonB]

@scrand
(As I understand it) QSqlRelationalTableModel does not do any JOIN at the database. It retrieves two tables, the main table and the fk table. That is how it can offer, say, a combobox with all the possible values for the fk field. The main table still has its underlying, non-mapped value in it; else you wouldn't be able to update it.

First, make sure that mainTable->data(index, Qt::DisplayRole) for the foreign column does not return the mapped value? (I don't know whether it does.) I think that's what you're saying you want? I'd expect mainTable->data(index, Qt::EditRole) for sure to return the unmapped value.

If that does not give the mapped value you want, have a look at the source code in https://code.woboq.org/qt5/qtbase/src/sql/models/qsqlrelationaldelegate.h.html. Note how it has a line like

QSqlTableModel *childModel = sqlModel ? sqlModel->relationModel(index.column()) : nullptr;

I would have thought that is the approach needed to locate the value in the fk table, via its data() method?

But I'm trying to make a display that works on a single record. It would be nice if I could use a QDataWidgetMapper

All I know is there are quite a few Google hits for QDataWidgetMapper QSqlRelationalTableModel. Did you look through those (they are often quite old, but the principle should still hold) to see if one of them is trying to do what you want?

[scrand]

@JonB, voila!

I think that Qt::EditRole vs Qt::DisplayRole may be the secret sauce that I am looking for. I had my table class returning record(row), which I loaded into a data record object inherited from QSqlRecord. Then I would call value("fk_field_name").toInt() on the data record object. But since "fk_field_name" was now mapped to the display value, it just returned a garbage negative integer based on the display string. If data(full_index, Qt::EditRole) gives me the actual foreign key value, it will be pretty easy to grab it and just make int foreign_key a member of the data record class.

As for the other, yes, I did look at some examples, and even got it to work in a way. The ComboBox works fine as a combo box for that one field, but then I would need the foreign key to look up the child record do some other stuff, and I couldn't get to it from my QSqlRelationalTableModel. So even though I could use it to control the parent record, I couldn't get to the child record when I needed it. I figured the fk value had to be in there somewhere, because the delegate had to update it. Now I think you've given me the key.

I will play with it some and update the results.

Thank you!

[scrand]

Well, I tried data() with a Qt::EditRole, but I just kept getting 0, which at least is an integer, but not the one I wanted. I even inspected my QModelIndex in the debugger to make sure I had the right row, column, and model. So this sounds like something that should work, but if it does work in theory, I'm doing it wrong.

However, I came up with a solution. A bit of a hackaround, but it seems to work. All of my tables have their own class that inherits from QSqlRelationalTableModel. I added a QHash<int, int> foreignKeyHash to the class that needs the FK. Then I do this in the constructor:

    int temprows;
    QSqlRecord temprecord;
    select(); // Select before I set up any relations

    temprows = rowCount();
    for(int i=0; i < temprows; i++){
        temprecord = getRow(i);
        foreignKeyHash[temprecord.value("id").toInt()] = temprecord.value("foreign_key").toInt();
    }
            
   SetupRelations(); // Setup my relationships
   select(); // This select gives me the relational model for display

Now I have a nice relational model for display purposes, but I stored all of the foreign key values in the hash before I populated it with relational values.

My data record class that inherits from QSqlRecord has a member value int foreign_key, which I set whenever my table model fetches a row from the table. My getForeignKey() method now just returns foreign_key instead of value("foreign_key").toInt(), which gives me garbage.

Not perfect, and not the most elegant solution, but it seems to work.