Unsolved Activating QSqlRelationalTableModel causes duplicates row in QTableView
-
My sqlite3 scheme is as follow:
sqlite> .schema qso CREATE TABLE qso (id INTEGER PRIMARY KEY AUTOINCREMENT,date VARCHAR(10), time VARCHAR(10), callsign VARCHAR(10), callsign2 VARCHAR(10), band_id INTEGER not null,freq INTEGER, mode_id INTEGER NOT NULL, tx INTEGER, rx INTEGER,qsl VARCHAR(50), low VARCHAR(50), eq VARCHAR(50), dxcc INTEGER, cqz INTEGER,ituz INTEGER, state VARCHAR(50), locator VARCHAR(50), manager VARCHAR(50),iota VARCHAR(50), sat VARCHAR(50), power VARCHAR(50), country VARCHAR, my_country INTEGER, note TEXT, processed_dxcc BOOLEAN DEFAULT 0, processed_qrz BOOLEAN DEFAULT 0, qrz_url VARCHAR(255), FOREIGN KEY (mode_id) REFERENCES mode, FOREIGN KEY (band_id) REFERENCES band, FOREIGN KEY (country) REFERENCES entity, FOREIGN KEY (my_country) REFERENCES entity, FOREIGN KEY (dxcc) REFERENCES entity); sqlite> .schema entity CREATE TABLE entity (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(40) NOT NULL, cqz INTEGER NOT NULL, ituz INTEGER NOT NULL, continent INTEGER NOT NULL, latitude REAL NOT NULL, longitude REAL NOT NULL, utc INTEGER NOT NULL, dxcc INTEGER NOT NULL, mainprefix VARCHAR(15) NOT NULL, deleted INTEGER, sincedate VARCHAR(10), todate VARCHAR(10), UNIQUE (dxcc, mainprefix), FOREIGN KEY (continent) REFERENCES continent(shortname));
So qso.country is pointing to entity.dxcc.
(note: i'm not using here a relation to the classic "id" field because it can change, and the "dxcc" one is unique, which id won't)In Qt i have a QTableView which is using a QSqlRelationalTableModel.
When i activate the "country" relation in Qt, i got duplicates row.
Using :qDebug().noquote() << dbQsoModel->query().executedQuery(); qDebug() << dbQsoModel->rowCount();
I got "33" without the relation (same count in bdd), "40" with it.
Following is the code i'm using for this part, and the two generated queries (without relation, and with).
Also tried the queries inside sqlite3 and got the correct number of rows for the without-relation, but duplicates with the relation.Did anyone have an idea what can be causing that in Qt or missing in SQL ?
dbQsoModel = new QSqlRelationalTableModel(); dbQsoModel->setTable("qso"); dbQsoModel->setJoinMode(QSqlRelationalTableModel::LeftJoin); // set Left Join to avoid hiding rows with NULL/EMPTY RELATIONS dbQsoModel->setRelation(dbQsoModel->fieldIndex("band_id"), QSqlRelation("band", "id", "name")); dbQsoModel->setRelation(dbQsoModel->fieldIndex("mode_id"), QSqlRelation("mode", "id", "name")); // the next relation is causing duplicates rows dbQsoModel->setRelation(dbQsoModel->fieldIndex("country"), QSqlRelation("entity", "dxcc", "name")); //dbQsoModel->setRelation(dbQsoModel->fieldIndex("my_country"), QSqlRelation("entity", "dxcc", "name")); dbQsoModel->setEditStrategy(QSqlTableModel::OnRowChange); dbQsoModel->setHeaderData(dbQsoModel->fieldIndex("id"), Qt::Horizontal, tr("Id")); // other setHeaderData here ui->tableViewQso->setModel(dbQsoModel); ui->tableViewQso->setItemDelegate(new QSqlRelationalDelegate(ui->tableViewQso)); ui->tableViewQso->setSelectionBehavior(QAbstractItemView::SelectRows); ui->tableViewQso->setSelectionMode(QAbstractItemView::SingleSelection); qDebug() << dbQsoModel->lastError().text(); qDebug().noquote() << dbQsoModel->query().executedQuery(); qDebug() << dbQsoModel->rowCount(); ui->tableViewQso->setColumnHidden(dbQsoModel->fieldIndex("id"), true); ui->tableViewQso->setColumnHidden(dbQsoModel->fieldIndex("processed_dxcc"), true); ui->tableViewQso->resizeColumnsToContents();
Without relation:
SELECT qso."id",qso."date",qso."time",qso."callsign",qso."callsign2",relTblAl_5.name AS band_name_2,qso."freq",relTblAl_7.name,qso."tx",qso."rx",qso."qsl",qso."low",qso."eq",qso."dxcc",qso."cqz",qso."ituz",qso."state",qso."locator",qso."manager",qso."iota",qso."sat",qso."power",qso."country",qso."my_country",qso."note",qso."processed_dxcc",qso."processed_qrz",qso."qrz_url" FROM qso LEFT JOIN band relTblAl_5 ON qso."band_id"=relTblAl_5.id LEFT JOIN mode relTblAl_7 ON qso."mode_id"=relTblAl_7.id
With relation:
SELECT qso."id",qso."date",qso."time",qso."callsign",qso."callsign2",relTblAl_5.name AS band_name_3,qso."freq",relTblAl_7.name AS mode_name_2,qso."tx",qso."rx",qso."qsl",qso."low",qso."eq",qso."dxcc",qso."cqz",qso."ituz",qso."state",qso."locator",qso."manager",qso."iota",qso."sat",qso."power",relTblAl_22.name,qso."my_country",qso."note",qso."processed_dxcc",qso."processed_qrz",qso."qrz_url" FROM qso LEFT JOIN band relTblAl_5 ON qso."band_id"=relTblAl_5.id LEFT JOIN mode relTblAl_7 ON qso."mode_id"=relTblAl_7.id LEFT JOIN entity relTblAl_22 ON qso."country"=relTblAl_22.dxcc