Nominate our 2022 Qt Champions!

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 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->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->setHeaderData(dbQsoModel->fieldIndex("id"), Qt::Horizontal, tr("Id"));
        // other setHeaderData here
        ui->tableViewQso->setItemDelegate(new QSqlRelationalDelegate(ui->tableViewQso));
        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);

    Without relation:

    SELECT qso."id",qso."date",qso."time",qso."callsign",qso."callsign2", AS band_name_2,qso."freq",,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" LEFT JOIN mode relTblAl_7 ON qso."mode_id"

    With relation:

    SELECT qso."id",qso."date",qso."time",qso."callsign",qso."callsign2", AS band_name_3,qso."freq", 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",,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" LEFT JOIN mode relTblAl_7 ON qso."mode_id" LEFT JOIN entity relTblAl_22 ON qso."country"=relTblAl_22.dxcc

Log in to reply