Editable QSqlRelationalTableModel - How to apply filter on Delegate in TableView

[daniel.ste]

Hello,

I have a problem that I'm not sure how to solve:

I use a local file based SQLite DB in my Qt Application. I have 3 static relational tables that are created like this (simplified):

QSqlQuery createProjekteTable("CREATE TABLE IF NOT EXISTS projekte (id INTEGER PRIMARY KEY, name TEXT)");

QSqlQuery createProjekteTable("CREATE TABLE IF NOT EXISTS projekte (id INTEGER PRIMARY KEY, name TEXT)");

To copy to clipboard, switch view to plain text mode 

QSqlQuery createSubProjekteTable("CREATE TABLE IF NOT EXISTS subProjekte (id INTEGER PRIMARY KEY, name TEXT, p_id INTEGER)");

QSqlQuery createSubProjekteTable("CREATE TABLE IF NOT EXISTS subProjekte (id INTEGER PRIMARY KEY, name TEXT, p_id INTEGER)");

To copy to clipboard, switch view to plain text mode 

QSqlQuery createTasksTable("CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY, name TEXT)");

QSqlQuery createTasksTable("CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY, name TEXT)");

To copy to clipboard, switch view to plain text mode 

Important: The table "subProjekte" has a foreign key " p_id" that is related to the "id" primary key of the table "projekte". With other words every subProject belongs to a Project.

Then I created a editable table that has relations to the above tables:

QSqlQuery createControllingTable("CREATE TABLE IF NOT EXISTS controlling (id INTEGER PRIMARY KEY, projekt_id INTEGER, sub_id INTEGER, task_id INTEGER, minuten INTEGER, date DATE, text TEXT, submitted BOOLEAN)");

QSqlQuery createControllingTable("CREATE TABLE IF NOT EXISTS controlling (id INTEGER PRIMARY KEY, projekt_id INTEGER, sub_id INTEGER, task_id INTEGER, minuten INTEGER, date DATE, text TEXT, submitted BOOLEAN)");

To copy to clipboard, switch view to plain text mode 

I want to be able to edit this table using a QTableView with delegates. Therefore I use a QSqlRelationalTableModel and define the relations like this:

// join name of subProjekt in controlling at sub_id
 
    subProjektIndex = relControllingModel->fieldIndex("sub_id");
    relControllingModel->setRelation(subProjektIndex, QSqlRelation("subProjekte", "id", "name"));
 
    // join name of projekt in controlling at projekt_id
 
    projektIndex = relControllingModel->fieldIndex("projekt_id");
    relControllingModel->setRelation(projektIndex, QSqlRelation("projekte", "id", "name"));
 
    // join name of task in controlling at task_id
 
    taskIndex = relControllingModel->fieldIndex("task_id");
    relControllingModel->setRelation(taskIndex, QSqlRelation("tasks", "id", "name"));
 
    relControllingModel->select();

// join name of subProjekt in controlling at sub_id

    subProjektIndex = relControllingModel->fieldIndex("sub_id");
    relControllingModel->setRelation(subProjektIndex, QSqlRelation("subProjekte", "id", "name"));

    // join name of projekt in controlling at projekt_id

    projektIndex = relControllingModel->fieldIndex("projekt_id");
    relControllingModel->setRelation(projektIndex, QSqlRelation("projekte", "id", "name"));

    // join name of task in controlling at task_id

    taskIndex = relControllingModel->fieldIndex("task_id");
    relControllingModel->setRelation(taskIndex, QSqlRelation("tasks", "id", "name"));

    relControllingModel->select();

To copy to clipboard, switch view to plain text mode 

Then in my MainWindow i have the table view that shall be used as an editor for the controlling table:

ui->tableView->setModel(model);
    ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));

ui->tableView->setModel(model);
    ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));

To copy to clipboard, switch view to plain text mode 

This works great. I can double click the Projekt / Sub Projekt / Task fields in my View and it shows a dropdown with the records from the foreign tables.

My Problem is that the Dropdown for SubProjekt shows ALL records from the table "subProjekte". But as I said above, theres an additional relation: The dropdown should only show records from subProjekte WHERE subProjekte.projekt_id = projekte.id (for the project that is currently selected in the dropdown delegate).

How do i solve this? I need to apply a filter on the subProjekte dropdown delegate. Do i solve this problem on the relational model? Or on the view? Or can i kind of specify another relation for the controlling table?

Any hints? Thank you very much in advance!