Reference to non-existing key in QSqlRelationalTableModel
-
Hello everyone,
I am struggling since a long time to create the relatively simple use case. I am having the table of the workers set to a QSqlRelationalTableModel, where one of the fields would be the foreign key of the supervisor from the defined table relation.
Now some works will not have any supervisor. For now I was just creating the record "no_supervisor" in supervisor table and I was assigning it to it all the workers which don't need any key, otherwise the worker's record would not be shown in the QTableView, the existing foreign key has to be given. The problem is, that now in the supervisor QTableView I will always have this dummy record "no_supervisor" so to avoid it I was just hiding the row. Then I got into problem, that when I was sorting the supervisor QTableView and I was some additional supervisors the hidden dummy element could have been shown again, because some of the records move down by. You see my point.
So my question is, how may I just give the information such as NULL and still get the record shown in the QTableView?
-
Hello everyone,
I am struggling since a long time to create the relatively simple use case. I am having the table of the workers set to a QSqlRelationalTableModel, where one of the fields would be the foreign key of the supervisor from the defined table relation.
Now some works will not have any supervisor. For now I was just creating the record "no_supervisor" in supervisor table and I was assigning it to it all the workers which don't need any key, otherwise the worker's record would not be shown in the QTableView, the existing foreign key has to be given. The problem is, that now in the supervisor QTableView I will always have this dummy record "no_supervisor" so to avoid it I was just hiding the row. Then I got into problem, that when I was sorting the supervisor QTableView and I was some additional supervisors the hidden dummy element could have been shown again, because some of the records move down by. You see my point.
So my question is, how may I just give the information such as NULL and still get the record shown in the QTableView?
@Jendker
So have you tried assigning the parent value toNULLin the child row(s) and seeing what happens? From what I read, this ought to work, the child will not show a selected value from the parent. I don't know how the interface displays this, how easy it is via the combobox in the view to swap between selecting a parent and no parent and back again, you'll have to check that out. -
@Jendker
So have you tried assigning the parent value toNULLin the child row(s) and seeing what happens? From what I read, this ought to work, the child will not show a selected value from the parent. I don't know how the interface displays this, how easy it is via the combobox in the view to swap between selecting a parent and no parent and back again, you'll have to check that out. -
@JonB Thanks, I am trying it out. For now setting
NULLfor the foreign key does not allow the record to be shown in theQTableView, but maybe I can make it somehow work with different widgets, for example combobox as you proposed.@Jendker
[I proposedQCompleterrather thanQCombBox.] SettingNULLin the child key column for the parent (worker has no supervisor) prevents display of the whole worker row when viewing the list of workers? I presumed it would show the worker but have a "blank" where the parent should be. If not, it does not look likeQSqlRelationalTableModellikesNULL, it expects a genuine, existent parent for the child to be valid/displayed, which is not good for your situation.... -
@Jendker
[I proposedQCompleterrather thanQCombBox.] SettingNULLin the child key column for the parent (worker has no supervisor) prevents display of the whole worker row when viewing the list of workers? I presumed it would show the worker but have a "blank" where the parent should be. If not, it does not look likeQSqlRelationalTableModellikesNULL, it expects a genuine, existent parent for the child to be valid/displayed, which is not good for your situation....@JonB said in Reference to non-existing key in QSqlRelationalTableModel:
If not, it does not look like
QSqlRelationalTableModellikesNULL, it expects a genuine, existent parent for the child to be valid/displayed, which is not good for your situation....It seems, that it's exactly the case... I will just try to carry on with improving the hiding of the dummy row, which will stand for "none" in order to keep the things working with
QSqlRelationalTableModelI don't know, maybe some time I will just drop the QSqlTableModels altogether and work with the queries.
The solution can be also in the post by wysota on the old forum back in 2009:
"What would you like to show instead of null values in the rows that contain them? A NULL value in a field that is a foreign key can be considered a design flaw of the database. If you want to have null values as foreign key then don't pretend this is a relation and use QSqlTableModel() with a custom delegate that will treat the foreign key in a special manner - by replacing its values with ones from another table. "
That would be some work in rebuilding database scheme, but it could be the actual solution. I will try this one first.
-
@JonB said in Reference to non-existing key in QSqlRelationalTableModel:
If not, it does not look like
QSqlRelationalTableModellikesNULL, it expects a genuine, existent parent for the child to be valid/displayed, which is not good for your situation....It seems, that it's exactly the case... I will just try to carry on with improving the hiding of the dummy row, which will stand for "none" in order to keep the things working with
QSqlRelationalTableModelI don't know, maybe some time I will just drop the QSqlTableModels altogether and work with the queries.
The solution can be also in the post by wysota on the old forum back in 2009:
"What would you like to show instead of null values in the rows that contain them? A NULL value in a field that is a foreign key can be considered a design flaw of the database. If you want to have null values as foreign key then don't pretend this is a relation and use QSqlTableModel() with a custom delegate that will treat the foreign key in a special manner - by replacing its values with ones from another table. "
That would be some work in rebuilding database scheme, but it could be the actual solution. I will try this one first.
A NULL value in a field that is a foreign key can be considered a design flaw of the database.
When I started to answer your question, that was my initial position (except I wouldn't say "flaw"). When I have used FKs I was never allowed
NULL, and I thought that was actually required. However I Googled and plenty said many databases support it (e.g. MySQL) and it was regarded as "acceptable".I never did think
QSqlRelationalTableModeloffered much overQSqlTableModel. It's really just a glorified combobox (model being the FK table) interface in the view. Exactly as 2009 says, a custom delegate could do the job. Just a shame that Qt's doesn't offerNULLhandling option for what you want.
