PDA

View Full Version : PyQt SQL Many to Many relationship question



lukeQt
13th August 2015, 22:26
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 Closed

my 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
);