Method to read foreign key values from QSqlRelationalTableModel ?

[Al_]

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]

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_]

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]

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::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");

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode