QSqlRelation fails on a NULL in foreign key column

galrub

Hello all,
I have a table which has two foreign keys, in every row, one is use and the other stays NULL (sqlite).
when not using QSqlRelation the table shows on the QTableView, so it's a QSqlRelation issue.

any idea?

Thanks,
G

Eddy

Can you show us your table structure with the definition of your columns?

galrub

@
CREATE TABLE card (
card_idx integer PRIMARY KEY AUTOINCREMENT NOT NULL,
pincode numeric(4),
pincode2 numeric(6),
card_type integer,
vehicle_idx integer,
driver_idx integer,
/* Foreign keys */
FOREIGN KEY (driver_idx)
REFERENCES driver(idx)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (vehicle_idx)
REFERENCES vehicle(idx)
ON DELETE SET NULL
ON UPDATE CASCADE
);
@

the columns are vehicle_idx and driver_idx... ether a vhicle_idx or a driver_idx are set, so every row in the db has one of these values set to null.

ThX

Eddy

Can you show us the relevant code where you use QSqlRelation?

galrub

ok:
@
QSqlRelationalTableModel * model = new QSqlRelationalTableModel(parent, db);
model ->setTable("card");
model->setEditStrategy(QSqlTableModel::OnRowChange);
uint idx = model ->fieldIndex("driver_idx");
model->setRelation(model ->fieldIndex("vehicle_idx"), QSqlRelation("vehicle", "idx", "plate_number"));
--model->setRelation(model ->fieldIndex("driver_idx"), QSqlRelation("driver", "idx", "last_name"));

model->setHeaderData(1, Qt::Horizontal, tr("Pin"));
model->setHeaderData(2, Qt::Horizontal, tr("Pin2"));
model->setHeaderData(3, Qt::Horizontal, tr("Type"));
model->setHeaderData(4, Qt::Horizontal, tr("Vehicle"));
model->setHeaderData(5, Qt::Horizontal, tr("Driver"));
model ->select();--
//.....
QTableView * view = ui.cardTab.....
table ->hideColumn(model ->fieldIndex("card_idx"));
table ->setItemDelegateForColumn(4, new QSqlRelationalDelegate(table));
table ->setItemDelegateForColumn(5, new QSqlRelationalDelegate(table));
@

and, just to make it clear again, if there are values for the both foreign keys then there is no problem, only when one is NULL. if I dont use relation on ether of these columns the table will show empty cells.

THX,
G

Eddy

bq. if there are values for the both foreign keys then there is no problem, only when one is NULL. if I dont use relation on ether of these columns the table will show empty cells.

The relation needs something to refer to.
What you can do is add an empty choice using " " or "-" or "none". AFAIK the QSqlRelation needs some value to look it up in the other table. If you don't provide any it can't do anything.

Could you try this?

galrub

bq. What you can do is add an empty choice using “ “ or “-” or “none”. AFAIK the QSqlRelation needs some value to look it up in the other table. If you don’t provide any it can’t do anything.

sadly I cannot do this because of other constraints,

I will try doing a subclass to the delegate and redo the paint() func to ignore null (0) values.

Thx

Eddy

bq. sadly I cannot do this because of other constraints,

What are those other constraints?

galrub

well, now I am working with a local embedded Sqlite3 database, (the plan is that) in the near future the program will connect to the company server (MSSQL) and to that sever there are enterprise servers connected, each one with his own 'requirements' (J2EE, .ASP.NET)...

so, as u can see, problems, but, we are considering changing this table soon, since there is a type column, we can have only one driver/vehicle column, until then, will have to do a workaround.

PS: I'm the maintenance guy for all of these servers (excluding the MSSQL), so, it's basically up to me... LOL

Atli

I found this post when I was trying to find a solution to a similar issue today but as it didn't have an acceptable answer to my problem I found another one which I thought I would share.

Making the model left join the tables means that any row with null foreign keys will be loaded. Using a foreign key delegate will however make you choose a non null option on edit.

To do this simply do:

model->setJoinMode((QSqlRelationalTableModel::JoinMode) 1);

The 1 is the value of the enum for Left Join, 0 is the default (Inner Join). Hope this help someones.

SGaist

Hi and welcome to devnet,

Thanks for sharing.

However why not just use: QSqlRelationalTableModel::LeftJoin ? Doing a C cast on an int like that just makes the code unclear and will break easily if the JoinMode enum changes for whatever reason.

Vikinn

@Atli said in QSqlRelation fails on a NULL in foreign key column:

I found this post when I was trying to find a solution to a similar issue today but as it didn't have an acceptable answer to my problem I found another one which I thought I would share.

Making the model left join the tables means that any row with null foreign keys will be loaded. Using a foreign key delegate will however make you choose a non null option on edit.

To do this simply do:
model->setJoinMode((QSqlRelationalTableModel::JoinMode) 1);
The 1 is the value of the enum for Left Join, 0 is the default (Inner Join). Hope this help someones.

IT IS TRUELY WORK! THANK YOU BOTH!
THIS IS MY SULOTION:

model->setJoinMode(QSqlRelationalTableModel::LeftJoin);