PDA

View Full Version : QSqlRelationalTableModel & Left Join



mhbeyle
18th December 2009, 19:41
Hello.

completes the internal query as "inner join" so when we have a relation that has null values in the main table key the relation is not shown.

I have valued three ways to avoiding this limitation:

1 .- Fill null values with a special value that has an interrelation in the other table. Id zero value or something like that.
2.- Sub-classing QSqlRelationalTableModel.
3.- Direct modify qsqlrelationaltablemodel.cpp and qsqlrelationaltablemodel.h and add a function for select de join mode.

Is there any way simpler to do this in editable data models?

wysota
20th December 2009, 10:42
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.

Tanuki-no Torigava
20th December 2009, 11:57
Actually Wysota is right because foreign key in one table is a primary key in another. But there are some situations when you need to fill the tables and you have cross relations between 2 tables (not the best solution anyway). For that case you can solve it with sql statements. You event don't need any Qt for this.

mhbeyle
21st December 2009, 13:40
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.

I would not like to show nothing different from NULL values but I want these NULL values if they are in some record/row.
I am not in accordance with your affirmation. I can have a relation between two tables and I can have NULL values in a foreign key when certain records do not need to have this relation. What is the problem with this? Another thing is that I have a value that replaces the NULL (with zero, null string and so on...) and assign it when the user has not a choice but why am I going to do that if I have a LEFT JOIN that has this work for me?

A small example: I have a table of books that has a field for "main author" and another for "additional authors". I have a table with Authors that has a relation with the books table. "main author" and "additional authors" are foreign keys that point to Authors table. There are books that have "additional authors" and there are another that only have an author. Why must I fill a record for the first case? I want display the record and I want a NULL value in the field. QSqlRelationalTableModel does not allow it. It only displays the records with an INNER join select but What about OUTER join?.

Thank you very much and sorry for my use of the english language :(