Get Value of Foreign Key in QSqlRelationalTableModel

[davethomaspilot]

I'm using a QSqlRelationalTableModel. It's handy, since field values from tables referenced by the foreign keys are displayed automatically.

But, I need to use the foreign key to get data from other tables to set up an editor widget. I don't see a method for that in QSqlRelationalTableModel.

Am I missing it?

Thanks,

[davidovv]

QSqlRelationalDelegate has createEditor method, if that is what you are asking

[ChrisW67]

If I understand the question correctly, you want the actual value in the joined column, not the display value obtained from the related table. There does not seem to be a direct way to obtain this. Bot the DisplayRole and EditRole return the mapped value. The QSqlRelationalDelegate actually looks up the display text in the relationModel() to find the id for its combo box drop down (this works only for simple lookup tables though). You could retrieve the primary key of the main table from the model and use that in a joined query when looking up the other tables.

[davethomaspilot]

ChrisW67, yet that's what I need. The actual foreign key value, not the display value obtained from the related table.

But, the foreign key can't by found with just the display text, since there's not a one-one relationship. Many unique foreign keys will have the same display text, so the display text is insufficient to determine the actual foreign key value. Maybe that's what you meant by "this works only for simple lookup tables though".

I've found threads for other work-arounds, but they all seem to require a one to one relationship between foreign keys and display text.

I thought I'd try adding columns to the select statement for the raw keys, but I now realize the QSqlRelationalTableModel does not support the setQuery method. I guess you get all the columns for the table, then remove those you don't want.

So, I think this means I shouldn't use the QSqlRelationalTable Model.

There's a SQL view that's been created that has what I need, but the columns need to be editable. So, I think that means a SQL view can't be used as the model for any QSqlTable model if you need editing capability.

I'm thinking my best option is to use a QSqlTableModel and do the mapping for the display text in my own code. I'll look for an example of how that's done.

Thanks,

Dave Thomas



Instead, use the QSqlRelationalModel and do the mapping

Added after 55 minutes:

I tried the work-around that mannermoe came up with in this thread:

[/URL]

[url]http://www.qtcentre.org/threads/28602-Method-to-read-foreign-key-values-from-QSqlRelationalTableModel (http://www.qtcentre.org/threads/28602-Method-to-read-foreign-key-values-from-QSqlRelationalTableModel)

I thought maybe "record" was a virtual method of some base class that was being re-implemented or specific to the new derived class. I was afraid changing it to return a record with the raw foreign keys instead of the data from the referenced table might break things. But, so far, it seems to be working without any undesired side effects.

Dave Thomas

[davethomaspilot]

Unfortunately, it DOES break other things.

At least one field in the record is now always zero, though most (all but one?) look god.

Added after 1 28 minutes:

I can get it to work if I calll QSqlRelationalTableModel::record instead of the derived class version for the field that's always zero. But, I don't understand why the derived class's version of record always always zero for one of the columns.

Here's the record method from the referenced thread (mannermore):

QSqlRecord mymodel::record(int row) const
{
QSqlTableModel mod;
QSqlRecord rec = QSqlQueryModel::record(row);

mod.setTable(tableName());
mod.select();
mod.setFilter(QString("idxxx = %2").arg(rec.value("idxxx").toInt()));

return mod.record(0);
}

I'm using that without the setFilter since I don't understand why he's doing that.

So, when I need the foreign key values, I use the "mymod::record", but for the other columns I use the QSqlRelationalTableModel version. It works, but I don't understand why I don't get the correct value for at least one coumn (that column doesn't have a relation).

Perhaps the record returned from the QSqlTableModel allocated on the stack doesn't get copied?

Any insight or suggestion for an alternative work-around would be appreciated!

Thanks,

Dave Thomas

[davethomaspilot]

The code fragment for mymodel::record might work in a non-general way, but it's not working for me--lots of issues.

Serves me right for non-understanding it completely before trying to use it.

So, I'm thinking a QSqlRelationTableModel isn't useful, if the foreign key values are needed for more than displaying associated text. That doesn't seem like an unusual situation, since typically a key is used to associate records from several tables,not just two.

I think the way to go is to use a QSqlTableModel and implement the displayRole for the foreign key columns. That way, the key values are available for retrieving data from several tables.

Dave Thomas