PyQt SQL Many to Many relationship question
-
Hi Everyone,
Is it possible to have joins in an sqlrelationaltablemodel? How do you do joins and show the data? I suspect that this is not possible, but wanted to check. I am using a sqlrelationaltablemodel. How can you do joins and add that to the model?
In pure sql I can do this:
SELECT * FROM rt_table_has_mgmt_desc
INNER JOIN rt_table on rt_table_has_mgmt_desc.rt_table_id = rt_table.id
INNER JOIN mgmt_desc ON mgmt_desc.id = rt_table_has_mgmt_desc.mgmt_desc_id
INNER JOIN mgmt_desc_lu ON mgmt_desc_lu.id = mgmt_desc.mgmt_lu_id;How do I do this in a model in PyQt? I suspect you can't, but what is the work around here?
What I want is this:
2 1 77 blm 2000-01-01 2000-01-01 2000-01-01 Limited
3 1 78 blm 2000-01-01 2000-01-01 2000-01-01 Closed
4 1 79 blm 2000-01-01 2000-01-01 2000-01-01 Open
5 1 80 blm 2000-01-01 2000-01-01 2000-01-01 Limited
6 6 81 blm 2000-01-01 2000-01-01 2000-01-01 Open
7 10 82 blm 2000-01-01 2000-01-01 2000-01-01 Limited
8 11 83 blm 2000-01-01 2000-01-01 2000-01-01 Closedmy db schema is:
CREATE TABLE rt_table_has_mgmt_desc (
id INTEGER PRIMARY KEY,
rt_table_id INTEGER NOT NULL,
mgmt_desc_id INTEGER NOT NULL,
CONSTRAINT 'fk_rt_table_has_mgmt_desc_rt_table1' FOREIGN KEY ( rt_table_id ) REFERENCES rt_table ( id ) ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT 'fk_rt_table_has_mgmt_desc_mgmt_desc1' FOREIGN KEY ( mgmt_desc_id ) REFERENCES mgmt_desc ( id )
);CREATE TABLE mgmt_desc (
id INTEGER PRIMARY KEY AUTOINCREMENT,
entry_date TEXT NOT NULL,
start_date TEXT NOT NULL,
End_date TEXT,
mgmt_lu_id INTEGER NOT NULL,
CONSTRAINT 'fk_mgmt_lu_mgmt_lu_id' FOREIGN KEY ( mgmt_lu_id ) REFERENCES mgmt_desc_lu ( id ) ON DELETE NO ACTION
ON UPDATE NO ACTION
);CREATE TABLE mgmt_desc_lu (
id INTEGER PRIMARY KEY,
mgmt_name TEXT NOT NULL,
mgmt_alias TEXT NOT NULL,
mgmt_def TEXT NOT NULL
); -
Hi,
Sounds rather like a QSqlQueryModel job, doesn't it ?
-
Hi SGaist,
As always you are right. I can subclass QSqlQueryModel. What if I want to included a setRelation though?
The idea is to allow the user to change routes in a lineedit widget and allow them to insert management decision in 4 additional widgets. But how do you allow them to insert rows into the management table? Can this be done with one model or do I need more than one model to do this?
Currently I was doing this with three models, but that might not be correct. I have a model for the route table. I have a model for the management table and a model for the tableview which is the many to many. Is there a complete example for inserting row? -
I just realized something: you already have joints when using a QSqlRelationalTableModel. You have to set the relations corresponding to your various tables, see setRelation
-
Hi SGaist,
Thank you SGaist for all your help.
But that only lets you connect one column to one column in the other table. That isn't a full join though right?
Is it possible to insert into many tables using one model? Otherwise how do you insert into the join table?
I want to insert into the management desc table and then insert into the rt_table_has_mgmt_desc table. This would be the many to many relationship.
Can this be done using one model or do I need three models to achieve the desired goal? How to do insert into two different table from one model? Having three models doesn't seem like the best approach either because it would be hard to connect each model.
I clearly am missing something because what I am trying to do should not be too hard.
-
I'm no DB admin, but AFAIK joints are used to query items from several different tables not to insert in several tables.
Yes you can update several tables from one model, but you'll have to write the logic to do that
