QSqlRelationalTableModel + QSqlRelation Failing when some fields are null

[kreyszig]

Hi all

I'm using a QSQlRelationalTableModel with QSqlRelation as per the 4.7 documentation.
However if just one of the rows contains data in a column referred to by a QSqlRelation that doesn't map to a valid foreign key (e.g NULL), the model stops working - at least the Tableview displaying it shows no data.
For example:

Tables:
 
CREATE TABLE MaterialProperty (
  ID    text UNIQUE PRIMARY KEY NOT NULL,
  ParameterID    text,
  ScalarValue    real,
  CurveID        text,
  StringValue    text,
  UnitID         text,
  MaterialID     text,
  FieldID        text,
  VersionNumber  integer,
  VersionDate    datetime,
  /* Foreign keys */
  FOREIGN KEY (CurveID)
    REFERENCES SeriesIndex(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION, 
  FOREIGN KEY (UnitID)
    REFERENCES Unit(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION, 
  FOREIGN KEY (MaterialID)
    REFERENCES MaterialIndex(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION, 
  FOREIGN KEY (ParameterID)
    REFERENCES Parameter(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  FOREIGN KEY (FieldID)
    REFERENCES FieldIndex(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION 
);
 
CREATE TABLE SeriesIndex (
  ID    text UNIQUE PRIMARY KEY NOT NULL,
  Name 		 varchar(50),
  Title          varchar(50),
  Dims		 integer default 2,
  Xlabel         varchar(50) default "X",
  Ylabel         varchar(50) default "Y",
  Zlabel         varchar(50) default "Z",
  VersionNumber  integer,
  VersionDate    datetime
);
 
QSqlRelationalTableModel *model = db->get_db_model();
model->setTable("materialproperty");
QSqlRelation r("seriesindex", "id", "name");
if(r.isValid()) // this passes
  model->setRelation(3, r);

Tables:

CREATE TABLE MaterialProperty (
  ID    text UNIQUE PRIMARY KEY NOT NULL,
  ParameterID    text,
  ScalarValue    real,
  CurveID        text,
  StringValue    text,
  UnitID         text,
  MaterialID     text,
  FieldID        text,
  VersionNumber  integer,
  VersionDate    datetime,
  /* Foreign keys */
  FOREIGN KEY (CurveID)
    REFERENCES SeriesIndex(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION, 
  FOREIGN KEY (UnitID)
    REFERENCES Unit(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION, 
  FOREIGN KEY (MaterialID)
    REFERENCES MaterialIndex(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION, 
  FOREIGN KEY (ParameterID)
    REFERENCES Parameter(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  FOREIGN KEY (FieldID)
    REFERENCES FieldIndex(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION 
);

CREATE TABLE SeriesIndex (
  ID    text UNIQUE PRIMARY KEY NOT NULL,
  Name 		 varchar(50),
  Title          varchar(50),
  Dims		 integer default 2,
  Xlabel         varchar(50) default "X",
  Ylabel         varchar(50) default "Y",
  Zlabel         varchar(50) default "Z",
  VersionNumber  integer,
  VersionDate    datetime
);

QSqlRelationalTableModel *model = db->get_db_model();
model->setTable("materialproperty");
QSqlRelation r("seriesindex", "id", "name");
if(r.isValid()) // this passes
  model->setRelation(3, r);

To copy to clipboard, switch view to plain text mode 

If a row in materialproperty has no curveID, then no data is presented in the view at all. If I don't set up the relation, all appears as expected. The same if any other foreign key value is NULL.
I have a number of optional parameters such as curveID that can end up null.

Is there a way I can stop this behaviour? I don't see why a failure to map a NULL value to a foreign key should break the whole view/model (i'm not sure which is broken exactly, I presume it's the model). FWIW I am using an Sqlite database.

I would prefer not to have to fill these columns with data (i.e some foreign key to represent null) simply to get Qt to work with them.

Your help most appreciated - I've asked this over at stack overflow but haven't had a single response.

before you ask I am using UUID's as keys to allow data migration between databases, hence the text primary key values.

[davethomaspilot]

I have the same issue and would be interested in a work-around.

[lukeQt]

I know this thread has been dead awhile. Is there a solution to this? I am running into the same issue as well.

---

Added after 1 28 minutes:

The answer is self.setJoinMode(QtSql.QSqlRelationalTableModel.Le ftJoin).

QSqlRelationalTableModel.setJoinMode (self, JoinMode joinMode)
Sets the SQL join mode to the value given by joinMode to show or hide rows with NULL foreign keys.
In InnerJoin mode (the default) these rows will not be shown; use the LeftJoin mode if you want to show them.
This function was introduced in Qt 4.8.