QSqlRelationalTableModel and setFilter

[wolfgang.hammes]

Hello,

Is it possible to set a filter for a QSqlRelationalTableModel?

When I try

    model = new QSqlRelationalTableModel(this);
    model->setTable(QStringLiteral("receiver"));
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    addressIndex = model->fieldIndex(QStringLiteral("address_id"));
    model->setRelation(addressIndex, QSqlRelation(QStringLiteral("address"),
                                                  QStringLiteral("id"),
                                                  QStringLiteral("address")));
    QString idString = QString::number(id);
    QString filter = QString(QStringLiteral("id = %1")).arg(idString);
    model->setFilter(filter);
    model->select();

I get zero rows. Trying 'the same' with a QSqlTableModel

    model = new QSqlTableModel(this);
    model->setTable(QStringLiteral("receiver"));
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    //addressIndex = model->fieldIndex(QStringLiteral("address_id"));
    //model->setRelation(addressIndex, QSqlRelation(QStringLiteral("address"),
    //                                              QStringLiteral("id"),
    //                                              QStringLiteral("address")));
    QString idString = QString::number(id);
    QString filter = QString(QStringLiteral("id = %1")).arg(idString);
    model->setFilter(filter);
    model->select();

I am working on it for hours but find no solution. Maybe I have to use another order for the relational model?

[JonB]

@wolfgang-hammes
This gets complicated! First of all, do you have a column named id in both tables? Where does your (unused) address_id come into it?

You may want to read through very old https://forum.qt.io/topic/2213/qsqlrelationaltablemodel-and-setfilter-difficulties-possible-bug, and also the description about "aliased" columns, in https://doc.qt.io/qt-6/qsqlrelationaltablemodel.html#details.

Start by printing out lastQuery() (after SELECT has been issued) so we can understand what query it is generating.

[wolfgang.hammes]

@JonB

Thank you very much. The last query was

 "SELECT receiver.`id`,relTblAl_1.address,receiver.`name`,receiver.`name_ex`,receiver.`street`,receiver.`zip_code`,receiver.`city` FROM receiver,address relTblAl_1 WHERE (receiver.`address_id`=relTblAl_1.id) AND (id = 1)" 

and

QString filter = QString(QStringLiteral("receiver.`id` = %1")).arg(idString);

is getting the right row.

[JonB]

@wolfgang-hammes
I believe that means you are working now (right?). See how useful lastQuery() is, especially with QSqlRelationalTableModel to understand the JOIN and alias names?

[wolfgang.hammes]

@JonB

Oh yes. It can really help.