PDA

View Full Version : Method to read foreign key values from QSqlRelationalTableModel ?



kawoN
3rd March 2010, 09:02
Hi,

My problem is: SqlRecord get by record() function from QSqlRelationalTableModel always (?) returns related value for column with foreign key - not original key value. Is there any method to obtain key value from record?

For instance:

Table 1:
country_id country_name continent_id
1 France 1
2 Germany 1
3 USA 2

Table 2:
continent_id continent_name
1 Europe
2 North America

We construct QSqlRelationalTableModel where continent_id in Table_1 is replaced by continent_name from Table_2:

QSqlRelationalTableModel *model = new QSqlRelationalTableModel;
model->setTable("Table_1");
model->setRelation(model->fieldIndex("continent_id"), QSqlRelation("Table_2", "continent_id", "continent_name"));

Than when we want to read data (first row i.e.) from model we use QSqlRecord:
....
QSqlRecord record = model->record(0);

Than - if we want to get continent_id field (using value function):

QString continent_id = record.value("continent_id").toString();

we will get empty string. Only continent_name is available.
My goal is to get continent_id, and I don't know how to do it.
If you put record to qDebug() You will see that record doesn't contain continent_id field.

Any suggestions welcome
kawoN

Al_
2nd April 2011, 15:51
I have the same issue. First, I confirmed that QSqlRelationalTableModel::data() returns the related string and not the key, irrespective whether role = Qt::DisplayRole or Qt::EditRole .

tableModel->select();
qDebug() << "*** Qt::DisplayRole ***";
for (int c(0); c < tableModel->columnCount(QModelIndex()); ++c) qDebug() << tableModel->data(tableModel->index(0, c, QModelIndex()), Qt::DisplayRole);
qDebug() << "*** Qt::EditRole ***";
for (int c(0); c < tableModel->columnCount(QModelIndex()); ++c) qDebug() << tableModel->data(tableModel->index(0, c, QModelIndex()), Qt::EditRole);
qDebug() << "*** record ***";
qDebug() << tableModel->record(0);
I currently consider the following workaround:
retrieve the related table
find the index in the related table that matches the string returned by the main table
retrieve the key from the same row of the related table.
The following code demonstrates my approach (in my case, tableModel has a foreign key in first column = column 0; the related table has its primary key in column 0 and the string in second column = column 1)

QSqlTableModel* relatedTable = tableModel->relationModel(0);
QModelIndex idx = relatedTable->match(relatedTable->index(0, 1),Qt::DisplayRole, tableModel->data(tableModel->index(0, 0, QModelIndex()), Qt::DisplayRole)).at(0);
qDebug() << relatedTable->data(relatedTable->index(idx.row(), 0, QModelIndex()), Qt::DisplayRole);
It is not elegant. And it works only if one knows which columns have relations (i.e., qobject_casts are needed in a library where the function gets a QAbstractItemModel* that may or may not be a QSqlRelationalTableModel)

I appreciate if anybody has a cleaner solution. A role flag such as Qt::DisplayRoleRaw would be nice.

Al_

mannermoe
9th October 2011, 20:07
I made my own model as a subclass of QSqlRelationalTableModel and reimplemented QSqlQueryModel::record(int row) like this.

mymodel.h

class mymodel : public QSqlRelationalTableModel
{
Q_OBJECT
public:
virtual QVariant data(const QModelIndex &item, int role = Qt::DisplayRole) const;
virtual QSqlRecord record(int row) const;
};

mymodel.cpp

.
.
.
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);
}

So if I call mymodel::record(int row) i get a QSqlRecord as it is in the database (from QSqlTableModel) without any relation.
I can fetch all entries including foreign keys by using QSqlRecord::value("...").

mannermoe

Al_
11th October 2011, 18:30
Agreed. This is certainly an option. Along the same thinking I could also implement my original suggestion of an additional flag, such as Qt::RawDisplayData.

Looking at the source code for QSqlRelationalTableModel, I realize why an indirection (i.e., getting related value first, then look up foreign key) is needed: behind the scene QSqlrelationalTableModel builds an SQL statement with joins and omitting the raw foreign keys.

7ymekk
12th November 2013, 15:00
Old thread but I was looking for solution. Here is what You should do:

model->setRelation(model->fieldIndex("continent_id"), QSqlRelation("Table_2", "continent_id", "continent_name, continent_id as continent_id"));

or something like that. I have table Orders with contractor_id and Contractors with id so I had to use:

setRelation(this->fieldIndex("contractor_id"), QSqlRelation("Contractors", "id", "name, contractor_id as contractor_id"));

'displayColumn' in Qt documentation is misleading. It should be called displayColumns.

Al_
16th November 2013, 13:38
An unexpected solution! Thanks!

Providing more than one column name as third parameter of the QSqlRelation constructor is undocumented (you call it 'misleading'), thus the internal handling might change in the future. But at present your solution is very nice.

francoisk
12th October 2015, 09:59
Sorry to bump this, but I've found another solution and I thought that someone may find it helpful :

I've built a class inheriting from QSqlTableModel rather than QSqlRelationalTableModel and chose to override the selectStatement and orderByClause methods.
Everything works well except that you won't be able to use setSort() if you want to order by a joined column. But since it allows you to provide your own SELECT query, it's not a real problem. setFilter and all other methods work as expected, which is very convenient :)



QString MyClass::QUERY = QString("SELECT t1.col1, t1.col2, t1.col3, t2.col1, t2.col2 FROM table1 t1 JOIN table2 t2 ON t1.col2 = t2.col1");
QString MyClass::ORDER_BY = QString("ORDER BY t2.col2 ASC");




QString MyClass::selectStatement() const
{
QString stmt = MyClass::QUERY;

// Sets filter (WHERE clause) :
if(!this->filter().isEmpty())
stmt.append(QLatin1String(" "))
.append(QLatin1String("WHERE "))
.append(this->filter());

// Sets sort (ORDER BY clause) :
stmt.append(QLatin1String(" "))
.append(this->orderByClause());

return stmt;
}

QString MyClass::orderByClause() const
{
return MyClass::ORDER_BY;
}