4. Update sql data models

Update sql data models


  • Hello,
    I need some help to continue my study of QML.
    How can I update a model used in combox by cascade. I have 3 combo connected to 3 suclasses of sqltablemodel. When the 1st is activated, the 2nd updates his content and so on to the 3rd.
    I include my code so you can help me see where I am wrong. Thanks.

    [0_1515773428441_countrymodels](Uploading 100%) [1_1515773477763_main.qml](Uploading 100%) [0_1515773477763_main.cpp](Uploading 100%)
    [0_1515773521720_database.h](Uploading 100%)

    0
  • Lifetime Qt Champion

    Hi and welcome to devnet,

    Your code is not accessible.

    Usually, you have a slot that is connected to the ComboBox activated signal and in that slot you update the content of the next ComboBox.

    0

  • @Diarby said in Update sql data models:

    Hello,
    I need some help to continue my study of QML.
    How can I update a model used in combox by cascade. I have 3 combo connected to 3 suclasses of sqltablemodel. When the 1st is activated, the 2nd updates his content and so on to the 3rd.
    I include my code so you can help me see where I am wrong. Thanks.

    [0_1515773428441_countrymodels](Uploading 100%) [1_1515773477763_main.qml](Uploading 100%) [0_1515773477763_main.cpp](Uploading 100%)
    [0_1515773521720_database.h](Uploading 100%)

    Hello,
    I don't know why my code is not accessible but I give below some important part of it. In each subclass I have 2 public slots
    int primaryKeyId(const int row);
    void refresh(const int parentId);

    int <classname>::primaryKeyId(const int row) {
    if (row >= 0 && row < rowCount()) {
    QSqlRecord record = this->record(row);
    return record.value("regionid").toInt();
    }
    return 0;
    }
    void <classname>::refresh(const int parentId) {
    setFilter(tr("regionid = %1").arg(parentId));
    // replace regionid by appropriate parentId column of each class
    }
    Each class is registered in main.cpp
    qmlRegisterType<RegionModel>("org.diarby.qmlcomponents", 1, 0, "Region");
    qmlRegisterType<SubRegionModel>("org.diarby.qmlcomponents", 1, 0, "SubRegion");
    qmlRegisterType<SubZoneModel>("org.diarby.qmlcomponents", 1, 0, "SubZone");
    qmlRegisterType<CountryModel>("org.diarby.qmlcomponents", 1, 0, "Country");

    In main.qml, 3 combo (id: comboRegion, comboSubRegion, comboSubZone)
    ComboBox {
    id: comboRegion
    textRole: "Region"
    model: Region {}
    onActivated: {
    var tmpId = model.primaryKeyId(currentIndex)
    comboSubRegion.model.refresh(tmpId)
    }
    onModelChanged: {
    currentIndex = Math.floor(Math.random() * count)
    }
    }

    for other combo, replace textRole and model as appropriate

    When I run, all combo are filled with data but when I click on a parent combo, the child one is cleared. I don't understand why.
    Before writing this help, I checked that primaryKeyId slot returned the correct value by putting a label that write the id of current index.

    0
  • Lifetime Qt Champion

    What does refresh do ?
    Are you properly handling the filter on the other models ?

    0 1 Reply

  • @SGaist
    Hi. Yes I do.

    int RegionModel::primaryKeyId(const int row) {
    if (row >= 0 && row < rowCount()) {
    QSqlRecord record = this->record(row);
    return record.value("regionid").toInt();
    }
    return 0;
    }
    int SubRegionModel::primaryKeyId(const int row) {
    if (row >= 0 && row < rowCount()) {
    QSqlRecord record = this->record(row);
    return record.value("subregionid").toInt();
    }
    return 0;
    }

    void SubRegionModel::refresh(const int parentId) {
    setFilter(tr("regionid = %1").arg(parentId));
    }

    int SubZoneModel::primaryKeyId(const int row) {
    if (row >= 0 && row < rowCount()) {
    QSqlRecord record = this->record(row);
    return record.value("subzoneid").toInt();
    }
    return 0;
    }

    void SubZoneModel::refresh(const int parentId) {
    setFilter(tr("subregionid = %1").arg(parentId));
    }

    There are the 3 tables :
    create table region (
    RegionId integer primary key,
    Region varchar(16) not null unique
    );

    create table subregion (
    RegionId integer not null,
    SubRegionId integer primary key,
    SubRegion varchar(32) not null unique
    );

    create table subzone (
    SubRegionId integer not null,
    SubZoneId integer primary key,
    SubZone varchar(32) not null unique
    );

    0 1 Reply

  • Please find data for tables :
    insert into region values (1,'Monde');
    insert into region values (2,'Afrique');
    insert into region values (19,'Amériques');
    insert into region values (142,'Asie');
    insert into region values (150,'Europe');
    insert into region values (9,'Océanie');

    insert into subregion values (2,14,'Afrique orientale');
    insert into subregion values (2,17,'Afrique centrale');
    insert into subregion values (2,15,'Afrique septentrionale');
    insert into subregion values (2,18,'Afrique australe');
    insert into subregion values (2,11,'Afrique occidentale');
    insert into subregion values (19,419,'Amérique latine et Caraïbes');
    insert into subregion values (19,21,'Amérique septentrionale');
    insert into subregion values (142,143,'Asie centrale');
    insert into subregion values (142,30,'Asie orientale');
    insert into subregion values (142,34,'Asie méridionale');
    insert into subregion values (142,35,'Asie du Sud-Est');
    insert into subregion values (142,145,'Asie occidentale');
    insert into subregion values (150,151,'Europe orientale');
    insert into subregion values (150,154,'Europe septentrionale');
    insert into subregion values (150,39,'Europe méridionale');
    insert into subregion values (150,155,'Europe occidentale');
    insert into subregion values (9,53,'Australie et Nouvelle-Zélande');
    insert into subregion values (9,54,'Mélanésie');
    insert into subregion values (9,57,'Micronésie');
    insert into subregion values (9,61,'Polynésie');

    insert into subzone values (419,29,'Caraïbes');
    insert into subzone values (419,13,'Amérique centrale');
    insert into subzone values (419,5,'Amérique du Sud');

    0
  • Lifetime Qt Champion

    @Diarby said in Update sql data models:

    setFilter(tr("subregionid = %1").arg(parentId));

    Why are you translating that string ?

    0

  • Hello,
    Excuse me for late answering. It's not a translation but a way to pass parentId parameter. Taking into account your remark, I changed to QString("subregionid = %1").arg(parentId), the result is the same : child combo are not updated.

    I am confused but this code is so simple that I wonder what is wrong ?

    0
  • Lifetime Qt Champion

    Did you check the content of the model after applying the filter on the C++ side ?

    0

  • Hello,
    I did most checks in C++ and got the data for each parentId supplied as expected. But back to QML, the changes are not applied.
    In theory the QML combo, updates are done when the control is activated : I send the selected ID to child model so it can update his data and applied it to combo.
    Maybe I am wrong, but where ?

    0
  • Lifetime Qt Champion

    Can you share your project ?

    0

  • With pleasure. Whole project is compiled in world.7z (12 KiB) [0_1516430081478_world.7z](Uploading 100%)
    I receive message that I don't have privilèges to upload files. May I send you by email ?

    0
  • Lifetime Qt Champion

    Not pre-compiled, just the sources.

    0

  • Hello ! I am happy to be back as politicians in my country blocked internet from saturday to wednesday. Please find here https://www.dropbox.com/s/6juslcf7dfkxk69/world.7z?dl=0 all sources of my project.
    Thanks for your help.

    0
  • Lifetime Qt Champion

    Your where close are wrong. For example, in SubRegionModel, it should be setFilter(QString("subregion.RegionId = %1").arg(parentId));.

    0

  • Hello, I made corrections and it works !
    My mistake were to not set the table's name as the class is subclassed a QSqlRelationalTableModel.
    Thanks a lot for your help.

    0
Log in to reply