QSqlRelation fails on a NULL in foreign key column
-
wrote on 20 Jun 2011, 16:52 last edited by
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 -
wrote on 20 Jun 2011, 17:23 last edited by
Can you show us your table structure with the definition of your columns?
-
wrote on 20 Jun 2011, 18:06 last edited by
@
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
-
wrote on 20 Jun 2011, 18:24 last edited by
Can you show us the relevant code where you use QSqlRelation?
-
wrote on 20 Jun 2011, 19:02 last edited by
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 -
wrote on 21 Jun 2011, 14:50 last edited by
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?
-
wrote on 21 Jun 2011, 15:26 last edited by
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
-
wrote on 21 Jun 2011, 18:35 last edited by
bq. sadly I cannot do this because of other constraints,
What are those other constraints?
-
wrote on 22 Jun 2011, 07:48 last edited by
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
-
wrote on 31 Jan 2017, 10:02 last edited by 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.
-
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. -
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.
wrote on 3 Apr 2019, 13:29 last edited by@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);