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->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!
Bookmarks