CODEWITHSUNDEEP
c++qtsqlitepyqtqt5
user1829358
user1829358

Reputation: 1091

Qt: QSqlRelationalTableModel reference to non-existing foreign key

Let's say that I have a table 'person' with the following columns: id, name, manager_id. Where 'id' is the primary key and 'manager_id' is the foreign key. Since some people might now have a manager this value is allowed to be NULL. However, this seems to create problems with Qt's QSqlRelationalTableModel.

Here is a minimalistic example which replicates the problem: window.cpp:

Window::Window(QWidget *parent) : QWidget(parent)
{
// setup database
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(":memory:");
db.open();

// create database
QSqlQuery query;
query.exec("create table 'person' (id INTEGER NOT NULL PRIMARY KEY, "
           "name varchar(20), manager_id INTEGER NULL)");
query.exec("insert into person values(1, 'Alice', 2)");
query.exec("insert into person values(2, 'Bob', -1)"); // invalid manager_id
//query.exec("insert into person values(2, 'Bob', 1)"); // valid example

// setup model
model = new QSqlRelationalTableModel(this);
model->setTable("person");
model->setEditStrategy(QSqlTableModel::OnRowChange);

// setup foreign key
int typeIndex = model->fieldIndex("manager_id");
model->setRelation(typeIndex, QSqlRelation("person", "id", "name"));
model->select();

// setup UI
auto nameLabel = new QLabel(tr("Name:")); auto nameEdit = new QLineEdit();
auto typeLabel = new QLabel(tr("Manager:")); auto typeComboBox = new QComboBox();
auto nextButton = new QPushButton(tr("Next"));
auto previousButton = new QPushButton(tr("Previous"));
QSqlTableModel *relModel = model->relationModel(typeIndex);
typeComboBox->setModel(relModel);
typeComboBox->setModelColumn(relModel->fieldIndex("name"));
QGridLayout *layout = new QGridLayout();
layout->addWidget(nameLabel, 0, 0, 1, 1);
layout->addWidget(nameEdit, 0, 1, 1, 1);
layout->addWidget(previousButton, 0, 2, 1, 1);
layout->addWidget(nextButton, 1, 2, 1, 1);
layout->addWidget(typeLabel, 2, 0, 1, 1);
layout->addWidget(typeComboBox, 2, 1, 1, 1);
setLayout(layout);

// setup mapper
mapper = new QDataWidgetMapper(this);
mapper->setModel(model);
mapper->setItemDelegate(new QSqlRelationalDelegate(this));
mapper->addMapping(nameEdit, model->fieldIndex("name"));
mapper->addMapping(typeComboBox, typeIndex);
mapper->toFirst();

connect(previousButton, SIGNAL(clicked()), mapper, SLOT(toPrevious()));
connect(nextButton, SIGNAL(clicked()), mapper, SLOT(toNext()));
}

window.h:

#include <QWidget>
class QDataWidgetMapper;
class QSqlRelationalTableModel;
class Window : public QWidget
{
    Q_OBJECT
public:
    Window(QWidget *parent = 0);
private slots:
private:
    QDataWidgetMapper *mapper;
    QSqlRelationalTableModel *model;
};

The problem is that the second record (i.e., "Bob") will not be displayed because his manager's id is invalid (-1).

The documentation of the QSqlRelationalTableModel states: "If a relational table contains keys that refer to non-existent rows in the referenced table, the rows containing the invalid keys will not be exposed through the model. The user or the database is responsible for keeping referential integrity."

However, is there no way around this? It seems to me that this is a common problem. Thanks.

Upvotes: 1

Views: 652

Answers (1)

ekhumoro
ekhumoro

Reputation: 120678

According to your description of the data, "no manager" is an allowable value, so it makes no sense to refer to it as invalid.

The real problem in your example is that the name column has several overlapping puposes. It really should be a separate names table, which can then have a row with an empty string to indicate "no manager".

The person table would then just contain ids from the names table.

Upvotes: 1

Related Questions