Solved Get foreign key value from QSqlRelationalTable
-
I'm using a
QSqlRelationalTableModel
to do some nice display things like populate aQComboBox
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
, butQSqlQuery
doesn't auto-update likeQSqlRelationalTableModel
. I also get that I could just load the whole table into aQTableView
and edit it there withQSqlRelationalDelegate
. But I'm trying to make a display that works on a single record. It would be nice if I could use aQDataWidgetMapper
to just map theQComboBox
to the foreign key field instead of manually updating when it changes. I just can't find a way to makeQSqlRelationalTableModel
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? -
@scrand
(As I understand it)QSqlRelationalTableModel
does not do anyJOIN
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 expectmainTable->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? -
@JonB, voila!
I think that
Qt::EditRole
vsQt::DisplayRole
may be the secret sauce that I am looking for. I had my table class returningrecord(row)
, which I loaded into a data record object inherited fromQSqlRecord
. Then I would callvalue("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. Ifdata(full_index, Qt::EditRole)
gives me the actual foreign key value, it will be pretty easy to grab it and just makeint 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!
-
Well, I tried
data()
with aQt::EditRole
, but I just kept getting0
, which at least is an integer, but not the one I wanted. I even inspected myQModelIndex
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 aQHash<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 valueint foreign_key
, which I set whenever my table model fetches a row from the table. MygetForeignKey()
method now just returnsforeign_key
instead ofvalue("foreign_key").toInt()
, which gives me garbage.Not perfect, and not the most elegant solution, but it seems to work.