SQLite data to GUI via QSqlTableModel or other possible ways

[A Former User]

Hi,

I have the following database structure (see ER model):

PK = Primary Key, FK = Foreign Key

And I have the following GUI

What I want:
Now I want to select a Mushroom in die QListView on the left site. Then all information about the mushroom should display on the right sight. The information should be editable when I click on the edit button and saved back to database if I click on save button (edit button will changed to save button).

About the structure
Everything is QLindEdits except Synonyms which is a QListView, Red List is a QComboBox and Notes is a QPlainTextEdit.

Now I played around a bit and tried to get the data into the fields somehow. Here are some of my tries:

First of all the thing to get data to QListView on the left site (What works):

QSqlTableModel *DatabaseManager::fullnameList() const
{
    QSqlTableModel *model = new QSqlTableModel;
    model->setTable("Pilze");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    model->setSort(2,Qt::AscendingOrder);
    model->select();
    return model;
}

After that I created a QDataWidgetMapper on my mainwindow. So that I can do select the Value on the left QListView I have tried that:

void MainWindow::on_listView_pressed(const QModelIndex &index)
{
    //mMapper = QDataWidgetMapper
    //mModel = QSqlTableModel
    mMapper->setModel(mModel);
    mMapper->addMapping(ui->fullname, 1);
    mMapper->addMapping(ui->number, 0, "text");
    mMapper->addMapping(ui->genus, 2);
    mMapper->addMapping(ui->kind, 3);
    mMapper->addMapping(ui->family, 4);
    mMapper->addMapping(ui->order, 5);
    mMapper->addMapping(ui->noteTextfield, 6);

    mMapper->setCurrentModelIndex(index);
}

This worksk as expect but only when I click with my mouse on a name in the ListView. I would like to be able to use the arrow keys.

So my first question: How can I change this so that the arrow keys also work for the selection?

The next problem I have is dealing with the database. In my example above I take all values from the table "Pilze" and can assign them to the QLineEdit's and QPlainTextEdit.

Is there any way I can put all the information from table "Pilze", "Synonyme", "RList", "DPilzname" in one model at once? Or do I have to create a separate model for each table I take information from? What is the best approach here?

I hope I could explain everything I want to do. I am really grateful for your help and tips.
Gabber

[JonB]

@Gabber
Do not use signal pressed() for the slot. Use signal currentChanged(), which i believe will work for either mouse or keyboard.

You must use a separate model for each table. One row in the model must correspond to one row in the database. You could JOIN tables (not for editing though), but only if they have a 1-1 mapping, not 1-N.

You can do a certain limited amount of 1-N via QSqlRelationalTableModel class. But it's only really designed to be used where a field in the main table is an "index" into another table just for the purpose of mapping to/from the index and a user-readable-string (in the second table), which can be presented to the user as the string while looking at the first table's row or as a combobox with all possible values when the user edits the first table's rows. It is worth a look, but if your requirements exceed this, roll your own code for connecting tables as necessary.

Separately, I would not expect a QDataWidgetMapper to have its model set and its mappings added each time user interacts with the UI, as per your on_listView_pressed(). Set up your mappings to tables once at the start. If necessary, use multiple, separate QDataWidgetMappers, one for each table.

[A Former User]

Thanks for your notes. I tried to implement the things. Unfortunately there is no slot currentChanged() in the QT Creator in the Designer.

How can I implement this?

The second problem I have is the mapping of a QSqlRelationalTableModel. QSqlTableModel mapping works fine. In the constructor of MainWindow.cpp I did the following:

[...]
        mFungusModel = mDatabaseManager->fullnameList();
        mSynonymModel = mDatabaseManager->synonymList();
        mGermanModel = mDatabaseManager->germanList();
        ui->tableView->setModel(mGermanModel);

        ui->listView->setModel(mFungusModel);
        ui->listView->setModelColumn(1);

        mFungusMapper->setModel(mFungusModel);
        mFungusMapper->addMapping(ui->number, 0, "text");
        mFungusMapper->addMapping(ui->fullname, 1);
        mFungusMapper->addMapping(ui->genus, 2);
        mFungusMapper->addMapping(ui->kind, 3);
        mFungusMapper->addMapping(ui->family, 4);
        mFungusMapper->addMapping(ui->order, 5);
        mFungusMapper->addMapping(ui->noteTextfield, 6);

        mSynonymMapper->setModel(mSynonymModel);
//        ui->listView_2->setModelColumn(1);
        mSynonymMapper->addMapping(ui->listView_2, 1);

        mGermanMapper->setModel(mGermanModel);
        mGermanMapper->addMapping(ui->germanName, mGermanModel->fieldIndex("Vollname"));
}

But the mapping

mGermanMapper->addMapping(ui->germanName, mGermanModel->fieldIndex("Vollname"));

unfortunately does not work. The rest of the code works.

How can I get the data into the QLineEdit field from QSqlRelationalTableModel ?

My DatabaseManager looks like this:

QSqlTableModel *DatabaseManager::fullnameList() const
{
    QSqlTableModel *model = new QSqlTableModel();
    model->setTable("Pilze");
    model->setSort(1, Qt::AscendingOrder);
    model->select();
    return model;
}

QSqlTableModel *DatabaseManager::synonymList() const
{
    QSqlTableModel *model = new QSqlTableModel();
    model->setTable("Synonyme");
    model->setSort(1, Qt::AscendingOrder);
    model->select();
    return model;
}

QSqlRelationalTableModel *DatabaseManager::germanList() const
{
    QSqlRelationalTableModel *model = new QSqlRelationalTableModel();
    model->setTable("DPilzname");
    model->setRelation(0,QSqlRelation("DName", "ID", "Vollname"));
    model->setSort(1, Qt::AscendingOrder);
    model->select();
    return model;
}

Thanks for your help!

[JonB]

@Gabber said in SQLite data to GUI via QSqlTableModel or other possible ways:

Unfortunately there is no slot currentChanged() in the QT Creator in the Designer.

QAbstractItemView::currentChanged()

This slot is called when a new item becomes the current item.

QListView::currentChanged()

Reimplements: QAbstractItemView::currentChanged()

It's override virtual protected, so if you want to react to it you need to subclass and override.

unfortunately does not work.

I do not know what that means. You might, but I don't.

How can I get the data into the QLineEdit field from QSqlRelationalTableModel ?

I don't know what data in what line edit. But what are you trying to edit? QSqlRelationalTableModel allows you to look up values in other tables as a foreign key, but it does not allow you to edit the other table's values.

[JonB]

OK, so I sort of get what you're trying to do. Your German stuff is a QSqlRelationalTableModel(). It's for editing/displaying DPilzname table, and allowing you to pick from values in table DName (see https://doc.qt.io/qt-5/qsqlrelationaltablemodel.html#details and QSqlRelationalDelegate). You can't edit what is in table DName like that. You would need a QSqlTableModel onto DName for that.

[A Former User]

Please excuse my bad description. I will see if I can get your tip implemented. I am a beginner when it comes to Qt. Some things I unfortunately do not always find on the Internet. I also have to try out a lot. I am glad that you support me. I will contact you if I do not get further.

Thanks

[A Former User]

You can find the solution here. That works for me great!